PHP Database::getDB方法代码示例

function moveUser($action, $id, $keyName)
{
    echo $keyName . "<br>";
    //Make connection to database
    $dbh = Database::getDB();
    if ($action == "delete") {
        //Construct the SQL query with variables
        $qry = "DELETE FROM ";
        $qry .= $_SESSION['tableName'];
        $qry .= " WHERE " . $keyName . "=" . $id;
        //Prepare the query
        $stmt = $dbh->prepare($qry);
        //execute the query
        $stmt->execute();
        //Unset the qry once finished
        unset($qry);
        unset($stmt);
    } else {
        /**
         * This one is a little trickier.  First create SQL query to get the values of the primary key.
         * Then delete the object from the DB.  After you delete, you insert into the new table with
         * the values obtained earlier.  Might be a faster way to do this, but this is fine for now.
         */
        $qry = "SELECT * FROM " . $_SESSION['tableName'] . " WHERE " . $keyName;
        $qry .= "=" . $id;
        //Prepare the query
        $stmt = $dbh->prepare($qry);
        //execute the query
        $stmt->execute();
        //Set the fetch mode.  BOTH means its named keys and by numbers.
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        //Put the results into an array
        $row = $stmt->fetch();
        //Unset the qry once finished
        unset($qry);
        unset($stmt);
        //Construct the 2nd SQL query with variables
        $qry = "DELETE FROM ";
        $qry .= $_SESSION['tableName'];
        $qry .= " WHERE " . $keyName . "=" . $id;
        //Prepare the query
        $stmt = $dbh->prepare($qry);
        //execute the query
        $stmt->execute();
        //Unset the qry once finished
        unset($qry);
        unset($stmt);
        //Construct the 2rd SQL query with variables
        $qry = "INSERT INTO " . $action . " (fullName, firstName, middleName, lastName, probability, indexFound, namesFound)";
        $qry .= " VALUES ('" . $row['fullName'] . "', '" . $row['firstName'] . "', '" . $row['middleName'] . "', '";
        $qry .= $row['lastName'] . "', '" . $row['probability'] . "', '" . $row['indexFound'] . "', '" . $row['namesFound'] . "')";
        //Prepare the query
        $stmt = $dbh->prepare($qry);
        //execute the query
        $stmt->execute();
        //Unset the qry once finished
        unset($qry);
        unset($stmt);
    }
}

    /**
     * Get the statistics for a certain month, grouped by day and host
     * @param	date	Minimum date
     * @return	Array of data
     */
    public static function month_by_day($start_date)
    {
        // Calculate end of this month
        $end_date = mktime(23, 59, 59, date('m', $start_date) + 1, 0, date('Y', $start_date));
        $query = Database::getDB()->prepare('
			SELECT ip, UNIX_TIMESTAMP(date) AS date, SUM(bytes_out) bytes_out, SUM(bytes_in) bytes_in
			FROM ' . Config::$database['prefix'] . 'combined
			WHERE date BETWEEN :start_date AND :end_date
			GROUP BY ip, DAY(date)
			ORDER BY date, ip');
        $query->execute(array('start_date' => Database::date($start_date), 'end_date' => Database::date($end_date)));
        // Start with an empty array for all the days of the month
        $day_base = date('Y-m-', $start_date);
        $days = array();
        for ($i = 1, $count = date('t', $start_date); $i <= $count; $i++) {
            $days[$day_base . str_pad($i, 2, '0', STR_PAD_LEFT)] = 0;
        }
        $data = array();
        while ($row = $query->fetchObject()) {
            // Check if this IP is on the list of IPs that should be shown
            if (!empty(Config::$include_ips) && !in_array($row->ip, Config::$include_ips)) {
                continue;
            }
            // Does this host have a data entry yet?
            if (!isset($data[$row->ip])) {
                $data[$row->ip] = $days;
            }
            $row->bytes_total = $row->bytes_in + $row->bytes_out;
            $data[$row->ip][date('Y-m-d', $row->date)] = $row->bytes_total;
        }
        return $data;
    }

 public static function getMapByName($name)
 {
     $validKeys = array("africa" => "Africa", "antartica" => "Antartica", "asia" => "Asia", "australia" => "Australia", "europe" => "Europe", "north-america" => "North America", "south-america" => "South America");
     if (!array_key_exists($name, $validKeys)) {
         return false;
     }
     $name = MapDatabase::sanitize($name);
     $selectQuery = "SELECT * FROM Maps WHERE mapName=:name";
     try {
         # Get Database
         $db = Database::getDB();
         # Get Map
         $statement = $db->prepare($selectQuery);
         $statement->bindParam(":name", $validKeys[$name]);
         $statement->execute();
         $mapSets = $statement->fetchAll(PDO::FETCH_ASSOC);
         $statement->closeCursor();
         foreach ($mapSets as $mapSet) {
             $mapID = $mapSet["mapID"];
             $mapName = $mapSet["mapName"];
             $mapURL = $mapSet["mapURL"];
         }
         # Create Map
         $map = new Map($mapID, $mapName, $mapURL);
         return $map;
     } catch (Exception $e) {
         return false;
     }
 }

 public static function getRobotDataRowSetsBy($type = null, $value = null)
 {
     $allowedTypes = ['robotId', 'robot_name', 'status'];
     $robotDataRowSets = array();
     try {
         $db = Database::getDB();
         $query = "SELECT robotId, robot_name, status FROM RobotData";
         if (!is_null($type)) {
             if (!in_array($type, $allowedTypes)) {
                 throw new PDOException("{$type} not an allowed search criterion for RobotData");
             }
             $query = $query . " WHERE ({$type} = :{$type})";
             $statement = $db->prepare($query);
             $statement->bindParam(":{$type}", $value);
         } else {
             $query = $query . " ORDER BY robotId ASC";
             $statement = $db->prepare($query);
         }
         $statement->execute();
         $robotDataRowSets = $statement->fetchAll(PDO::FETCH_ASSOC);
         $statement->closeCursor();
     } catch (Exception $e) {
         echo "<p>Error getting robot data rows by {$type}: " . $e->getMessage() . "</p>";
     }
     return $robotDataRowSets;
 }

 function execute(CommandContext $context)
 {
     $image_url = $context->get('test_name');
     $id = $context->get('edit');
     $event_name = $context->get('event_name');
     $event_location = $context->get('event_location');
     $event_date = strtotime($context->get('event_date')) + 86399;
     $ticket_prices = $context->get('ticket_prices');
     $ticket_location = $context->get('ticket_location');
     $open_time = $context->get('open_time');
     $start_time = $context->get('start_time');
     $event_restrictions = $context->get('event_restrictions');
     $artist_details = $context->get('event_details');
     if ($_FILES['event_image']['size'] > 0 and $_FILES['event_image']['size'] < 2097152) {
         $tempFile = $_FILES['event_image']['tmp_name'];
         $targetPath = PHPWS_SOURCE_DIR . "mod/events/images/";
         $targetFile = $targetPath . $_FILES['event_image']['name'];
         $image_url = "mod/events/images/" . $_FILES['event_image']['name'];
         move_uploaded_file($tempFile, $targetFile);
     }
     $db = \Database::getDB();
     $pdo = $db->getPDO();
     $query = "UPDATE events_events set eventname = :event_name, eventlocation = :event_location, eventdate = :event_date, ticketprices = :ticket_prices, ticketlocation = :ticket_location, \n\t\t\t\t\topentime = :open_time, starttime = :start_time, eventrestrictions = :event_restrictions, artistdetails = :artist_details,  imageurl = :image_url where id = :id";
     $sth = $pdo->prepare($query);
     $sth->execute(array('event_name' => $event_name, 'event_location' => $event_location, 'event_date' => $event_date, 'ticket_prices' => $ticket_prices, 'ticket_location' => $ticket_location, 'open_time' => $open_time, 'start_time' => $start_time, 'event_restrictions' => $event_restrictions, 'artist_details' => $artist_details, 'id' => $id, 'image_url' => $image_url));
     header('Location: ./?action=ShowAdminHome');
 }

function makeDB($dbName)
{
    // Creates a database named $dbName for testing and returns connection
    $db = Database::getDB('');
    try {
        $st = $db->prepare("DROP DATABASE if EXISTS {$dbName}");
        $st->execute();
        $st = $db->prepare("CREATE DATABASE {$dbName}");
        $st->execute();
        $st = $db->prepare("USE {$dbName}");
        $st->execute();
        $st = $db->prepare("DROP TABLE if EXISTS Users");
        $st->execute();
        $st = $db->prepare("CREATE TABLE Users (\r\n\t\t\t\t\tuserId             int(11) NOT NULL AUTO_INCREMENT,\r\n\t\t\t\t\tuserName           varchar (255) UNIQUE NOT NULL COLLATE utf8_unicode_ci,\r\n\t\t\t\t\tpassword           varchar(255) COLLATE utf8_unicode_ci,\r\n\t\t\t\t    dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\r\n\t\t\t\t\tPRIMARY KEY (userId)\r\n\t\t\t)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
        $st->execute();
        $st = $db->prepare("CREATE TABLE Submissions (\r\n\t\t\t  \t             submissionId       int(11) NOT NULL AUTO_INCREMENT,\r\n\t\t\t\t             userId             int(11) NOT NULL COLLATE utf8_unicode_ci,\r\n\t\t\t\t             assignmentNumber   int COLLATE utf8_unicode_ci,\r\n\t\t\t\t             submissionFile     varchar (255) UNIQUE NOT NULL COLLATE utf8_unicode_ci,\r\n\t\t\t\t             dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\r\n\t\t\t\t             PRIMARY KEY (submissionId),\r\n\t\t\t\t             FOREIGN KEY (userId) REFERENCES Users(userId)\r\n\t\t              )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
        $st->execute();
        $sql = "INSERT INTO Users (userId, userName, password) VALUES\r\n\t\t                          (:userId, :userName, :password)";
        $st = $db->prepare($sql);
        $st->execute(array(':userId' => 1, ':userName' => 'Kay', ':password' => 'xxx'));
        $st->execute(array(':userId' => 2, ':userName' => 'John', ':password' => 'yyy'));
        $st->execute(array(':userId' => 3, ':userName' => 'Alice', ':password' => 'zzz'));
        $st->execute(array(':userId' => 4, ':userName' => 'George', ':password' => 'www'));
        $sql = "INSERT INTO Submissions (submissionId, userId, assignmentNumber, submissionFile) \r\n\t                             VALUES (:submissionId, :userId, :assignmentNumber, :submissionFile)";
        $st = $db->prepare($sql);
        $st->execute(array(':submissionId' => 1, ':userId' => 1, ':assignmentNumber' => '1', ':submissionFile' => 'Kay1.txt'));
        $st->execute(array(':submissionId' => 2, ':userId' => 1, ':assignmentNumber' => '2', ':submissionFile' => 'Kay2.txt'));
        $st->execute(array(':submissionId' => 3, ':userId' => 2, ':assignmentNumber' => '1', ':submissionFile' => 'John1.txt'));
    } catch (PDOException $e) {
        echo $e->getMessage();
        // not final error handling
    }
    return $db;
}

 function execute(CommandContext $context)
 {
     if (!\UserStatus::isAdmin()) {
         header('Location: ./?action=ShowGuestHome');
     }
     $image_url = "https://placeholdit.imgix.net/~text?txtsize=33&txt=250%C3%97150&w=250&h=150";
     if ($_FILES['event_image']['size'] > 0 and $_FILES['event_image']['size'] < 2097152) {
         $tempFile = $_FILES['event_image']['tmp_name'];
         $targetPath = PHPWS_SOURCE_DIR . "mod/events/images/";
         $targetFile = $targetPath . $_FILES['event_image']['name'];
         $image_url = "mod/events/images/" . $_FILES['event_image']['name'];
         move_uploaded_file($tempFile, $targetFile);
     }
     var_dump($_POST);
     var_dump($context);
     exit;
     $event_name = $context->get('event_name');
     $event_location = $context->get('event_location');
     $event_date = strtotime($context->get('event_date')) + 86399;
     $ticket_prices = $context->get('ticket_prices');
     $ticket_location = $context->get('ticket_location');
     $open_time = $context->get('open_time');
     $start_time = $context->get('start_time');
     $event_restrictions = $context->get('event_restrictions');
     $artist_details = $context->get('event_details');
     $db = \Database::getDB();
     $pdo = $db->getPDO();
     $query = "INSERT INTO events_events (id, eventname, eventlocation, eventdate, ticketprices, ticketlocation, opentime, starttime, eventrestrictions, artistdetails, imageurl)\n\t\t\t\t\tVALUES (nextval('events_seq'), :event_name, :event_location, :event_date, :ticket_prices, :ticket_location, :open_time, :start_time, :event_restrictions, :artist_details, :image_url)";
     $sth = $pdo->prepare($query);
     $sth->execute(array('event_name' => $event_name, 'event_location' => $event_location, 'event_date' => $event_date, 'ticket_prices' => $ticket_prices, 'ticket_location' => $ticket_location, 'open_time' => $open_time, 'start_time' => $start_time, 'event_restrictions' => $event_restrictions, 'artist_details' => $artist_details, 'image_url' => $image_url));
     header('Location: ./?action=ShowAdminHome');
 }

 public static function spotReport($game_id)
 {
     $db = \Database::getDB();
     $lot = $db->addTable('tg_lot');
     $lot->addField('title', 'lot_title');
     $lot->addOrderBy('title');
     $spot = $db->addTable('tg_spot');
     $spot->addField('number', 'spot_number');
     $spot->addOrderBy('number');
     $c1 = $db->createConditional($spot->getField('lot_id'), $lot->getField('id'), '=');
     $db->joinResources($spot, $lot, $c1);
     $lottery = $db->addTable('tg_lottery');
     $lottery->addField('id', 'lottery_id');
     $c2a = $db->createConditional($spot->getField('id'), $lottery->getField('spot_id'), '=');
     $c2b = $db->createConditional($lottery->getField('game_id'), $game_id);
     $c2 = $db->createConditional($c2a, $c2b, 'and');
     $db->joinResources($spot, $lottery, $c2, 'left');
     $student = $db->addTable('tg_student');
     $student->addField('first_name');
     $student->addField('last_name');
     $c3a = $db->createConditional($lottery->getField('student_id'), $student->getField('id'), '=');
     $c3b = $db->createConditional($lottery->getField('picked_up'), 1);
     $c3 = $db->createConditional($c3a, $c3b, 'and');
     $db->joinResources($lottery, $student, $c3, 'left');
     $result = $db->select();
     $template = new \Template(array('rows' => $result));
     self::fillTitle($template, $game_id);
     $template->setModuleTemplate('tailgate', 'Admin/Report/spots.html');
     $content = $template->get();
     self::downloadPDF($content, 'Spot_Report.pdf');
 }

 public function getReserveQuestions($room_id)
 {
     $pdo = Database::getDB();
     // $number_question = 1;
     $stmt = $pdo->prepare("SELECT * FROM reserve_question WHERE room_num = ? AND difficulty = 'EASY' ORDER BY question_num ASC");
     $stmt->execute(array($room_id));
     $result = $stmt->fetchAll();
     $brain_Questions = array();
     // $remainder = $result/$number_question;
     foreach ($result as $b) {
         $brain_question = new brain_Question();
         $brain_question->setQuestion_id($b['question_id']);
         $brain_question->setQuestion_num($b['question_num']);
         $stmt = $pdo->prepare("SELECT * FROM brain_question, easy_choice WHERE brain_question.bid AND easy_choice.qid = ?");
         $stmt->execute(array($b['question_id']));
         $row = $stmt->fetch();
         $brain_question->setQuestion($row['question']);
         $brain_question->setLevel($row['level']);
         $brain_question->setSubject($row['subject']);
         $brain_question->setfile_path($row['question_path']);
         $brain_question->setHint($row['hint']);
         $brain_question->setCorrect($row['correct']);
         $brain_question->setChoiceB($row['choiceB']);
         $brain_question->setChoiceC($row['choiceC']);
         $brain_question->setChoiceD($row['choiceD']);
         $brain_Questions[] = $brain_question;
     }
     return $brain_Questions;
 }

 public static function getUserRowSetsBy($type, $value, $column)
 {
     // Returns the rows of Users whose $type field has value $value
     $allowedTypes = ["userId", "userName"];
     $allowedColumns = ["userId", "userName", "*"];
     $userRowSets = NULL;
     try {
         if (!in_array($type, $allowedTypes)) {
             throw new PDOException("{$type} not an allowed search criterion for User");
         } elseif (!in_array($column, $allowedColumns)) {
             throw new PDOException("{$column} not a column of User");
         }
         $query = "SELECT {$column} FROM Users WHERE ({$type} = :{$type})";
         $db = Database::getDB();
         $statement = $db->prepare($query);
         $statement->bindParam(":{$type}", $value);
         $statement->execute();
         $userRowSets = $statement->fetchAll(PDO::FETCH_ASSOC);
         $statement->closeCursor();
     } catch (PDOException $e) {
         // Not permanent error handling
         echo "<p>Error getting user rows by {$type}: " . $e->getMessage() . "</p>";
     }
     return $userRowSets;
 }

 public static function deleteSpots($lot_id)
 {
     $db = \Database::getDB();
     $tbl = $db->addTable('tg_spot');
     $tbl->addFieldConditional('lot_id', $lot_id);
     $db->delete();
 }

 public static function getUserBy($type, $value)
 {
     // Returns a User object whose $type field has value $value
     $allowed = ["userId", "userName"];
     $user = NULL;
     try {
         if (!in_array($type, $allowed)) {
             throw new PDOException("{$type} not an allowed search criterion for User");
         }
         $query = "SELECT * FROM Users WHERE ({$type} = :{$type})";
         $db = Database::getDB();
         $statement = $db->prepare($query);
         $statement->bindParam(":{$type}", $value);
         $statement->execute();
         $userRows = $statement->fetch(PDO::FETCH_ASSOC);
         if (!empty($userRows)) {
             $user = new User($userRows);
         }
         $statement->closeCursor();
     } catch (PDOException $e) {
         // Not permanent error handling
         echo "<p>Error getting user by {$type}: " . $e->getMessage() . "</p>";
     }
     return $user;
 }

 public static function getRobotAssocsRowsBy($type, $value)
 {
     $allowedTypes = ["robotAssocId", "robotId", "creatorId"];
     $robotAssocsRows = array();
     try {
         $db = Database::getDB();
         $query = "SELECT robotAssocId, robotId, creatorId from RobotAssocs";
         if (!is_null($type)) {
             if (!in_array($type, $allowedTypes)) {
                 throw new PDOException("{$type} is not an allowed search criterion for RobotAssocs");
             }
             $query = $query . " WHERE ({$type} = :{$type})";
             $statement = $db->prepare($query);
             $statement->bindParam(":{$type}", $value);
         } else {
             $query = $query . " ORDER BY robotAssocId ASC";
             $statement = $db->prepare($query);
         }
         $statement->execute();
         $robotAssocsRows = $statement->fetchAll(PDO::FETCH_ASSOC);
         $statement->closeCursor();
     } catch (Exception $e) {
         echo "<p>Error getting robot assoc rows by {$type}: " . $e->getMessage() . "</p>";
     }
     return $robotAssocsRows;
 }

 public static function formatDepartmentList($row)
 {
     $db = \Database::getDB();
     $tbl = $db->addTable('systems_department');
     $tbl->addField('display_name');
     $tbl->addField('parent_department');
     $tbl->addField('description');
     $tbl->addField('active');
     $tbl->addField('id');
     $row['action'] = '<button id="edit-department" type="button" class="btn btn-sm btn-default" onclick="editDepartment(' . $row['id'] . ')" >Edit</button>';
     if ($row['active']) {
         $row['dept_active'] = 'Yes';
     } else {
         $row['dept_active'] = 'No';
     }
     if ($row['parent_department']) {
         $dept_id = $row['parent_department'];
         $tbl->addFieldConditional('id', $dept_id, '=');
         $dep_result = $db->select();
         if ($dep_result) {
             $row['parent_department'] = $dep_result[0]['display_name'];
         }
     }
     return $row;
 }

 public static function getBetRowSetsBy($type = null, $value = null)
 {
     // Returns the rows of bets whose $type field has value $value
     $allowedTypes = ["id", "who", "game"];
     $betRowSets = array();
     try {
         $db = Database::getDB();
         $query = "SELECT * FROM bets";
         if (!is_null($type)) {
             if (!in_array($type, $allowedTypes)) {
                 throw new PDOException("{$type} not an allowed search criterion for bets");
             }
             $query = $query . " WHERE ({$type} = :{$type})";
             $statement = $db->prepare($query);
             $statement->bindParam(":{$type}", $value);
         } else {
             $statement = $db->prepare($query);
         }
         $statement->execute();
         $betRowSets = $statement->fetchAll(PDO::FETCH_ASSOC);
         $statement->closeCursor();
     } catch (Exception $e) {
         // Not permanent error handling
         echo "<p>Error getting bets rows by {$type}: " . $e->getMessage() . "</p>";
     }
     return $betRowSets;
 }