PHP DatabaseManager::query方法代码示例

 public static function clearDatabase()
 {
     apc_clear_cache();
     $database = new DatabaseManager();
     //$resource = $database->query("SELECT DATABASE()");
     //$name = $resource->fetch_row()[0];
     //$resource->close();
     //make sure we don't accidentally load dummy data on live database
     //to do: make sure this actually works
     //if(strpos($name, "test" === false)) {
     if (!$database->debug()) {
         throw new Exception("Database may not be safe to run tests on");
     }
     //addon_addons, addon_boards, addon_tags, addon_tagmap, group_groups, group_usermap, addon_comments, addon_ratings
     if (!$database->query("SET FOREIGN_KEY_CHECKS=0")) {
         throw new Exception("Database error: " . $database->error());
     }
     if (!$database->query("DROP TABLE IF EXISTS addon_tagmap, addon_tags, addon_dependency,\n\t\t\taddon_addons, addon_boards, addon_comments, addon_ratings, addon_stats,\n\t\t\tusers, build_builds, build_dependency, build_stats, tag_stats, group_groups, group_usermap,\n\t\t\tstatistics, screenshots, build_screenshotmap, addon_screenshotmap")) {
         throw new Exception("Database error: " . $database->error());
     }
     if (!$database->query("SET FOREIGN_KEY_CHECKS=1")) {
         throw new Exception("Database error: " . $database->error());
     }
     apc_clear_cache();
 }

 public function testBasicQuery()
 {
     TestManager::loadBasicDummyData();
     $database = new DatabaseManager();
     $resource = $database->query("SHOW TABLES");
     $this->assertNotEquals(false, $resource);
 }

 public function setUsername($name)
 {
     if ($this->verified) {
         $database = new DatabaseManager();
         $database->query("UPDATE `users` SET `username`='" . $database->sanitize($name) . "' WHERE `email`='" . $database->sanitize($this->getEmail()) . "'");
         apc_store('userObject_' . $this->blid, $this, 600);
     }
 }

 function getCount()
 {
     if (!isset($numberOfAddons)) {
         $db = new DatabaseManager();
         $res = $db->query("SELECT COUNT(*) FROM `addon_addons` WHERE board='" . $db->sanitize($this->id) . "'  AND deleted=0");
         $this->numberOfAddons = $res->fetch_row()[0];
     }
     return $this->numberOfAddons;
 }

 function collectHourStat($store = false)
 {
     $stats = new stdClass();
     $stats->time = gmdate("Y-m-d H:00:00", time());
     $stats->duration = "hour";
     $database = new DatabaseManager();
     //Addons!
     $addons = new stdClass();
     $addonArray = AddonManager::getAll();
     $addons->count = sizeof($addonArray);
     $addons->cumulative_downloads = array();
     $addons->usage = array();
     $addons->usage_total = array();
     foreach ($addonArray as $addon) {
         $downloadData = new stdClass();
         // TODO we need to go back. I dont want total downloads, I want individual
         //$downloadData->web =
         //$downloadData->ingame =
         //$downloadData->update =
         $addons->cumulative_downloads[$addon->getId()] = $downloadData;
         $res = $database->query("SELECT `version` FROM `stats_usage` WHERE `aid`='" . $addon->getId() . "' AND `reported` > now() - INTERVAL 1 HOUR");
         $ret = $res->fetch_object();
         $usage = array();
         $total = 0;
         while ($obj = $res->fetch_object()) {
             $total++;
             if (!isset($usage[$obj->version])) {
                 $usage[$obj->version] = 1;
             } else {
                 $usage[$obj->version]++;
             }
         }
         $addons->usage[$addon->getId()] = $usage;
         $addons->usage_total[$addon->getId()] = $total;
     }
     $stats->addons = $addons;
     //Builds
     $builds = new stdClass();
     $buildArray = BuildManager::getAll();
     $builds->count = sizeof($buildArray);
     $builds->cumulative_downloads = array();
     foreach ($buildArray as $build) {
         // TODO this isn't done either...
         //$builds->cumulative_downloads[$build->getId()] = $build->getDownloads();
     }
     $stats->builds = $builds;
     //Master Server
     $stats->master = new stdClass();
     $master = CronStatManager::getMasterServerStats();
     $stats->master->users = $master[0];
     $stats->master->servers = $master[1];
     if ($store) {
         CronStatManager::verifyTable($database);
         $database->query("INSERT INTO `cron_statistics`  (`time` , `duration` , `data`) VALUES ('" . $stats->time . "',  'hour',  '" . $database->sanitize(json_encode($stats)) . "')");
     }
     return $stats;
 }

 public static function getHistory($blid)
 {
     $db = new DatabaseManager();
     $res = $db->query("SELECT * FROM `user_log` WHERE `blid`='" . $db->sanitize($blid) . "' ORDER BY `lastseen` DESC");
     $ret = array();
     while ($obj = $res->fetch_object()) {
         $ret[] = $obj;
     }
     return $ret;
 }

 public static function register($email, $password1, $password2, $blid)
 {
     //if(!AccountManager::validUsername($username)) {
     //	return [
     //		"message" => "Invalid username provided. You may only use up to 20 characters."
     //	];
     //}
     if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
         return ["message" => "Invalid e-mail address"];
     }
     if ($password1 !== $password2) {
         return ["message" => "Your passwords do not match."];
     }
     if (strlen($password1) < 4) {
         return ["message" => "Your password must be at least 4 characters"];
     }
     $blid = trim($blid);
     if (!is_numeric($blid)) {
         return ["message" => "INVALID BL_ID"];
     }
     $loginDetails1 = AccountManager::getLoginDetailsFromBLID($blid);
     $loginDetails2 = AccountManager::getLoginDetailsFromEmail($email);
     if ($loginDetails1) {
         return ["message" => "That BL_ID is already in use!"];
     } else {
         if ($loginDetails2) {
             return ["message" => "That E-mail address is already in use."];
         }
     }
     $database = new DatabaseManager();
     //AccountManager::verifyTable($database);
     $intermediateSalt = md5(uniqid(rand(), true));
     $salt = substr($intermediateSalt, 0, 6);
     $hash = hash("sha256", $password1 . $salt);
     //long if statement because oh well
     //I am assuming 'groups' is a json array, so by default it is "[]"
     if ($database->query("INSERT INTO users (password, salt, blid, email, groups, username) VALUES ('" . $database->sanitize($hash) . "', '" . $database->sanitize($salt) . "', '" . $database->sanitize($blid) . "', '" . $database->sanitize($email) . "', '" . $database->sanitize("[]") . "', '" . $database->sanitize("Blockhead" . $blid) . "')")) {
         //$_SESSION['justregistered'] = 1;
         //header("Location: " . $redirect);
         //I think this is the only way to do a redirect containing post information
         //echo("<!doctype html><head><meta charset=\"utf-8\"></head><body>");
         //echo("<form class=\"hidden\" action=\"/login.php\" name=\"redirectForm\" method=\"post\">");
         //echo("<input type=\"hidden\" name=\"redirect\" value=\"" . htmlspecialchars($redirect) . "\">");
         //echo("<input type=\"hidden\" name=\"justregistered\" value=\"1\">");
         //echo("<input type=\"submit\" value=\"Click here if your browser does not automatically redirect you\">");
         //echo("</form>");
         //echo("<script language=\"JavaScript\">document.redirectForm.submit();</script>");
         //echo("</body></html>");
         //die();
         return ["redirect" => "/login.php"];
     } else {
         throw new Exception("Error adding new user into databse: " . $database->error());
     }
 }

 public static function getAll()
 {
     $ret = array();
     $db = new DatabaseManager();
     $res = $db->query("SELECT `id` FROM `build_builds`");
     while ($obj = $res->fetch_object()) {
         $ret[$obj->id] = BuildManager::getFromId($obj->id);
     }
     $res->close();
     return $ret;
 }

 public static function getDistribution($aid)
 {
     $db = new DatabaseManager();
     $res = $db->query("SELECT * FROM `stats_usage` WHERE `aid`='" . $db->sanitize($aid) . "' AND `reported` > now() - INTERVAL 30 DAY");
     $ret = array();
     while ($obj = $res->fetch_object()) {
         if (isset($ret[$obj->version])) {
             $ret[$obj->version]++;
         } else {
             $ret[$obj->version] = 1;
         }
     }
     return $ret;
 }

 public static function getAllBoards()
 {
     $ret = array();
     $db = new DatabaseManager();
     $res = $db->query("SELECT `id` FROM `addon_boards`");
     if (!$res) {
         throw new Exception("Error getting data from database: " . $db->error());
     }
     while ($obj = $res->fetch_object()) {
         $ret[$obj->id] = BoardManager::getFromId($obj->id);
     }
     //improves performance with simultaneous connections
     $res->close();
     return $ret;
 }

 function getEntry($time, $duration)
 {
     $entry = apc_fetch('cronStat_' . $duration . '_' . $time, $success);
     if (!$success) {
         //$duration = hour, day, week, month
         $database = new DatabaseManager();
         $res = $database->query("SELECT * FROM `cron_statistics` WHERE `duration`='" . $database->sanitize($duration) . "' AND `time`='" . $database->sanitize($time) . "'");
         if ($res->num_rows == 0) {
             return false;
         } else {
             $obj = json_decode($res->fetch_object()->data);
             apc_store('cronStat_' . $duration . '_' . $time, $obj);
             return $obj;
         }
     } else {
         return $entry;
     }
 }

 public static function getUnapproved()
 {
     $ret = array();
     $db = new DatabaseManager();
     $res = $db->query("SELECT `id` FROM `addon_addons` WHERE `approved`='0'");
     while ($obj = $res->fetch_object()) {
         $ret[$obj->id] = AddonManager::getFromId($obj->id);
     }
     return $ret;
 }

