PHP Database::getConnection方法代码示例

 public static function createUser($username, $email, $password)
 {
     $datbase = new Database();
     $datbase->openConnection();
     mysqli_query($datbase->getConnection(), "INSERT INTO `Users`(`Username`,`Email`,`Password`) VALUES('" . mysqli_real_escape_string($datbase->getConnection(), $username) . "','" . mysqli_real_escape_string($datbase->getConnection(), $email) . "','" . mysqli_real_escape_string($datbase->getConnection(), hash("sha256", $password)) . "')");
     $datbase->closeConnection();
 }

 public function deleteAction()
 {
     $database = new Database();
     $stmt = $database->getConnection()->prepare("DELETE FROM Accounts WHERE ID = ?");
     $stmt->bind_param("i", $ID);
     $ID = $this->_params['accountID'];
     $stmt->execute();
     $stmt->close();
     $database->getConnection()->close();
 }

 public static function uploadFile($userid, $image)
 {
     $type = $image['image']['type'];
     if ($type == "image/png" || $type == "image/jpg" || $type == "image/jpeg") {
         if (move_uploaded_file($image['image']['tmp_name'], __DIR__ . "/../public/images/" . $image['image']['name'])) {
             $database = new Database();
             $database->openConnection();
             mysqli_query($database->getConnection(), "INSERT INTO `Images`(`OwnerUserID`,`ImagePath`) VALUES('" . $_SESSION['User']->getID() . "','" . mysqli_real_escape_string($database->getConnection(), "images/" . $image['image']['name']) . "')");
             $database->closeConnection();
         }
     }
 }

 public static function LoginUser($email, $password)
 {
     $datbase = new Database();
     $datbase->openConnection();
     $results = mysqli_query($datbase->getConnection(), "SELECT `id`,`Email`,`Password`,`IsAdmin` FROM `Users` WHERE `Email`='" . mysqli_real_escape_string($datbase->getConnection(), $email) . "' AND `Password`='" . mysqli_real_escape_string($datbase->getConnection(), hash("sha256", $password)) . "' LIMIT 1");
     $resultsarray = mysqli_fetch_array($results);
     $datbase->closeConnection();
     if (mysqli_num_rows($results) == 1) {
         return new User($resultsarray['id'], $resultsarray['Email'], $resultsarray['Password'], $resultsarray['IsAdmin'] == 1 ? true : false);
     } else {
         return null;
     }
 }

 /**
  * Initializes framework object. Sets up Database, Session, and Templates if applicable
  */
 public function init($cli = false)
 {
     $this->isError = false;
     $this->smarty = false;
     if (Config::get('useDatabase') !== false) {
         /**
          * Load DB
          */
         $this->loadLibrary('Database');
         $connectionParams = array('dbname' => Config::get('database.database'), 'user' => Config::get('database.username'), 'password' => Config::get('database.password'), 'host' => Config::get('database.host'));
         $this->db = Database::getConnection($connectionParams);
     }
     if (Config::get('useSession') !== false && !$cli) {
         $this->loadLibrary('Session');
         $this->session = new Session();
     }
     if (Config::get('useTemplate') !== false && !$cli) {
         $this->loadLibrary('Smarty/Smarty');
         $this->smarty = new Smarty();
         $this->smarty->template_dir = APP_PATH . '/pages';
         $this->smarty->compile_dir = STORAGE_PATH . 'cache/templates';
         $this->smarty->plugins_dir[] = Config::get('SMARTY_PLUGIN_DIRECTORY');
         $this->smarty->caching = false;
     }
 }

 function delete() {
     Category::requirePermission("DELETE");
     $link = Database::getConnection();
     $query = "DELETE FROM category WHERE id=".Database::sqlValue($this->id);
     mysql_query($query) or die(Database::formatError($query, Text::getText("QueryFailed")));
     Database::returnConnection($link);
 }

 /**
  * Gets the database time.
  * 
  * @return DateTime (database time)
  * @throws Exception (if we can't get the db time)
  * @throws DatabaseException (custom exception for if we can't get the time)
  */
 public static function getTime()
 {
     try {
         $conn = Database::getConnection();
         $commString = 'SELECT now()';
         $stmt = $conn->prepare($commString);
         if (!$stmt->execute()) {
             throw new DatabaseException('Unknown error during statement execution while getting the database time.', 1);
         } else {
             $stmt->bind_result($time);
             if ($stmt->fetch()) {
                 return $time;
             } else {
                 throw new DatabaseException('Unknown error during statement execution while getting the database time.', 1);
             }
         }
     } catch (Exception $ex) {
         throw $ex;
     } finally {
         if (isset($conn)) {
             $conn->kill($conn->thread_id);
             $conn->close();
         }
     }
 }

 /**
  * Gets all chat messages.
  * 
  * @param int $limit the max amount of messages to return
  * @return array Browser
  * @throws Exception generic error for if something goes wrong while talking to the database
  * @throws BrowserDBException error for if something goes wrong while getting the browsers
  */
 public static function getChatMessages($limit)
 {
     try {
         $conn = Database::getConnection();
         $commString = 'SELECT message_id, first_name, last_name, user, text, DATE_FORMAT(CONVERT_TZ(message_time, @@global.time_zone, ?), "%d/%m/%Y %H:%i") message_time FROM stippers_chat_messages JOIN stippers_users ON user = user_id ORDER BY message_id DESC LIMIT ?';
         $stmt = $conn->prepare($commString);
         //Check if statement could be prepared
         if ($stmt) {
             $timezone = GlobalConfig::MYSQL_TIME_ZONE;
             $stmt->bind_param('si', $timezone, $limit);
             if (!$stmt->execute()) {
                 throw new ChatDBException('Unknown error during statement execution while getting chat messages.', ChatDBException::UNKNOWNERROR);
             } else {
                 $stmt->bind_result($messageId, $firstName, $lastName, $user, $text, $messageTime);
                 $messagesUserNames = array();
                 while ($stmt->fetch()) {
                     array_push($messagesUserNames, new ChatMessage($messageId, $firstName, $lastName, $user, $text, $messageTime));
                 }
                 return $messagesUserNames;
             }
         } else {
             throw new ChatDBException('Cannot prepare statement.', ChatDBException::CANNOTPREPARESTMT);
         }
     } catch (Exception $ex) {
         throw $ex;
     } finally {
         if (isset($conn)) {
             $conn->kill($conn->thread_id);
             $conn->close();
         }
     }
 }

 public static function find(array $fields = null)
 {
     $data = [];
     $pdo = Database::getConnection();
     $columns = "ARTYPE.\"ARB-Type\",\n                    ARTYPE.\"ARB-CodeType\",\n                    ARTYPE.\"ARB-Descript\"";
     $from = "from PUB.ARTYPE";
     $where = "where ARTYPE.\"ARB-WebShow\"='yes'";
     if ($fields) {
         if (!empty($fields['CodeType'])) {
             $t = $fields['CodeType'] === 'C' ? 'C' : 'T';
             $where .= " and ARTYPE.\"ARB-CodeType\"='{$t}'";
         } elseif (!empty($fields['category'])) {
             $c = preg_replace('[^A-Z]', '', $fields['category']);
             if ($c) {
                 $columns = "distinct {$columns}";
                 $from = "from PUB.ARSECTION join PUB.ARTYPE on ARSECTION.\"ARS-Type\"=ARTYPE.\"ARB-Type\"";
                 $where = "where ARSECTION.\"ARS-WebShow\"='yes' and ARSECTION.\"ARS-Category\"='{$c}'";
             }
         }
     }
     $sql = "select {$columns} {$from} {$where}";
     $result = $pdo->query($sql);
     if (!$result) {
         print_r($pdo->errorInfo());
     }
     return $result;
 }

 function getAppointmentPatientList($patientName, $hosiptal, $appdate)
 {
     $dbConnection = new Database();
     $sql = "SELECT * from appointment where patientName LIKE :patientName and hosiptalid = :hosiptalid and appointementdate = :appdate and status = 'N'";
     //   echo $sql;
     //    echo $patientName;
     try {
         $db = $dbConnection->getConnection();
         $stmt = $db->prepare($sql);
         $stmt->bindValue("patientName", "%" . $patientName . "%", PDO::PARAM_STR);
         $stmt->bindParam("hosiptalid", $hosiptal);
         $stmt->bindParam("appdate", $appdate);
         $stmt->execute();
         $appointmentDetails = $stmt->fetchAll(PDO::FETCH_OBJ);
         $db = null;
         //$_SESSION['userDetails'] = $userDetails;
         // echo $stmt->debugDumpParams();
         //  print_r($userDetails);
         return $appointmentDetails;
     } catch (PDOException $e) {
         echo '{"error":{"text":' . $e->getMessage() . '}}';
     } catch (Exception $e1) {
         echo '{"error11":{"text11":' . $e1->getMessage() . '}}';
     }
 }

  /**
   * Define the behaviour of the database driver during the scope of the
   * life of this instance.
   *
   * @param Connection $connection
   * 
   *  Instance of the connection to be configured. Leave null to use the
   *  current default connection.
   *
   * @param mixed $bypass_queries
   * 
   *  Do not preprocess the query before execution.
   *
   * @param mixed $direct_query
   * 
   *  Prepare statements with SQLSRV_ATTR_DIRECT_QUERY = TRUE.
   *  
   * @param mixed $statement_caching
   * 
   *  Enable prepared statement caching. Cached statements are reused even
   *  after the context has expired.
   * 
   */
  public function __construct(Connection $connection = NULL, 
        $bypass_queries = NULL, 
        $direct_query = NULL, 
        $statement_caching = NULL) {

    if ($connection == NULL) {
      $connection = Database::getConnection();
    }
    
    $this->connection = $connection;

    $this->state_bypass = $this->connection->bypassQueryPreprocess;
    $this->state_direct = $this->connection->directQuery;
    $this->statement_caching = $this->connection->statementCaching;
    
    if ($bypass_queries !== NULL) {
      $this->connection->bypassQueryPreprocess = $bypass_queries;
    }

    if ($direct_query !== NULL) {
      $this->connection->directQuery = $direct_query;
    }

    if ($statement_caching !== NULL) {
      $this->connection->statementCaching = $statement_caching;
    }

  }

