PHP PDO
The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for PHP to access databases.
PDO provides a data access abstraction layer, which means that regardless of which database you use, you can use the same functions (methods) to query and fetch data.
PDO was released with PHP 5.1 and is also available in the PECL extension for PHP 5.0; it cannot run on earlier PHP versions.
PDO Installation
You can use PHP's phpinfo() function to check whether the PDO extension is installed.
Installing PDO on Unix Systems
On Unix or Linux, you need to add the following extensions:
extension=pdo.so
Windows Users
PDO and all the major drivers are distributed with PHP as shared extensions. To activate them, simply edit the php.ini file and add the following extensions:
extension=php_pdo.dllIn addition, there are the following corresponding database extensions:
;extension=php_pdo_firebird.dll ;extension=php_pdo_informix.dll ;extension=php_pdo_mssql.dll ;extension=php_pdo_mysql.dll ;extension=php_pdo_oci.dll ;extension=php_pdo_oci8.dll ;extension=php_pdo_odbc.dll ;extension=php_pdo_pgsql.dll ;extension=php_pdo_sqlite.dll
After setting these configurations, we need to restart PHP or the web server.
Next, let's look at a specific example. The following is an example of using PDO to connect to a MySQL database:
Example
<?php
$dbms='mysql'; //database type
$host='localhost'; //database host name
$dbName='test'; //database used
$user='root'; //database connection username
$pass=''; //corresponding password
$dsn="$dbms:host=$host;dbname=$dbName";
try {
$dbh = new PDO($dsn, $user, $pass); //initialize a PDO object
echo "Connection successful<br/>";
/*You can also perform a search operation
foreach ($dbh->query('SELECT * from FOO') as $row) {
print_r($row); //you can use echo($GLOBAL); to see these values
}
*/
$dbh = null;
} catch (PDOException $e) {
die ("Error!: " . $e->getMessage() . "<br/>");
}
//By default, this is not a persistent connection. If you need a persistent database connection, you need to add a parameter at the end: array(PDO::ATTR_PERSISTENT => true) like this:
$db = new PDO($dsn, $user, $pass, array(PDO::ATTR_PERSISTENT => true));
?>
$dbms='mysql'; //database type
$host='localhost'; //database host name
$dbName='test'; //database used
$user='root'; //database connection username
$pass=''; //corresponding password
$dsn="$dbms:host=$host;dbname=$dbName";
try {
$dbh = new PDO($dsn, $user, $pass); //initialize a PDO object
echo "Connection successful<br/>";
/*You can also perform a search operation
foreach ($dbh->query('SELECT * from FOO') as $row) {
print_r($row); //you can use echo($GLOBAL); to see these values
}
*/
$dbh = null;
} catch (PDOException $e) {
die ("Error!: " . $e->getMessage() . "<br/>");
}
//By default, this is not a persistent connection. If you need a persistent database connection, you need to add a parameter at the end: array(PDO::ATTR_PERSISTENT => true) like this:
$db = new PDO($dsn, $user, $pass, array(PDO::ATTR_PERSISTENT => true));
?>
Very simple. Next, let's take a closer look at the specific documentation for PHP PDO:
- Predefined Constants
- PHP PDO Connection Management
- PHP PDO Transactions and Auto-commit
- PHP PDO Prepared Statements and Stored Procedures
- PHP PDO Errors and Error Handling
- PHP PDO Large Objects (LOBs)
- PDO Class:
- PDO::beginTransaction— Starts a transaction
- PDO::commit— Commits a transaction
- PDO::__construct— Creates a PDO instance representing a database connection
- PDO::errorCode— Gets the SQLSTATE associated with the last operation on the database handle
- PDO::errorInfo— Returns the error message for the last database operation
- PDO::exec— Executes an SQL statement and returns the number of affected rows
- PDO::getAttribute— Retrieves a property of a database connection
- PDO::getAvailableDrivers— Returns an array of available drivers
- PDO::inTransaction— Checks if within a transaction
- PDO::lastInsertId— Returns the ID or sequence value of the last inserted row
- PDO::prepare— Prepares an SQL statement to execute and returns a PDOStatement object
- PDO::query— Executes an SQL statement, returns a PDOStatement object, which can be understood as a result set
- PDO::quote— Adds quotes to a string in an SQL statement.
- PDO::rollBack— Rolls back a transaction
- PDO::setAttribute— Sets an attribute
- PDOStatement Class:
- PDOStatement::bindColumn— Binds a column to a PHP variable
- PDOStatement::bindParam— Binds a parameter to a specified variable name
- PDOStatement::bindValue— Binds a value to a parameter
- PDOStatement::closeCursor— Closes the cursor, enabling the statement to be executed again.
- PDOStatement::columnCount— Returns the number of columns in the result set
- PDOStatement::debugDumpParams— Prints an SQL prepared command
- PDOStatement::errorCode— Gets the SQLSTATE associated with the last statement handle operation
- PDOStatement::errorInfo— Gets extended error information associated with the last statement handle operation
- PDOStatement::execute— Executes a prepared statement
- PDOStatement::fetch— Fetches the next row from the result set
- PDOStatement::fetchAll— Returns an array containing all rows in the result set
- PDOStatement::fetchColumn— Returns a single column from the next row in the result set.
- PDOStatement::fetchObject— Fetches the next row and returns it as an object.
- PDOStatement::getAttribute— Retrieves a statement attribute
- PDOStatement::getColumnMeta— Returns metadata for a column in the result set
- PDOStatement::nextRowset— Advances to the next rowset in a multi-rowset statement handle
- PDOStatement::rowCount— Returns the number of rows affected by the last SQL statement
- PDOStatement::setAttribute— Sets a statement attribute
- PDOStatement::setFetchMode— Sets the default fetch mode for the statement.