FastSitePHP\Data\Database

The Database class provides a thin wrapper for PHP Data Objects to reduce the amount of code needed when querying a database.

Source Code

Related Links

• http://php.net/manual/en/book.pdo.php
• http://php.net/manual/en/pdo.drivers.php

رمز المثال

// FastSitePHP provides a Database class which is a thin wrapper for PDO to
// reduce the amount of code needed when querying a database. An additional
// example on this page shows how to use PDO.

// Connect to a Database, this example uses SQLite with a temp in-memory db.
$dsn = 'sqlite::memory:';
$db = new \FastSitePHP\Data\Database($dsn);

// Depending on the connection 4 additional parameters can also be used:
/*
$user = null;
$password = null;
$persistent = false;
$options = [];
$db = new Database($dsn, $user, $password, $persistent, $options);
*/

// Create tables and test records. The function [execute()] is used for
// action queries (INSERT, UPDATE, DELETE, CREATE, etc) and returns the
// number of affected rows.

$db->execute('CREATE TABLE page_types (id INTEGER PRIMARY KEY, page_type)');

$sql = 'CREATE TABLE pages (id INTEGER PRIMARY KEY AUTOINCREMENT,';
$sql .= ' type_id, title, content)';
$db->execute($sql);

// This example uses a double-quotes for the string ["] because SQL strings
// include the single-quote character ['] for text.
$sql = "INSERT INTO page_types (id, page_type) VALUES (1, 'text/plain')";
$rows_added = $db->execute($sql);

// An optional 2nd parameter for parameters can be used. This is recommended
// when for user input to prevent SQL Injection Attacks. The Question Mark [?]
// is the placeholder character to use in the SQL statement.
$sql = 'INSERT INTO page_types (id, page_type) VALUES (?, ?)';
$params = [2, 'text/html'];
$rows_added += $db->execute($sql, $params);

// Multiple records can be added (or updated, etc) when using [executeMany()]
$sql = 'INSERT INTO pages (type_id, title, content) VALUES (?, ?, ?)';
$records = [
    [1, 'Text Test Page', 'This is a test.'],
    [2, 'HTML Test Page', '<h1>Test<h1><p>This is a test.</p>'],
];
$rows_added += $db->executeMany($sql, $records);

// In addition to using [?] you can also used named parameters in the
// format of ":name". Named parameters can make the code easier to read.
$sql = 'INSERT INTO pages (type_id, title, content)';
$sql .= ' VALUES (:type_id, :title, :content)';
$params = [
    'type_id' => 1,
    'title'   => 'Named Parameters',
    'content' => 'Test with Named Parameters.',
];
$rows_added += $db->execute($sql, $params);

// Get the id of the last inserted row or sequence value
$last_id = $db->lastInsertId();

// Query for Multiple Records
// Returns an Array of Records (Associative Array for each Record).
$sql = 'SELECT * FROM pages';
$records = $db->query($sql);

// Query for one record. Returns an Associative Array or [null] if not found.
// Both [query()] and [queryOne()] support optional parameters when querying.
$sql = 'SELECT * FROM pages WHERE id = ?';
$params = [1];
$record = $db->queryOne($sql, $params);

// The [Database] class also contains additional functions such as
// [queryValue(), queryList() and querySets()] to simplify and reduce
// the amount code needed when working with databases.

// FastSitePHP’s Database class or PHP's built-in PDO class can connect to
// different databases. FastSitePHP’s Database class provides a thin wrapper
// over PDO to reduce the amount of code needed when querying a database.

// Examples below show how to build connection strings and run a query for
// a number of different databases. If you download this site, the code below
// can be modified and tested for your environment; or simply copy what you
// need to your site or app.

// When specifying the hostname (Server Name), you can often specify just the
// server name (example: 'db-server') or the fully-qualified domain name (FQDN)
// (example 'db-server.example.com') based on how your network is setup.
// For example on an internal network simply using the server name will work
// but through VPN using the FQDN is often required.

// ----------------------------------------------------------------------------
// MySQL
//   Basic Format:
//     "mysql:host={hostname};dbname={database}";
//
// This example also shows using the [MYSQL_ATTR_INIT_COMMAND]
// option to set the timezone to UTC when the connection is created.
//
// If you have a site or application that has users in multiple timezones or
// countries an application design that works well is to save all dates and
// times in UTC and then format based on the users selected timezone.
//
$dsn = 'mysql:host=localhost;dbname=wordpress;charset=utf8';
$user = 'root';
$password = 'wordpress';
$options = [
    \PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+00:00'",
];
$sql = 'SELECT table_schema, table_name';
$sql .= ' FROM information_schema.tables';
$sql .= " WHERE table_type = 'BASE TABLE'";

