PHP DatabaseConnection::prepare方法代码示例

 /**
  * Populates tracks collection with all library tracks matching criteria.
  *
  * @param array $parameters Requested parameters
  *
  * @return bool true if the database read is ok, false otherwise
  */
 public function populateTracks($parameters)
 {
     require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
     $connection = new DatabaseConnection();
     //handle requested parameters
     $sqlCondition = '';
     foreach ($parameters as $parameter => $value) {
         if (isset($value)) {
             switch ($parameter) {
                 case 'trackTitle':
                     $sqlCondition .= ' AND `track`.`title` LIKE :trackTitle';
                     break;
                 case 'artistName':
                     $sqlCondition .= ' AND `artist`.`name` LIKE :artistName';
                     break;
                 case 'albumName':
                     $sqlCondition .= ' AND `album`.`name` LIKE :albumName';
                     break;
             }
         }
     }
     //prepare query
     $query = $connection->prepare('SELECT `track`.`id`, `track`.`title`, `track`.`artist`, `artist`.`name` AS `artistName`, `track`.`album`, `album`.`name` AS `albumName`, `track`.`id` AS `file`, `cover`.`albumId` AS `coverId` FROM `track`, `artist` ,`album` LEFT JOIN `cover` ON `album`.`id`=`cover`.`albumId` AND `cover`.`status` = 1 WHERE `track`.`artist`=`artist`.`id` AND `track`.`album`=`album`.`id`' . $sqlCondition . ' ORDER BY `additionTime` DESC;');
     //add query criteria value
     foreach ($parameters as $parameter => $value) {
         if (isset($value)) {
             switch ($parameter) {
                 case 'trackTitle':
                     $query->bindValue(':trackTitle', "%{$value}%", PDO::PARAM_STR);
                     break;
                 case 'artistName':
                     $query->bindValue(':artistName', "%{$value}%", PDO::PARAM_STR);
                     break;
                 case 'albumName':
                     $query->bindValue(':albumName', "%{$value}%", PDO::PARAM_STR);
                     break;
             }
         }
     }
     if ($query->execute()) {
         $this->tracks = $query->fetchAll(PDO::FETCH_CLASS);
         foreach ($this->tracks as $track) {
             $trackStructured = new Track();
             $track = $trackStructured->structureData($track);
         }
         //return true to indicate tracks was successfully read
         return true;
     }
     //return false to indicate an error occurred while reading tracks
     return false;
 }

 /**
  * Deletes a specific user's track.
  *
  * @return bool true if the track is deleted from the user's playlist, false on error
  */
 public function delete()
 {
     if (isset($this->userId, $this->sequence)) {
         require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
         $connection = new DatabaseConnection();
         $query = $connection->prepare('DELETE FROM `playlist` WHERE `userId`=:userId AND `sequence`=:sequence;');
         $query->bindValue(':userId', $this->userId, PDO::PARAM_INT);
         $query->bindValue(':sequence', $this->sequence, PDO::PARAM_INT);
         //return true to indicate a successful track deletion
         return $query->execute() && $query->rowCount() > 0;
     }
     //return false to indicate an error occurred while deleting track from user's playlist
     return false;
 }

 /**
  * Create SQL schema and tables.
  * @param  string  $dbEngine     Database engine (sqlite or mysql, default is sqlite)
  * @param  string  $wmpDbName    Database name (only for MySQL)
  * @param  string  $adminUserPwd Admin user account password
  * @return boolean True if schema is set
  */
 public function createSchema($dbEngine = 'sqlite', $wmpDbName = 'wmp', $adminUserPwd = 'nimda')
 {
     try {
         //connect to database
         require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
         $connection = new DatabaseConnection();
         //clean previous schema (MySQL only)
         if ($dbEngine == 'mysql') {
             //get all tables
             $query = $connection->prepare("SELECT table_name FROM information_schema.TABLES WHERE table_schema = :schema;");
             $query->bindValue('schema', $wmpDbName);
             if (!$query->execute()) {
                 throw new RuntimeException('Schema drop has failed: unable to get tables');
             }
             $tables = $query->fetchAll();
             //disable foreign keys before droping table
             $query = $connection->prepare("SET FOREIGN_KEY_CHECKS=0;");
             $query->execute();
             //drop each table
             foreach ($tables as $table) {
                 $table_name = $table['table_name'];
                 $query = $connection->prepare("DELETE FROM {$wmpDbName}.{$table_name} WHERE 1;");
                 $query->execute();
                 $query = $connection->prepare("DROP TABLE {$wmpDbName}.{$table_name};");
                 if (!$query->execute()) {
                     throw new RuntimeException('Schema drop has failed on table ' . $table_name);
                 }
             }
             //enable foreign keys
             $query = $connection->prepare("SET FOREIGN_KEY_CHECKS=1;");
             $query->execute();
         }
         //load language dependant script
         $sqlFilename = $_SERVER['DOCUMENT_ROOT'] . '/server/configuration/create-' . $dbEngine . '.sql';
         //split each query (separated by the ";EOL")
         $array = explode(";\n", file_get_contents($sqlFilename));
         $nbLines = count($array);
         for ($i = 0; $i < $nbLines; ++$i) {
             //remove comments (-- and text behind) and handle line
             $queryString = preg_replace('/--.*$/m', '', filter_var($array[$i]));
             //remove EOL
             $queryString = str_replace("\n", ' ', $queryString);
             if ($queryString !== '' && $queryString !== ' ') {
                 //add end a ";" at the end of the query
                 $queryString .= ';';
                 //replace the default schema 'wmp' with the user's one (MySQL only)
                 $queryString = str_replace('`wmp`', "`{$wmpDbName}`", $queryString);
                 $query = $connection->prepare($queryString);
                 if ($query === false) {
                     //error during statement preparation, display cause
                     error_log(json_encode($connection->errorInfo()) . ' on query: ' . $queryString);
                     $results['tables']['Tables creation'] = $connection->errorInfo()[2];
                     //return to the main thread for displaying error
                     return false;
                 }
                 if (!$query->execute()) {
                     //error during statement execution, display cause
                     error_log(json_encode($query->errorInfo()) . ' on query: ' . $queryString);
                     $results['tables']['Tables creation'] = $query->errorInfo()[2];
                     $continue = false;
                     break;
                 }
                 //table creation is ok
                 $results['tables']['Tables creation'] = $i . ' tables set';
                 $continue = true;
             }
         }
         $results['tables']['Admin user account'] = 'Admin user account password has not been changed';
         if ($continue && $adminUserPwd !== '') {
             $results['tables']['Admin user account'] = 'Admin user account password has been changed';
             //update admin user password
             $query = $connection->prepare('UPDATE `user` SET `password`=:password WHERE `id`=1;');
             $query->bindValue(':password', md5($adminUserPwd), PDO::PARAM_STR);
             if (!$query->execute()) {
                 $results['tables']['Admin user account'] = 'Admin user account password has not been changed';
                 return false;
             }
             return true;
         }
         return $continue;
     } catch (Exception $exception) {
         $results['user']['Database access'] = $exception->getMessage();
         return false;
     }
 }

 /**
  * Get album cover.
  *
  * @return bool|string Stream of the cover image or false on failure
  */
 public function getCoverImage()
 {
     //check if there is already a cover
     require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
     $connection = new DatabaseConnection();
     $query = $connection->prepare('SELECT `image` FROM `cover` WHERE `albumId` = :albumId AND `status` = 1;');
     $query->bindValue(':albumId', $this->id, PDO::PARAM_INT);
     $query->execute();
     $image = $query->fetchColumn();
     if ($image !== false) {
         //image found in database
         return $image;
     }
     //not found in database, try to get it from Cover Art Archive if there is a MBID
     if ($this->getMBID()) {
         return $this->callCoverArtArchive();
     }
     //returns there is no MBID for this album
     return false;
 }

 /**
  * Return all users.
  *
  * @return array All users
  */
 public function getAllUsers()
 {
     require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
     $connection = new DatabaseConnection();
     $query = $connection->prepare('SELECT * FROM `user`;');
     if ($query->execute()) {
         //return array of users
         return $query->fetchAll(PDO::FETCH_CLASS, 'User');
     }
     //indicate there is a problem during querying
     return false;
 }

 /**
  * Delete an artist from database.
  *
  * @return bool True on success or false on failure
  */
 public function delete()
 {
     if (is_int($this->id)) {
         require_once $_SERVER['DOCUMENT_ROOT'] . '/server/lib/DatabaseConnection.php';
         $connection = new DatabaseConnection();
         $query = $connection->prepare('DELETE FROM `artist` WHERE `id` = :id;');
         $query->bindValue(':id', $this->id, PDO::PARAM_INT);
         //returns deletion result
         return $query->execute();
     }
     //returns an error if no identifier was provided
     return false;
 }