<table class="commenttable">
<tbody>
<?php 
//This page is designed to be requested by ajax
//I also want it to be possible to request this content in-game.
//In the future the file that actually interacts with the database should be in /private/class, while this one processes get requests and formats data
require_once realpath(dirname(__DIR__) . "/private/class/DatabaseManager.php");
$database = new DatabaseManager();
//the "and `verified` = 1 can be deleted if we decide to force blid database entries to be unique
$result = $database->query("SELECT * FROM `addon_comments` WHERE `blid` = '" . $database->sanitize($_GET['blid']) . "' AND `verified` = 1");
if (!$result) {
    echo "Database error: " . $database->error();
} else {
    if ($result->num_rows == 0) {
        echo "<tr style=\"vertical-align:top\">";
        echo "<td colspan=\"2\" style=\"text-align: center;\">";
        echo "There are no comments here yet.";
        echo "</td></tr>";
    } else {
        require_once realpath(dirname(__DIR__) . "/private/class/UserHandler.php");
        while ($row = $result->fetch_object()) {
            $user = UserManager::getFromId($row->uid);
            echo "<tr style=\"vertical-align:top\">";
            echo "<td style=\"width: 150px;\">";
            echo "<a href=\"/user/view.php?id=" . $user->getID() . "\">" . utf8_encode($user->getUsername()) . "</a>";
            //Not sure where administrator status is stored.  My guess is 'groups' but I can't be certain.
            //At any rate, we should probably go and rethink the database tables for long term use.
            echo "<br /><span style=\"font-size: .8em;\">" . $user->getBLID() . "<br />Administrator?</span>";
            echo "</td><td>";
            echo utf8_encode($row->comment);
            echo "</td></tr>";

} else {
    $db = new DatabaseManager();
    $baseQuery = "SELECT * FROM `addon_addons` WHERE `name` LIKE '%" . $db->sanitize($_POST['query']) . "%'";
    //later on we can make it so administrators can search for deleted add-ons
    $extendedQuery = " AND `deleted` = 0";
    if (isset($_POST['blid'])) {
        try {
            require_once realpath(dirname(__DIR__) . "/private/class/UserManager.php");
            $user = UserManager::getFromBLID($_POST['blid']);
            $extendedQuery = $extendedQuery . " AND `author` = '" . $db->sanitize($_POST['blid']) . "'";
        } catch (Exception $e) {
            echo "<p>User " . htmlspecialchars($_POST['blid']) . " not found.</p>";
        }
    }
    //One of the few time's we'll use a direct SQL query on a page
    $result = $db->query($baseQuery . $extendedQuery);
    echo "<h2>Search Results for ";
    echo "\"<u>" . htmlspecialchars($_POST['query']) . "</u>\"";
    if (isset($user) && $user) {
        echo " by <a href=\"/user/view.php?id=" . $user->getID() . "\">" . htmlspecialchars($user->getUsername()) . "</a>";
    }
    echo "</h2><hr />";
    if ($result->num_rows) {
        while ($row = $result->fetch_object()) {
            echo "<p><b><a href=\"addon.php?id=" . $row->id . "\">" . htmlspecialchars($row->name) . "</a></b><br />";
            if (strlen($row->description) > 200) {
                $desc = substr($row->description, 0, 196) . " ...";
            } else {
                $desc = $row->description;
            }
            $Parsedown = new Parsedown();

 public function getCommentsRange($start, $end)
 {
     $db = new DatabaseManager();
     $commentRes = $db->query("SELECT * FROM `addon_comments` WHERE aid='" . $this->id . "' ORDER BY timestamp DESC LIMIT {$start}, {$end}");
     $comments = array();
     while ($comment = $commentRes->fetch_object()) {
         $author = UserManager::getFromId($comment->uid);
         $comments[] = new Comment(array($comment->comment, $author, $comment->timestamp));
     }
     return $comments;
 }