function content()
{
    if (isset($_POST['mode'])) {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT SUM(dc.qty) AS q, d.id, d.name, n.player, n.event, n.medal \n\t\t  FROM decks d, entries n, deckcontents dc, events e  \n      WHERE d.name LIKE ? AND n.deck=d.id \n      AND dc.deck=d.id AND dc.issideboard=0\n      AND n.event=e.name\n      GROUP BY dc.deck\n      HAVING q>=60\n      ORDER BY e.start DESC, n.medal");
        $decknamesearch = "%" . $_POST['deck'] . "%";
        $stmt->bind_param("s", $decknamesearch);
        $stmt->execute();
        $stmt->bind_result($qty, $id, $name, $player, $event, $medal);
        echo "<table align=\"center\" style=\"border-width: 0px;\" cellpadding=3>";
        while ($stmt->fetch()) {
            echo "<tr><td><a href=\"deck.php?mode=view&id={$id}\">";
            echo "{$name}</a></td>";
            echo "<td><img src=\"/images/{$medal}.gif\"></td>\n";
            echo "<td>{$player}</td>";
            echo "<td>{$event}";
            echo "</td></tr>\n";
        }
        $stmt->close();
        echo "</table>";
    } else {
        echo "<form method=\"post\" action=\"{$_SERVER['REQUEST_URI']}\">";
        echo "Enter a deck name. You may use % as a wildcard.<br><br>";
        echo "<input type=\"text\" name=\"deck\">";
        echo "<input type=\"submit\" name=\"mode\" value=\"Gimme some decks!\">";
        echo "</form>";
    }
}

 /**
  * Tests aborting of traditional SQL database systems with invalid data.
  */
 function testInsertDuplicateData()
 {
     // Try to insert multiple records where at least one has bad data.
     try {
         db_insert('test')->fields(array('name', 'age', 'job'))->values(array('name' => 'Elvis', 'age' => 63, 'job' => 'Singer'))->values(array('name' => 'John', 'age' => 17, 'job' => 'Consultant'))->values(array('name' => 'Frank', 'age' => 75, 'job' => 'Singer'))->execute();
         $this->fail('Insert succeedded when it should not have.');
     } catch (IntegrityConstraintViolationException $e) {
         // Check if the first record was inserted.
         $name = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 63))->fetchField();
         if ($name == 'Elvis') {
             if (!Database::getConnection()->supportsTransactions()) {
                 // This is an expected fail.
                 // Database engines that don't support transactions can leave partial
                 // inserts in place when an error occurs. This is the case for MySQL
                 // when running on a MyISAM table.
                 $this->pass("The whole transaction has not been rolled-back when a duplicate key insert occurs, this is expected because the database doesn't support transactions");
             } else {
                 $this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
             }
         } else {
             $this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
         }
         // Ensure the other values were not inserted.
         $record = db_select('test')->fields('test', array('name', 'age'))->condition('age', array(17, 75), 'IN')->execute()->fetchObject();
         $this->assertFalse($record, 'The rest of the insert aborted as expected.');
     }
 }

 static function getLessonsBySubjectId($subject_id)
 {
     //Clear the result
     $lessons = array();
     //get connection
     $connection = Database::getConnection();
     $query = 'SELECT * FROM lesson WHERE subject_id=' . $subject_id . ' AND deleted=false ORDER BY date DESC';
     //Run the query
     $result_obj = $connection->query($query);
     try {
         //I COULD USE A FOR AND IT WOULD BE BETTER
         //BUT IT DOESN'T WORK AND I HAVE NO TIME TO
         //FIND THE PROBLEM :)
         $i = 0;
         while ($result = $result_obj->fetch_array(MYSQLI_ASSOC)) {
             $lessons[$i] = new Lesson($result);
             $i++;
         }
         //Pass back the result
         return $lessons;
     } catch (Exception $e) {
         $_SESSION['message'] = $e->getMessage();
         //Not properly good for safety
     }
 }