// ----------------------------------------------------------------------------
// Oracle
//   Format:
//      "oci:dbname=//{hostname}:{port-number}/{database}"
$dsn = 'oci:dbname=//server:1521/hr';
$user = 'sys';
$password = 'password';
$options = [];
$sql = 'SELECT OWNER, TABLE_NAME FROM ALL_TABLES ORDER BY OWNER, TABLE_NAME';

// In addition to the standard format you can also specify a full TNS string
$tns = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)';
$tns .= '(HOST=server.example.com)(PORT=1521)))';
$tns .= '(CONNECT_DATA=(SERVICE_NAME=dbname)))';
$dsn = 'oci:dbname=' . $tns;

// ----------------------------------------------------------------------------
// SQL Server
$dsn = 'sqlsrv:Server=db-server;Database=DbName';
$user = 'sa';
$password = 'password';
$options = [];
$sql = 'SELECT SCHEMA_NAME(schema_id) AS schema_name, name FROM sys.tables';

// SQL Server (using ODBC)
// If the native SQL Server PDO driver is not installed and the
// PDO ODBC Driver is installed and a ODBC Connection is setup
// you could use this:
$dsn = 'odbc:DRIVER={SQL Server};SERVER=db-server;DATABASE=DbName;';

// ----------------------------------------------------------------------------
// IBM (using ODBC)
// This example show a connection to an IBM DB2 or AS/400 through iSeries.
// ODBC Options will vary based on the driver installed and used.
$dsn = 'odbc:DRIVER={iSeries Access ODBC Driver};';
$dsn .= 'HOSTNAME=AS400.EXAMPLE.COM;';
$dsn .= 'PORT=56789;';
$dsn .= 'SYSTEM=SYSTEM;';
$dsn .= 'PROTOCOL=TCPIP;';
$dsn .= 'UID=USER;';
$dsn .= 'PWD=PASSWORD;';
$user = null;
$password = null;
$options = [];
$sql = 'SELECT SYSTEM_TABLE_SCHEMA, TABLE_NAME, TABLE_TEXT';
$sql .= ' FROM QSYS2.SYSTABLES';
$sql .= " WHERE SYSTEM_TABLE_SCHEMA IN 'QSYS'";
$sql .= ' ORDER BY SYSTEM_TABLE_SCHEMA, TABLE_NAME';
$sql .= ' FETCH FIRST 100 ROWS ONLY';

// ----------------------------------------------------------------------------
// PostgreSQL
$dsn = 'pgsql:host=localhost;port=5432;dbname=dbname;';
$user = 'postgres';
$password = 'password';
$options = [];
$sql = 'SELECT table_schema, table_name';
$sql .= ' FROM information_schema.tables';
$sql .= " WHERE table_type = 'BASE TABLE'";

// ----------------------------------------------------------------------------
// SQLite
//   Example using a file path:
//     'sqlite:/var/www/app_data/db.sqlite'
//     'sqlite:C:\inetpub\wwwroot\db.sqlite'
//   In-Memory Database:
//     'sqlite::memory:'
$dsn = 'sqlite:' . $file_path;
$user = null;
$password = null;
$options = [];
$sql = 'SELECT * FROM sqlite_master';

// ----------------------------------------------------------------------------
// Persistent Connection Option
//
// Many PHP Database drivers support persistent connections which can allow
// for better performance.
$persistent = false;

// ============================================================================
// Connect using PHP Data Objects (PDO)
$options[\PDO::ATTR_ERRMODE] = \PDO::ERRMODE_EXCEPTION;
if ($persistent) {
    $options[\PDO::ATTR_PERSISTENT] = true;
}
$pdo = new \PDO($dsn, $user, $password, $options);

// Query using PDO
$stmt = $pdo->query($sql);
$records = $stmt->fetchAll(\PDO::FETCH_ASSOC);

// =================================================================================
// Connect and Query using FastSitePHP's Database class.
// Only the DSN (Data Source Name) is a required parameter.
$db = new \FastSitePHP\Data\Database($dsn, $user, $password, $persistent, $options);
$records = $db->query($sql);

