README

一个 PDO 封装类。

安装

使用 Composer（推荐）

$ composer require saturn/database

设置

定义并设置以下常量

define("kHostname", "hostname"); // The hostname on which the database server resides.
define("kDatabase", "database"); // The name of the database.
define("kUsername", "username"); // The username.
define("kPassword", "password"); // The password.

使用方法

使用 Database 非常简单。只需包含它（手动或使用 Composer 的加载器），然后通过提供上述常量创建一个实例。更多信息请参见 下面。

use saturn\database\Database;

$database = new Database($hostname, $database, $username, $password);

// Retrieve from the database
$SQLQuery = "SELECT SomeColumn FROM SomeTable";
$response = $database->read($SQLQuery);

// Insert into the database
$SQLQuery = "INSERT INTO SomeTable (SomeColumn) VALUES (:someValue)";
$SQLParam = array("someValue" => "Foobar");
$response = $database->write($SQLQuery, $SQLParam);

API

下面概述了 Database 类的公共方法。

/*
 *  Create the PDO instance and set the defaults attributes on the database handle.
 *
 *  @param      string  Database hostname.
 *  @param      string  Database name.
 *  @param      string  Username.
 *  @param      string  Password.
 */
public function __construct($hostname, $database, $username, $password) {}

/**
 *  Run the supplied query. Only for fetching rows from
 *  the database.
 *
 *  @param      string  Optional. The SQL query to execute.
 *  @param      array   Optional. Additional parameters to supply to the query.
 *  @param      bool    If true, fetches all matching rows. Defaults to TRUE.
 *  @return     array
 **/
public function read($query, $params = NULL, $shouldFetchAll = true) {}

/**
 *  Run the supplied query. Only for adding rows to the the database.
 *
 *  @param      string  Optional. The SQL query to execute.
 *  @param      array   Optional. Additional parameters to supply to the query.
 *  @return     array
 **/
public function write($query = NULL, $params = NULL) {}

/**
 *  Return an array of error information about the last performed operation.
 *
 *  @param      bool    Value determines if the errorInfo should be performed on the 
 *                      database handle or the statement handle.
 *  @return     array
 */
public function error($connection = true) {}

/**
 *  Execute the prepared SQL statement.
 *
 *  @param      array   Optional. The input parameters.
 *  @return     mixed
 */
public function execute($params = NULL) {}

/**
 *  Fetch all the rows in the result set.
 *
 *  @param      int     Optional. Value controls how the row should be returned. The value 
 *                      must be one of the FETCH_* constants. Defaults to: FETCH_ASSOC.
 *  @return     mixed
 */
public function fetchAll($flags = PDO::FETCH_ASSOC) {}

/**
 *  Fetch the next row in the result set.
 *
 *  @param      int     Optional. Value controls how the row should be returned. The value 
 *                      must be one of the FETCH_* constants. Defaults to: FETCH_ASSOC.
 *  @return     mixed
 */
public function fetch($flags = PDO::FETCH_ASSOC) {}

/**
 * Prepares a statement for execution.
 *
 *  @param      string  The SQL string.
 *  @return     bool
 */
public function prepare($query) {}

/**
 * Bind a value to a named or question mark placeholder
 * in the prepared SQL statement.
 *
 *  @param      mixed   The parameter identifier. For named placeholder, this value must be a
 *                      string (:name). For a question mark placeholder, the value must be the
 *                      1-indexed position of the parameter.
 *  @param      mixed   The value to bind to the parameter.
 *  @param      int     Data type for the parameter, using the predefined PDO constants:
 *                      https://php.ac.cn/manual/en/pdo.constants.php
 *  @return     bool
 */
public function bindValue($param, $value, $dataType) {}

/**
 *  Bind a referenced variable to a named or question mark
 *  placeholder in the prepared SQL statement.
 *
 *  @param      mixed   The parameter identifier. For named placeholder, this value must be a
 *                      string (:name). For a question mark placeholder, the value must be the
 *                      1-indexed position of the parameter.
 *  @param      mixed   Variable to bind to the parameter.
 *  @param      int     Data type for the parameter, using the predefined PDO constants:
 *                      https://php.ac.cn/manual/en/pdo.constants.php
 *  @return     bool
 */
public function bindParam($param, &$variable, $dataType) {}

/**
 *  Number of rows affected by last operation.
 *
 *  @return     int
 */
public function rowCount() {}

/**
 *  Return the id of last inserted row.
 *
 *  @return     int
 */
public function lastInsertId() {}

示例

从数据库中检索

// Prepare the query
$database->prepare("SELECT SomeColumn FROM SomeTable");

// Call the shorthand method read() to fetch the results
$response = $database->read();

// Because the read/write methods prepares the statement, the above code can be shortened:
$response = $database->read("SELECT SomeColumn FROM SomeTable");

// Retrieve a specific row like this:
$response = $database->read("SELECT SomeColumn FROM SomeTable WHERE id = :id", array("id" => $id));

添加到数据库

$values = array(
  "ThisValue"  => "Foo!",
  "ThatValue"  => "Bar!"
);

$SQLQuery = "INSERT INTO SomeTable (someColumn, anotherOne) VALUES (:ThisValue, :ThatValue)";
$database->prepare($SQLQuery);

// Bind the values that are to be inserted
$database->bindValue(":ThisValue", $values["ThisValue"]);
$database->bindValue(":ThatValue", $values["ThatValue"]);

$response = $this->write();

从数据库中删除

$SQLQuery = "DELETE FROM SomeTable WHERE id = :id";
$SQLParam = array("id" => $id);
$response = $database->write($SQLQuery, $SQLParam);

一个更复杂的示例

$values = array(
  array(
    "ThisValue"  => "Foo 123",
    "ThatValue"  => "Bar 456"
  ),
  array(
    "ThisValue"  => "Foo 789",
    "ThatValue"  => "Bar 012"
  ),
  array(
    "ThisValue"  => "Foo 345",
    "ThatValue"  => "Bar 678"
  )

// Create the unnamed placeholders, based on the number of values the row will take:
$markers = array_fill(0, count($values[0]), '?');
$markers = '(' . implode(", ", $markers) . ')';

// The number of placeholders must match the number of values that are to be inserted 
// in the VALUES-clause. Create the array with array_fill() and join the array with 
// the query-string.
$clause = array_fill(0, count($values), $markers);
$query  = "INSERT INTO SomeTable (someColumn, anotherOne) VALUES " . implode(", ", $clause);
$database->prepare($query);

// Bind the values using bindValue(). Using question marked placeholders, the value 
// must be 1-indexed, that is starting at position 1.
$index = 1;
foreach ($values AS $key => $value) {
  $this->bindValue($index++, $value['ThisValue']);
  $this->bindValue($index++, $value['ThatValue']);
}

// A more pretty and dynamic way to write the above statement could be by going
// by the columns of the array, like so:
$columns = array_keys($values[0]);
foreach ($values AS $key => $value) {
  foreach ($columns AS $column)
    $this->bindValue($position++, $value[$column]);
  }
}

// And don't forget to write to database
$response = $database->write();

作者

Database 由 Ardalan Samimi 编写。