function recentTable()
{
    $db = Database::getConnection();
    $result = $db->query("SELECT b.event, b.player, d.name \n    FROM entries b, decks d, events e \n    WHERE b.medal='1st' AND d.id=b.deck AND e.name=b.event\n    ORDER BY e.start DESC LIMIT 3");
    $result or die($db->error);
    echo "<table align=\"center\" width=\"90%\">\n";
    while ($row = $result->fetch_assoc()) {
        $query = "SELECT COUNT(*) AS c FROM trophies \n      WHERE event=\"{$row['event']}\"";
        $res2 = $db->query($query) or die($db->error);
        $row2 = $res2->fetch_assoc();
        if ($row2['c'] > 0) {
            echo "<tr><td colspan=\"3\" align=\"center\">";
            echo "<a href=\"/gatherling/deck.php?mode=view&";
            echo "event={$row['event']}\">";
            echo "<img src=\"/gatherling/displayTrophy.php?";
            echo "event={$row['event']}\" style=\"border-width: 0px;\"></a>";
            echo "</td></tr>\n";
        }
        echo "<tr><td><b><a href=\"/gatherling/profile.php?player=";
        echo "{$row['player']}\">{$row['player']}</a></td>";
        echo "<td><i><a href=\"/gatherling/deck.php?";
        echo "mode=view&event={$row['event']}\">{$row['name']}</a></td>";
        echo "<td><a href=\"/gatherling/eventreport.php?event={$row['event']}\">{$row['event']}</a></td></tr>\n";
    }
    echo "</table>";
    $result->close();
}