// =================================================================================
// In addition to FastSitePHP's Database class [OdbcDatabase] and [Db2Database]
// can also be used for supported enviroments, and especially for IBM Databases.
//
// When using the class [OdbcDatabase] the DSN will be the same as the PDO DSN
// excluding the 'odbc:' prefix.
/*
$odbc = new OdbcDatabase($dsn, $user, $password, $persistent, $options);
$db2  = new Db2Database($dsn, $user, $password, $persistent, $options);
*/

// ============================================================================
// Lazy Loading with FastSitePHP
//
// FastSitePHP’s Application object has a function [lazyLoad()] which accepts
// a property name and callback function. It creates the object as a property
// of the app only if used. This is ideal for working with sites where some
// pages connect to a database and some pages do not, or if you have a site
// that connects to multiple databases but not all pages use each database.
$app->lazyLoad('db', function() use ($dsn, $user, $password) {
    return new \FastSitePHP\Data\Database($dsn, $user, $password);
});

// Query for records. The database gets connected to here only when first used.
$records = $app->db->query($sql);

// ============================================================================
// To obtain a list of available drivers on the computer call [phpinfo()]
// and view the result or call the following function to get an array of
// driver names. A full list of PDO Drivers can be found at:
//   http://php.net/manual/en/pdo.drivers.php
// If you need a driver and it is not available or enabled on your server
// they are generally easy to install and enable.
$drivers = \PDO::getAvailableDrivers();

Properties

Methods

__construct($dsn, $user = null, $password = null, $persistent = false, array $options = array())

Class constructor. Creates Db Connection using PDO.

• http://php.net/manual/en/pdo.construct.php
• http://php.net/manual/en/features.persistent-connections.php

getBindType($value) {

Return a PDO Constant for use with `PDOStatement->bindValue()` based on the value type. This is used internally for parametrized queries by default unless `$this->use_bind_value = false`.

Returns: int

query($sql, array $params = null)

Run a Query and return results as any array of records. Records are each associative arrays. If no records are found an empty array is returned.

Returns: array

queryOne($sql, array $params = null)

Query for a single record and return it as a associative array or return null if the record does not exist.

Returns: array | null

queryValue($sql, array $params = null)

Query for a single value from the first column of the first record found. If no records were found null is returned.

Returns: mixed

queryList($sql, array $params = null)

Query for an array of values from the first column of all records found.

Returns: array

querySets($sql, array $params = null)

Query for and return multiple Row Sets from a single query. This feature works in most databases but is not available for SQLite.

Returns: array

execute($sql, array $params = null)

Run a SQL Action Statement (INSERT, UPDATE, DELETE, etc) and return the number or rows affected. If multiple statements are passed then the returned row count will likely be for only the last query.

Returns: int - Row count of the last query

lastInsertId($name = null)

Returns the ID of the last inserted row or sequence value. This calls the PDO function [lastInsertId()]. Additionally if using SQL the last ID can be obtained from the following queries:

    MySQL:      SELECT LAST_INSERT_ID()
    SQLite:     SELECT last_insert_rowid()
    SQL Server: SELECT SCOPE_IDENTITY()
                SELECT @@IDENTITY
    IBM:        SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

Oracle and PostgreSQL uses Sequence Objects of Auto-Numbers.

Example if using SQL with [queryValue()]:
    $id = $db->queryValue('SELECT SCOPE_IDENTITY()');

Returns: string

• http://php.net/manual/en/pdo.lastinsertid.php

executeMany($sql, array $records)

Prepare a SQL Statement and run many record parameters against it. This can be used for transactions such as bulk record inserts. Returns the total number of rows affected for all queries.

Returns: int

trimStrings($new_value = null)

Get or set whether spaces on strings should be trimmed when calling [query(), queryOne(), queryValue(), queryList(), querySets()].

When called strings are trimmed after the records are queried and before the function returns the result.

Often legacy databases will use [CHAR] text fields over [VARCHAR] or similar types. For example when using a [CHAR] field:
    Field: [name] CHAR(20)
    Data saved as "John                "

When querying by default the spaces will be returned however if this function is set to [true] then "John" would be returned.

Defaults to [false]. Calling this function takes extra memory vs not using it so if you have a high traffic site and want to trim strings you may want to do so in the SQL Statement and keep this [false].

For a small amount of records (several hundred or less) this has little or not noticeable impact however if using a large set of records (1,000+) this setting may cause a about a 10% increase in memory or more.

Returns: bool | $this