This is a field guide to the PDO PHP interface API that allows PHP to connect to a MySQL database (through access to the PHP PDO MySQL driver extension). Each of them is a wrapper to a C client library.

The alternate APIs connect with the (deprecated, unupdated) mysql extension and the mysqli extension.

  1. $exampleQuery = "SELECT column1 FROM example_table1";
  2. $exampleValue1 = 1;
  3. $exampleValue2 = "%w%";
  4. $examplePDO = new PDO("mysql:host=localhost;dbname=pdo-test-1", "exampleUser", "examplePassword"); // PDO Database Connection Constructor

    This is the constructor for the PDO class. It creates a connection to the specified host and database.

    The first parameter specifies the database host and database name. This is the Data Source Name, or DSN. It consists of the particular database’s driver name, a colon, and the driver’s connection syntax.

    The last two parameters are the username and password. They are be optional for some PDO drivers.

    The constructor throws a PDOException if the connection is unsuccessful. Otherwise, it returns a new PDO object through which PHP can connect to the database.

  5. $examplePDO = null; // Connection Closer

    This code tries to terminate the database connection by removing the reference to the PDO object. Otherwise, the connection termiantes with the script.

    If the PDO object is referenced elsewhere, those references must be destroyed as well.

    NOTE: This connection termination may not happen immediately on the server side.

  6. try { // Recommended Error-Handling Encapsulation

    In case a successful database connection is not achieved, it is recommended to encapsulate the PDO constructor in a try-catch block.

  7. $examplePDO = new PDO("mysql:host=localhost;dbname=pdo-test-1", "exampleUser", "examplePassword");
  8. } catch( PDOException $e ) { // PDOException Object

    The PDO object throws a PDOException object when a database is not successful.

  9. echo "Error: " . $e->getMessage(); // Error Handling Code
  10. die();
  11. }
  12. $examplePDO = new PDO("mysql:host=localhost;dbname=pdo-test-1", "exampleUser", "examplePassword", array( // PDO Constructor with Attributes Array

    The PDO object accepts a variety of optional attributes to define the connection.

    These attributes can also be set with the setAttribute method, but these are only effective going forward and – in some cases – only during instantiation.

  13. PDO::ATTR_PERSISTENT => true // PDO::ATTR_PERSISTENT Persistent Connection Attribute

    This connection creates a persistent connection to the database that is not destroyed when its PDO object reference is nullified.

    The next PDO connection that is created uses that connection, thus saving time.

    It is one of many possible attributes. Some of them are specific to a database driver.

  14. PDO::ERRMODE_SILENT => true // PDO::ERRMODE_* Error Mode Connection Attribute

    This attribute sets the error mode for a database connection. There are three possible values:

    • PDO::ERRMODE_SILENT: silent except for errorCode/errorInfo function calls (defaut)
    • PDO::ERRMODE_WARNING: emits an E_WARNING message
    • PDO::ERRMODE_EXCEPTION: throws an exception

  15. ));
  16. try {
  17. $examplePDO->beginTransaction(); // PDO::beginTransaction Function

    This function begins a transaction through the database connection, disabling “auto-commit” mode. This guarantees that any work done is guaranteed to be done safely and without interference from other queries once the transaction is committed.

    If the transaction’s commit is unsuccessful, the function returns an error and rolls back the changes. If the connection/driver doesn’t support transactions, the function may or may not return an error.

    The alternative to transactions is an “auto-commit” mode that creates implicit transactions for each query. With manual transactions, batch queries become possible.

    Transactions are explained in more detail on the “Database” page.

  18. $examplePDO->exec( "CREATE TABLE example_table1 ( column1 int )" ); // PDO::exec Function

    This function executes an SQL statement and returns the number of rows affected.

    For getting values from query statements, use the query function.

    For query statements run multiple times, prepare a PDOStatement object with the prepare function, then use the PDOStatement object’s execute function.

    NOTE: Some queries, like “CREATE TABLE” or “DROP TABLE”, auto-commit without possibility of rollback.

  19. $examplePDO->query( "SELECT * FROM example_table1" ); // PDO::query Function

    This function executes an SQL statement and returns the values as a PDOStatement object.

    It can contain more than one attribute.

  20. $examplePDO->commit(); // PDO::commit Function

    This function commits the transactions stated between this statement and the object’s beginTransaction function.

    It returns true on success and false on failure.

  21. $examplePDO->errorCode(); // PDO:errorCode Function

    This function returns an SQLSTATE from the most recent transaction.

    An SQLSTATE is a five-character string that indicates errors with the most recent query.

  22. $examplePDO->errorInfo(); // PDO::errorInfo Function

    This function returns an array of error information about the last transaction:

  23. $examplePDO->getAttribute( PDO::ATTR_AUTOCOMMIT ); // PDO::getAttribute Function

    This function returns the value of a database connection’s attribute. An unsuccessfull call returns null.

    Common database connection attributes are:

    • PDO::ATTR_AUTOCOMMIT: auto-commit mode status
    • PDO::ATTR_CASE: force column names to a specific case
    • PDO::ATTR_CLIENT_VERSION: database client version
    • PDO::ATTR_CONNECTION_STATUS: database connection status
    • PDO::ATTR_DRIVER_NAME: database connection driver name
    • PDO::ATTR_ERRMODE: error/warning reporting mode
    • PDO::ATTR_ORACLE_NULLS: conversion of nulls and empty strings
    • PDO::ATTR_PERSISTENT: persistent connection status
    • PDO::ATTR_PREFETCH: prefetch size (larger size is faster with higher memory costs)
    • PDO::ATTR_SERVER_INFO: database server information
    • PDO::ATTR_SERVER_VERSION: database server version
    • PDO::ATTR_TIMEOUT: connection timeout setting in seconds

  24. $examplePDO->setAttribute( PDO::ATTR_AUTOCOMMIT, 0 ); // PDO:setAttribute Function

    This function sets the specified attribute.

    Common database connection attributes:

    • PDO::ATTR_AUTOCOMMIT: (bool) auto-commit mode status
    • PDO::ATTR_CASE: force column names to a specific case
      • PDO::CASE_LOWER: lower case
      • PDO::CASE_NATURAL: default
      • PDO::CASE_UPPER: upper case
    • PDO::ATTR_ERRMODE: error/warning reporting mode
      • PDO::ERRMODE_SILENT: only set error codes
      • PDO::ERRMODE_WARNING: raise E_WARNING
      • PDO::ERRMODE_EXCEPTION: throw exceptions
    • PDO::ATTR_ORACLE_NULLS: conversion of nulls and empty strings
      • PDO::NULL_NATURAL: no conversion
      • PDO::NULL_EMPTY_STRING: empty strings become NULLs
      • PDO::NULL_TO_STRING: NULLs become empty strings
    • PDO::ATTR_STRINGIFY_FETCHES: (bool) convert fetched numeric values to strings
    • PDO:ATTR_STATEMENT_CLASS: (array(classname),array(constructor_args)) set statement class derived from PDOStatements.
    • PDO::ATTR_TIMEOUT: (int) connection timeout setting in seconds
    • PDO::ATTR_DEFAULT_FETCH_MODE: sets default fetch mode
    • PDO::MYSQL_ATTR_USE_BUFFERED_QUERY: use buffered queries in MySQL connections

  25. $examplePDO->getAvailableDrivers(); // PDO::getAvailableDrivers Function

    This function returns an array of the available PDO drivers.

  26. $examplePDO->inTransaction(); // PDO::inTransaction Function

    This function checks if a transaction is currently active. It returns true if this is so and false if it is not.

  27. $examplePDO->lastInsertId(); // PDO::lastInsertId Function

    This function returns the ID of the last inserted row or the last value from a sequence object.

    If a string parameter is supplied, it returns the last value retrieved for the specified sequence object.

  28. $examplePDO->quote( $exampleQuery ); // PDO::quote Function

    This function places a quote around a string and escapes any special characters.

    It is recommended to use this function in conjunction with PDO::prepare() rather than as a replacement.

  29. } catch( Exception $e ) {
  30. $examplePDO->rollBack(); // PDO::rollback Function

    This function rolls back the current transaction, undoing the database effects of all queries, and throwing a PDOException if there is none.

    This function also restores auto-commit mode if beginTransaction has been called.

  31. }
  32. try {
  33. $exampleQuery1 = 'SELECT * FROM example_table1 WHERE column1 = :column1 AND column2 = :column2'; // SQL Statement with Named Parameters

    This statement creates an SQL statement with named parameters, preparing for use in a PDOStatement object.

    This is an excellent way of sanitizing queries before using them in the database.

  34. $exampleQuery2 = 'SELECT * FROM example_table1 WHERE column1 = ? AND column2 = ?'; // SQL Statement with Open Parameters

    This statement creates an SQL statement with open parameters, preparing for use in a PDOStatement object.

    This is an excellent way of sanitizing queries before using them in the database.

  35. $examplePDOStatement1 = $examplePDO->prepare( $exampleQuery1, array( 'column1' => 1, 'column2' => 'two' ) ); // PDO::prepare PDOStatement Prepared Statement Function

    This function returns a PDOStatement object created with the named parameters in the array.

    A prepared statement has two advantages: it need only be parsed/prepared once by the database, but it can be executed many times with customized parameters, and parameters need not be quoted to protect against SQL injection.

  36. $examplePDOStatement2 = $examplePDO->prepare( $exampleQuery2, array( 1, 'two' ) ); // PDO::prepare PDOStatement Alternate Prepared Statement Function

    This function returns a PDOStatement object created with the unnamed parameters in the array.

    A prepared statement has two advantages: it need only be parsed/prepared once by the database, but it can be executed many times with customized parameters, and parameters need not be quoted to protect against SQL injection.

  37. $examplePDOStatement1->bindParam( ':column1', $exampleValue1 ); // PDOStatement::bindParam Function

    This function binds a named parameter to a variable reference.

    The statement and parameter, once bound, can be executed multiple times with whatever value is attached to the variable at time of execution.

  38. $examplePDOStatement2->bindParam( 1, $exampleValue1 ); // PDOStatement::bindParam Alternate Function

    This statement is the bindParam function when used with unnamed parameters (as designed by question marks).

    he first parameter is the position of the parameter in the query, with the first being 1.

  39. $examplePDOStatement2->bindParam( 1, $exampleValue1, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000 ); // PDOStatement::bindParam Full Parameters Function

    The bindParam function accepts four parameters, two optional:

    • parameter: the name or order value of the parameter to be used
    • variable: the name of the variable to used in the parameter
    • data_type: the data type of the parameter according to the PDO::PARAM_* constants. Use the bitwise OR operator to set PDO::PARAM_INPUT_OUTPUT bits.
    • length: length of the data type
    • driver_options: optional driver options

  40. $examplePDOStatement2->bindParam( 1, $exampleValue1, PDO::PARAM_LOB ); // PDOStatement::bindParam Large Objects Parameters

    One of the parameter options allows working with large objects like pictures, allowing database connections to access stream data.

  41. $examplePDOStatement2->bindParam( 2, $exampleValue2 ); // PDOStatement::bindParam with WildCard Parameters Function

    Wildcard values should be set in the variable itself, not in the statement with the variables.

  42. $examplePDOStatement1->bindValue( 1, $exampleValue ); // PDOStatement::bindValue Function

    This function binds a parameter to a variabe value.

    Unlike bindParam, it does not change when the variable changes.

  43. $examplePDOStatement1->execute(); // PDOStatement::execute Function

    This function executes an SQL statement with the option to bind variables by value (as shown in the next two statements in this example).

  44. $examplePDOStatement1->execute( ':column1' => 1, ':column2' => '’two' ); // PDOStatement::execute with Named Parameters
  45. $examplePDOStatement2->execute( array( 1, "two" ) ); // PDOStatement::execute with Unnamed Parameters
  46. $examplePDOStatement1->bindColumn( 'column1', $newExampleVariable ); // PDOStatement::bindColumn Function

    This function binds a variable to a column value from a result set. Each call to fetch or fetchall will update all variables bound to that column.

    It accepts a number of additional parameters:

    • column: string or column number of the column in the result set
    • variable: name of the variable to which the column value is bound
    • type: data type of the variable
    • maxlen: maximum length of the data type
    • driverdata: optional driver parameters

  47. $examplePDOStatement1->columnCount(); // PDOStatement::columnCount Function

    This function returns the number of columns in a result set, or 0 if there is none yet.

  48. $examplePDOStatement1->debugDumpParams(); // PDOStatement::debugDumpParams Function

    This function dumps a prepared SQL command into the normal output, providing the query, the parameters, and their name or position.

  49. $examplePDOStatement1->errorCode(); // PDOStatement::errorCode Function

    This function returns the five digit SQLSTATE for the last operation.

    An SQLSTATE is a five-character string that indicates errors with the most recent query.

  50. $examplePDOStatement1->errorInfo(); // PDOStatement::errorInfo Function

    This statement returns extended error information for the last operation, including the SQLSTATE data.

  51. $examplePDOStatement1->fetch(); // PDOStatement::fetch Function

    This statement fetches the next row from an SQL statement and outputs according to parameters, then increments the result set cursor to be ready to fetch the next row.

    Optional parameters are:

    • fetch_style: how the next row will be returned to the caller
      • PDO::FETCH_ASSOC: associated array
      • PDO::FETCH_BOTH: associated and numeric array
      • PDO::FETCH_BOUND: binds columns to variables set by bindColumn
      • PDO::FETCH_CLASS: new object of requested class, mapping columns to properties
      • PDO::FETCH_INTO: existing object of requested class
      • PDO::FETCH_LAZY: combination of FETCH_BOTH and FETCH_OBJ, creating variable names as accessed
      • PDO::FETCH_NAMED: same as FETCH_ASSOC, but if there are multiple columns with the same name they are combined in a array associatd with that name
      • PDO::FETCH_NUM: numeric array
      • PDO::FETCH_OBJ: anonymous object with property names associated with columns
    • cursor_orientation: behavior of the cursor incrementation
    • offset: behavior of the cursor row pointing

  52. $examplePDOStatement1->fetchObject(); // PDOStatement::fetchObject Function

    This function fetches the next row and returns it as an object.

    Its optional parameters are the name of the class and an array consisting of the parameters for a custom constructor for that class.

    If no parameter is supplied, it returns an object of the stdClass.

  53. $examplePDOStatement1->fetchColumn(); // PDOStatement::fetchColumn Function

    This function returns a single column from the next row of a result set.

    Its optional parameter is the column position or name.

    It should not be used to fetch boolean columns, as it returns false if there are no more values to return.

  54. $examplePDOStatement1->fetchAll(); // PDOStatement::fetchAll Function

    This function returns an array containing all the result set rows.

    Optional parameters are:

    • fetch_style: same as for fetch with these additions:
      • PDO::FETCH_COLUMN: fetches a single column, as indicated by the fetch_argument parameter
      • PDO::FETCH_CLASS: fetches instanes of the specified class, as indicated by the fetch_argument parameter
      • PDO::FETCH_FUNC: returns the results of calling the specified function, using each row’s parameters in the call, as indicated by the fetch_argument parameter
    • fetch_argument
    • ctor_args: arguments for the custom class constructor when using PDO::FETCH_CLASS.

  55. $examplePDOStatement1->closeCursor(); // PDOStatement::closeCursor Function

    This function closes the cursor, freeing the database connection and allowing a statement to be executed again even if not all values have been fetched.

  56. $examplePDOStatement1->getAttribute( PDO::ATTR_AUTOCOMMIT ); // PDOStatement::getAttribute Function

    This function returns a specified statement attribute.

    Common database connection attributes are:

    • PDO::ATTR_AUTOCOMMIT: auto-commit mode status
    • PDO::ATTR_CASE: force column names to a specific case
    • PDO::ATTR_CLIENT_VERSION: database client version
    • PDO::ATTR_CONNECTION_STATUS: database connection status
    • PDO::ATTR_DRIVER_NAME: database connection driver name
    • PDO::ATTR_ERRMODE: error/warning reporting mode
    • PDO::ATTR_ORACLE_NULLS: conversion of nulls and empty strings
    • PDO::ATTR_PERSISTENT: persistent connection status
    • PDO::ATTR_PREFETCH: prefetch size (larger size is faster with higher memory costs)
    • PDO::ATTR_SERVER_INFO: database server information
    • PDO::ATTR_SERVER_VERSION: database server version
    • PDO::ATTR_TIMEOUT: connection timeout setting in seconds

  57. $examplePDOStatement1->setAttribute( PDO::ATTR_AUTOCOMMIT, 1 ); // PDOStatement::setAttribute Function

    This function sets a specified statement attribute and returns true on success and false on failure.

  58. $examplePDOStatement1->setFetchMode( PDO::FETCH_NUM ); // PDOStatement::setFetchMode Function

    This function sets the fetch mode for a statement:

    It has a number of possible parameters depending on the type of fetch mode:

    • PDO::FETCH_COLUMN: column number
    • PDO::FETCH_CLASS: classname, array of constructor arguments
    • PDO::FETCH_INTO: object name

  59. $examplePDOStatement1->rowCount(); // PDOStatement::rowCount Function

    This function returns the number of rows affected by the last statement.

    It works for DELETE, INSERT, and UPDATE statements. It does not work reliably for SELECT statements.

  60. $examplePDOStatement1->nextRowset(); // PDOStatement::newRowset Function

    This function advances to the next rowset in a multi-rowset statement, such as for a stored procedure.

  61. $examplePDOStatement1->getColumnMeta( 1 ); // PDOStatement::getColumnMeta Function

    This function returns metadata for a column in a result set. It is experimental.

  62. } catch( Exception $e ) {
  63. }
  64. //