PHP core\Database类代码示例

 private static function clearTables(\Core\Database $db)
 {
     $clearScript = new \Helpers\TablesClearScript();
     foreach ($clearScript as $statement) {
         $db->exec($statement, array());
     }
 }

 /**
  * @param float $latitude
  * @param float $longitude
  * @param $radio
  */
 public static function getRepublicas($latitude, $longitude, $radius, Database &$database)
 {
     $latitude = filter_var($latitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
     $longitude = filter_var($longitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
     $radius = filter_var($radius, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
     //Haversine formula
     $query = $database->prepare('
         SELECT *, (6371 * acos(
             cos(radians( :latitude )) * cos(radians(latitude)) *
             cos(radians(longitude) - radians( :longitude )) + 
             sin(radians( :latitude )) * sin(radians(latitude))
         ))
         AS distance
         FROM republicas
         HAVING distance < :radius
         ORDER BY distance
     ');
     $query->bindParam(':latitude', $latitude);
     $query->bindParam(':longitude', $longitude);
     $query->bindParam(':radius', $radius);
     $query->execute();
     $json = array();
     while ($item = $query->fetch(Database::FETCH_ASSOC)) {
         $json[] = $item;
     }
     return json_encode($json);
 }

 /**
  * Static method get
  *
  * @param  array $group
  * @return \helpers\database
  */
 public static function get($group = null)
 {
     // Determining if exists or it's not empty, then use default group defined in config
     $group = !$group ? array('type' => DB_TYPE, 'host' => DB_HOST, 'name' => DB_NAME, 'user' => DB_USER, 'pass' => DB_PASS, 'port' => DB_PORT) : $group;
     // Group information
     $type = $group['type'];
     $host = $group['host'];
     $name = $group['name'];
     $user = $group['user'];
     $pass = $group['pass'];
     $port = $group['port'];
     // ID for database based on the group information
     $id = "{$type}.{$host}.{$port}.{$name}.{$user}.{$pass}";
     // Checking if the same
     if (isset(self::$instances[$id])) {
         return self::$instances[$id];
     }
     try {
         // I've run into problem where
         // SET NAMES "UTF8" not working on some hostings.
         // Specifiying charset in DSN fixes the charset problem perfectly!
         $instance = new Database("{$type}:host={$host};port={$port};dbname={$name};charset=utf8", $user, $pass);
         $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         // Setting Database into $instances to avoid duplication
         self::$instances[$id] = $instance;
         return $instance;
     } catch (PDOException $e) {
         //in the event of an error record the error to ErrorLog.html
         Logger::newMessage($e);
         Logger::customErrorMsg();
     }
 }

 public function getAll(\Core\Database $db)
 {
     $recs = $db->fetchAllAssoc("select * from rooms", array());
     $result = array();
     for ($i = 0; $i < count($recs); $i++) {
         $result[] = new \Application\RoomItem($recs[$i]);
     }
     return $result;
 }

 protected function makeUpdateQuery($table_name, $fields_to_save, array $where_condition, \Core\Database $db)
 {
     $value_equation_list_imploded = $this->makeEquationString(',', $fields_to_save);
     $value_list = $this->makeValueVarArray($fields_to_save);
     $sql = "update {$table_name} set {$value_equation_list_imploded} where " . $this->makeEquationString(' and ', $where_condition);
     $value_list = array_merge($value_list, $this->makeValueVarArray($where_condition));
     //error_log("\nSQL:" . print_r($sql, true) . "\nvalues:" . print_r($value_list, true), 3, "my_errors.txt");
     return $db->exec($sql, $value_list);
 }

 public static function getUserId($username, $password)
 {
     $result = Database::getInstance()->prepare("SELECT id FROM `users` where `username` = :username AND `password`=:password OR `email`=:username AND `password`=:password");
     $result->execute(array(':username' => $username, ':password' => $password));
     $row = $result->fetch(PDO::FETCH_ASSOC);
     return $row['id'];
 }

 /**
  * Get use user count
  * @return int
  */
 public static function GetUseUserCount()
 {
     $statement = Database::prepare("SELECT count(*) FROM member WHERE lastConnTime > 0");
     $statement->execute();
     $count = $statement->fetch(\PDO::FETCH_NUM);
     return $count[0] == null ? 0 : $count[0];
 }

 /**
  * Create a new instance of the database helper.
  */
 public function __construct()
 {
     /**
      * connect to PDO here.
      */
     $this->db = \Core\Database::get();
 }

 public static function queryUrl($url)
 {
     $stm = Database::sql('SELECT `id`, `alias`, `url`, `status`, `add_time`, `click_num` FROM `url_list` WHERE `url`=?');
     $stm->bindValue(1, $url, Database::PARAM_STR);
     $stm->execute();
     return $stm->fetchObject(__CLASS__);
 }

 /**
  * Create a new PageData object.
  * @param string $tableName Target table name
  * @param string $extras Such as where statement or order statement
  * @param array $column Column names needs to be fetch
  */
 public function __construct($tableName, $extras = '', $column = array('*'))
 {
     $columns = '`' . implode('`, `', $column) . '`';
     $this->countQuery = Database::getInstance()->prepare("SELECT COUNT(*) FROM `{$tableName}` {$extras}");
     $this->query = Database::getInstance()->prepare("SELECT {$columns} FROM `{$tableName}` {$extras} LIMIT :pageDataStart,:pageDataRPP");
     if ($_GET['page']) {
         $this->setPage($_GET['page']);
     }
 }

 public function save($mode = self::SAVE_AUTO)
 {
     $map = array();
     $reflection = new ReflectionObject($this);
     $reflectionProp = $reflection->getProperties(ReflectionProperty::IS_PROTECTED | ReflectionProperty::IS_PUBLIC);
     foreach ($reflectionProp as $property) {
         if (strpos($property->getDocComment(), '@ignore')) {
             continue;
         }
         $propertyName = $property->getName();
         if ($propertyName == 'primaryKey') {
             continue;
         }
         if ($property->isProtected()) {
             $property->setAccessible(true);
         }
         $propertyValue = $property->getValue($this);
         $map[$propertyName] = $propertyValue;
     }
     $primaryKey = $this->getPrimaryKeyName($reflection);
     $identifier = $map[$primaryKey];
     unset($map[$primaryKey]);
     $tableName = $this->getTableName($reflection);
     if ($mode == self::SAVE_UPDATE || $identifier && $mode != self::SAVE_INSERT) {
         $sql = "UPDATE `{$tableName}` SET ";
         foreach ($map as $key => $value) {
             $sql .= "`{$key}` = :{$key},";
         }
         $sql = rtrim($sql, ',');
         $sql .= " WHERE {$primaryKey} = :id";
         $statement = Database::getInstance()->prepare($sql);
         $statement->bindValue(':id', $identifier);
         foreach ($map as $key => $value) {
             $statement->bindValue(":{$key}", $value);
         }
     } else {
         $sql = "INSERT INTO `{$tableName}` SET ";
         foreach ($map as $key => $value) {
             $sql .= "`{$key}` = :{$key},";
         }
         $sql = rtrim($sql, ',');
         $statement = Database::getInstance()->prepare($sql);
         foreach ($map as $key => $value) {
             $statement->bindValue(":{$key}", $value);
         }
     }
     $statement->execute();
     if (!$identifier) {
         $insertId = Database::getInstance()->lastInsertId();
         if ($insertId) {
             $reflection->getProperty($primaryKey)->setValue($this, $insertId);
         }
     }
 }

 /**
  * @param (int|string)[] $answers
  *
  * @return boolean
  */
 public static function save(array $answers, Database &$database)
 {
     $options = array('dificuldade' => FILTER_SANITIZE_STRING, 'explicacao_dificuldade' => FILTER_SANITIZE_STRING, 'encontrou' => FILTER_SANITIZE_STRING, 'aluno_EACH' => FILTER_SANITIZE_STRING, 'indicaria' => FILTER_SANITIZE_STRING, 'referencia' => FILTER_SANITIZE_STRING, 'nota_design' => FILTER_SANITIZE_NUMBER_INT, 'nota_funcionalidades' => FILTER_SANITIZE_NUMBER_INT, 'nota_acessibilidade' => FILTER_SANITIZE_NUMBER_INT, 'nota_insercao_reps' => FILTER_SANITIZE_NUMBER_INT, 'info_adicional' => FILTER_SANITIZE_STRING);
     $answers = filter_var_array($answers, $options);
     $query = $database->prepare('
         INSERT INTO feedback (
             dificuldade, explicacao_dificuldade, encontrou, aluno_EACH,
             indicaria, referencia, nota_design, nota_funcionalidades,
             nota_acessibilidade, nota_insercao_reps, info_adicional
         ) VALUES (
             :dificuldade, :explicacao_dificuldade, :encontrou, :aluno_EACH,
             :indicaria, :referencia, :nota_design, :nota_funcionalidades,
             :nota_acessibilidade, :nota_insercao_reps, :info_adicional
         )
     ');
     do {
         $query->bindParam(':' . key($answers), current($answers));
     } while (next($answers) !== false);
     return $query->execute();
 }

 public function run()
 {
     // 清理一个月前的数据
     $mon = time() - 2592000;
     $stn = Database::sql('DELETE FROM `card` WHERE add_time<? AND status=0');
     $stn->bindValue(1, $mon, Database::PARAM_INT);
     $stn->execute();
     $stn = Database::sql("DELETE FROM `invite` WHERE dateLine<? AND status=1");
     $stn->bindValue(1, $mon, Database::PARAM_INT);
     $stn->execute();
 }

 /**
  * Migrate current database
  * @param $dropTable bool drop the table
  */
 public function execute($dropTable = false)
 {
     $this->database = Database::getInstance();
     $modelDir = "Application/Model";
     $file = opendir($modelDir);
     // there is fileName
     while (($fileName = readdir($file)) !== false) {
         if (substr($fileName, -4) == ".php") {
             $this->migrateTable($modelDir . "/" . $fileName, $dropTable);
         }
     }
 }

 public static function getById($id)
 {
     try {
         $connection = Database::instance();
         $sql = "SELECT * from usuarios WHERE id = ?";
         $query = $connection->prepare($sql);
         $query->bindParam(1, $id, \PDO::PARAM_INT);
         $query->execute();
         return $query->fetch();
     } catch (\PDOException $e) {
         print "Error!: " . $e->getMessage();
     }
 }