TABLE OF CONTENTS (HIDE)

Developing PHP/MySQL Webapps

PHP with MySQL Database

PHP provides extensions for interfacing with MySQL database. The PHP's MySQL-related extensions include:

the older mysql extension (not recommended),
the newer mysqli (mysql improved) extension, and
the object-oriented PHP Data Object (PDO).

The older mysql extension provides a procedural interface and is intended for use with MySQL versions earlier than 4.1.3. It does not support all the latest MySQL features. The newer mysqli extension (or mysql improved extension) supports features like prepared statement and transaction. It provides an object-oriented interface as well as a procedural interface. However, I recommend PHP Data Object (PDO) for new development, which is fully object-oriented with proper exception handling, having neater and simpler interface.

PHP Data Object (PDO) By Examples

Reference:

PHP manual "PHP Data Objects (PDO)" @ http://www.php.net/manual/en/book.pdo.php.

Example 1: Create Database Connection, query() and exec()

This example is meant for toy programs. For production, use PDO prepared statement as illustrated in Example 2 to prevent SQL injection.

<?php
/**
 * Testing PDO MySQL Database Connection, query() and exec().
 * For CREATE TABLE, INSERT, DELETE, UPDATE:
 *   exec(): returns the affected rows.
 * For SELECT:
 *   query(): returns a result set.
 */
// Define the MySQL database parameters.
// Avoid global variables (which live longer than necessary) for sensitive data.
$DB_HOST = 'localhost'; // MySQL server hostname
$DB_PORT = '3306';      // MySQL server port number (default 3306)
$DB_NAME = 'test';      // MySQL database name
$DB_USER = 'testuser';  // MySQL username
$DB_PASS = 'xxxx';      // password
 
try {
   // Create a PDO database connection to MySQL server, in the following syntax:
   //  new PDO('mysql:host=hostname;port=number;dbname=database', username, password)
   $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS);
   $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception
   echo 'Connected', '<br />';
 
   // Run SQL statements
   // Use exec() to run a CREATE TABLE, DROP TABLE, INSERT, DELETE and UPDATE,
   //  which returns the affected row count.
   $rowCount = $dbConn->exec('DROP TABLE IF EXISTS `test`');
   echo 'DROP TABLE: ', $rowCount, ' rows', '<br />';
 
   $rowCount = $dbConn->exec(
         'CREATE TABLE IF NOT EXISTS `test` (
           `id` INT AUTO_INCREMENT,
           `name` VARCHAR(20),
           PRIMARY KEY (`id`))');
   echo 'CREATE TABLE: ', $rowCount, ' rows', '<br />';
 
   $rowCount = $dbConn->exec("INSERT INTO `test` (`id`, `name`) VALUES (1001, 'peter')");
   echo 'INSERT INTO: ', $rowCount, ' rows', '<br />';
   // Use lastInsertId() to get the LAST_INSERT_ID of the AUTO_INCREMENT column.
   echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br />';
 
   $rowCount = $dbConn->exec("INSERT INTO `test` (`name`) VALUES ('paul'),('patrick')");
   echo 'INSERT INTO: ', $rowCount, ' rows', '<br />';
   echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br /><br />';
 
   // Use query() to run a SELECT, which returns a resultset.
   $sql = 'SELECT * FROM `test`';
   $resultset = $dbConn->query($sql);
   // By default, resultset's row is an associative array
   //  indexed by BOTH column-name AND column-number (starting at 0).
   foreach ($resultset as $row) {  // Loop thru all rows in resultset
      echo 'Retrieve via column name: id=', $row['id'], ', name=', $row['name'], '<br />';
      echo 'Retrieve via column number: id=', $row[0], ', name=', $row[1], '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
   echo '<br />';
 
   // Run again with "FETCH_ASSOC" option.
   // Resultset's row is an associative array indexed by column-name only.
   $resultset = $dbConn->query($sql, PDO::FETCH_ASSOC);
   // print_r($resultset);   // A PDOStatement Object
   foreach ($resultset as $row) {
      echo 'Retrieve via column name: id=', $row['id'], ', name=', $row['name'], '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
   echo '<br />';
 
   // Run again with "FETCH_OBJ" option.
   // Resultset's row is an object with column-names as properties.
   $resultset = $dbConn->query($sql, PDO::FETCH_OBJ);
   foreach ($resultset as $row) {
      echo 'Retrieve via column name: id=', $row->id, ', name=', $row->name, '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
 
   // Close the database connection (optional).
   $dbConn = NULL;
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // Filename that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />');
}
?>

Connected
DROP TABLE: 0 rows
CREATE TABLE: 0 rows
INSERT INTO: 1 rows
LAST_INSERT_ID (of the AUTO_INCREMENT column) is 1001
INSERT INTO: 2 rows
LAST_INSERT_ID (of the AUTO_INCREMENT column) is 1002

Retrieve via column name: id=1001, name=peter
Retrieve via column number: id=1001, name=peter
Array ( [id] => 1001 [0] => 1001 [name] => peter [1] => peter )
Retrieve via column name: id=1002, name=paul
Retrieve via column number: id=1002, name=paul
Array ( [id] => 1002 [0] => 1002 [name] => paul [1] => paul )
Retrieve via column name: id=1003, name=patrick
Retrieve via column number: id=1003, name=patrick
Array ( [id] => 1003 [0] => 1003 [name] => patrick [1] => patrick )

Retrieve via column name: id=1001, name=peter
Array ( [id] => 1001 [name] => peter )
Retrieve via column name: id=1002, name=paul
Array ( [id] => 1002 [name] => paul )
Retrieve via column name: id=1003, name=patrick
Array ( [id] => 1003 [name] => patrick )

Retrieve via column name: id=1001, name=peter
stdClass Object ( [id] => 1001 [name] => peter )
Retrieve via column name: id=1002, name=paul
stdClass Object ( [id] => 1002 [name] => paul )
Retrieve via column name: id=1003, name=patrick
stdClass Object ( [id] => 1003 [name] => patrick )

Dissecting the Program

To create a Database connection to MySQL Server:
```
$dbConn = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $pass);
```
where $host is the MySQL server's hostname, $port is the port number, $dbName is the default database name, $user and $pass are the username/password for an authorized MySQL user.
The connection will be closed automatically at the end of the script. However, you could explicitly close the connection by setting it to NULL (which is often not necessary).
```
$dbConn = NULL;
```
Exception Handling: PDO support 3 error modes, which could be set via the setAttribute() function:
1. PDO::ERRMODE_SILENT (default): you need to check for errors yourself.
2. PDO::ERRMODE_WARNING: issue PHP warning and continue execution.
3. PDO::ERRMODE_EXCEPTION (recommended): throw PDOException, to be handled via try-catch as shown in the above example. From the PDOException object, you can invoke the getFile(), getLine() and getMessage() to retrieve the full-path filename, line number, and error message where the exception is thrown.
For CREATE/DROP TABLE, INSERT, DELETE, UPDATE, invoke exec() function, which returns the number of affected rows.
For SELECT, invoke query() function, which returns a resultset.
- query($sql) returns a row-array resultset indexed by both column-name and column-number (starting from 0).
- query($sql, PDO::FETCH_ASSOC) returns a row-array resultset indexed by column-name only (in an associative array).
- query($sql, PDO::FETCH_OBJ) returns a row-object resultset with column-names as properties.
You can use connection's lastInsertId() to retrieve the last insert id for the AUTO_INCREMENT column.

Example 2: Prepared Statements

Prepared statements can reasonably guard against SQL injection attack (Wiki), and are recommended in production.

<?php
/**
 * PdoPreparedTest.php: Using PDO Prepared Statement.
 * For CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE:
 *   prepare() -> bindParam() -> execute() -> check rowCount().
 * For SELECT:
 *   prepare() -> bindParam() -> execute() -> check rowCount() -> fetch() a row or fetchAll() rows.
 */
// Define the MySQL database parameters.
// Avoid global variables (which live longer than necessary) for sensitive data.
$DB_HOST = 'localhost'; // MySQL server hostname
$DB_PORT = '3306';      // MySQL server port number (default 3306)
$DB_NAME = 'test';      // MySQL database name
$DB_USER = 'testuser';  // MySQL username
$DB_PASS = 'xxxx';      // password
 
try {
   // Create a database connection to MySQL server.
   $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS);
   $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception
   echo 'Connected', '<br />';
 
   $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `test`');  // Create prepared statement
   $pstmt->execute();  // Run prepared statement
   echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   $pstmt = $dbConn->prepare(
         'CREATE TABLE IF NOT EXISTS `test` (
            `id` INT AUTO_INCREMENT,
            `name` VARCHAR(20),
             PRIMARY KEY (`id`))');  // Create prepared statement
 
   $pstmt->execute();  // Run prepared statement
   echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   // Create prepared statement with named parameters in the form of :paramName
   $pstmt = $dbConn->prepare(
         'INSERT INTO `test` (`id`, `name`) VALUES (:id, :name)');
   // Bind named parameters to program variables, with the type specified.
   $pstmt->bindParam(':id', $id, PDO::PARAM_INT);
   $pstmt->bindParam(':name', $name, PDO::PARAM_STR);
 
   $id = 2001;
   $name = 'john';
   $pstmt->execute();  // Run prepared statement.
   echo 'INSERT INTO: ', $pstmt->rowCount(), ' rows', '<br />';
   echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br />';
 
   $id = 2002;
   $name = "jane";
   $pstmt->execute();  // Run prepared statement again with different values.
   echo 'INSERT INTO: ', $pstmt->rowCount(), ' rows', '<br />';
   echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br /><br />';
 
   // Create prepared statement with positional (unnamed) parameters in the form of ?
   $pstmt = $dbConn->prepare('SELECT * FROM `test` WHERE `id` = ? AND `name` LIKE ?');
   $pstmt->bindParam(1, $id, PDO::PARAM_INT);    // Positional index starts at 1
   $pstmt->bindParam(2, $name, PDO::PARAM_STR);
   $name = 'j%';
   $id = 2001;
   $pstmt->execute();  // Run prepared statement.
   echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />';
   // Fetch resultset row-by-row
   // By default, resultset's row is indexed by BOTH column-name AND column-number (starting at 0).
   while ($row = $pstmt->fetch()) {
      echo 'Retrieve via column name: id=', $row['id'], ' name=', $row['name'], '<br />';
      echo 'Retrieve via column number: id=', $row[0], ' name=', $row[1], '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
   echo '<br />';
 
   // Run again with the same $name but different $id.
   $id = 2002;
   $pstmt->execute();
   echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />';
   // Fetch entire resultset (all rows) into program buffer, with "FETCH_ASSOC" option.
   // Resultset's row is an associative array indexed by column-name only.
   $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC);
   // print_r($resultset);  // all the rows in program buffer
   foreach ($resultset AS $row) {  // Process each row in the buffer
      echo 'Retrieve via column name: id=', $row['id'], ' name=', $row['name'], '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
   echo '<br />';
 
   // Run again with the same parameter values.
   $pstmt->execute();
   echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />';
   // Fetch entire resultset (all rows) into program buffer, with "FETCH_OBJ" option.
   // Resultset's row is an object with column-names as properties.
   $resultset = $pstmt->fetchAll(PDO::FETCH_OBJ);
   foreach ($resultset AS $row) {  // Process each row in the buffer
      echo 'Retrieve via column name: id=', $row->id, ' name=', $row->name, '<br />';
      print_r($row); // for showing the contents of resultset's row
      echo '<br />';
   }
 
   // Close the database connection  (optional).
   $dbConn = NULL;
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // File that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />');
}
?>

Connected
DROP TABLE: 0 rows
CREATE TABLE: 0 rows
INSERT INTO: 1 rows
LAST_INSERT_ID (of the AUTO_INCREMENT column) is 2001
INSERT INTO: 1 rows
LAST_INSERT_ID (of the AUTO_INCREMENT column) is 2002

SELECT: 1 rows
Retrieve via column name: id=2001 name=john
Retrieve via column number: id=2001 name=john
Array ( [id] => 2001 [0] => 2001 [name] => john [1] => john )

SELECT: 1 rows
Retrieve via column name: id=2002 name=jane
Array ( [id] => 2002 [name] => jane )

SELECT: 1 rows
Retrieve via column name: id=2002 name=jane
stdClass Object ( [id] => 2002 [name] => jane )

Dissecting the Program

To use prepared statement, the operating sequence is prepare(), bindParam() and execute().
You can get the affected row thru statement's rowCount().
For SELECT, invoke statement's fetch() or fetchAll() to fetch a row or the entire resultset. fetchAll() is more efficient, but requires more space.
PDO supports the following types of placeholders for parameters:
1. Named Placeholders (recommended): The parameters are marked by :paramName, and bound by the name using bindParam(placeHolderName, varName, paramType), as shown in the above example.
  If a named parameter is used multiple times in the SQL statement, you need to bind only once for all the occurrences. For example,
```
$pstmt = $dbConn->prepare('SELECT * FROM `Users` WHERE `id` = :input OR `email` = :input');
$pstmt->bindParam(':input', $idOrEmail, PDO::PARAM_STR);  // bind both occurrences
$pstmt->execute();
```
  Instead of binding the parameters via bindParam(), you can also pack the actual values in an associative array and run the execute($dataArray), for example,
```
$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (:id, :name)');
$dataArray = array('id' => 1001, 'name' => 'peter');  
$pstmt->execute($dataArray);
```
2. Positional (Unnamed) Placeholders: The parameters are marked by ?, and bind thru the parameter number (starting from 1), for example,
```
$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (?, ?)');
$pstmt->bindParam(1, $id,   PDO::PARAM_INT);
$pstmt->bindParam(2, $name, PDO::PARAM_STR);
$pstmt->execute();
```
  You can also pack the actual values in an array in proper sequence and run the execute($dataArray), for example,
```
$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (?, ?)');
$dataArray = array(1001, 'peter');  
$pstmt->execute($dataArray);
```
Fetch mode: You can also set the fetch mode via setFetchMode(), e.g.,
```
$pstmt->setFetchMode(PDO::FETCH_ASSOC);
```
1. PDO::FETCH_ASSOC: returns an array indexed by column name.
2. PDO::FETCH_OBJ: returns an anonymous object with property names corresponding to the column names.
3. PDO::FETCH_BOTH (default): returns an array indexed by both column name and column number (starting from 1).
4. PDO::FETCH_CLASS: assign the values of columns to the corresponding properties (variables) of the given class.
$pstmt->numRow(): returns the number of rows affected.
$dbConn->lastInsertID(): returns the last insert ID (of the AUTO_INCREMENT column) of the last INSERT operation.
PDOException: You can use getFile(), getLine(), getMessage() to get the filename, line number and exception error message, respectively.

Example 3: Test SQL Injection on Regular vs. Prepared Statements

<?php
/**
 * PdoSQLInjectionTest.php: Checking SQL Injection for regular and prepared statements.
 */
// Define the MySQL database parameters.
$DB_HOST = 'localhost'; // MySQL server hostname
$DB_PORT = '3306';      // MySQL server port number (default 3306)
$DB_NAME = 'test';      // MySQL database name
$DB_USER = 'testuser';  // MySQL username
$DB_PASS = 'xxxx';      // password
 
try {
   // Create a database connection to MySQL server.
   $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS);
   $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception
   echo 'Connected', '<br />';
 
   $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `test`');  // Create prepared statement
   $pstmt->execute();  // Run prepared statement
   echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   $pstmt = $dbConn->prepare(
         'CREATE TABLE IF NOT EXISTS `test` (
            `id` INT AUTO_INCREMENT,
            `name` VARCHAR(20),
             PRIMARY KEY (`id`))');  // Create prepared statement
 
   $pstmt->execute();  // Run prepared statement
   echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   // Create prepared statement with named parameters in the form of :paramName
   $rowCount = $dbConn->exec("INSERT INTO `test` (`name`) VALUES ('peter'),('paul'),('patrick')");
   echo 'INSERT INTO: ', $rowCount, ' rows', '<br /><br />';
 
   // Use regular statement without sanitizing user input.
   $nameInput = "peter' OR '1'='1";  // SQL injection
   $resultset = $dbConn->query("SELECT * FROM `test` WHERE `name` = '$nameInput'");
   // Check result
   foreach ($resultset as $row) {  // Loop thru all rows in resultset
      print_r($row); // Show the contents of resultset's row
      echo '<br />';
   }
   echo '<br />';
 
   // Sanitizing user input via quote(), which escapes special characters.
   $sanitizedNameInput = $dbConn->quote($nameInput, PDO::PARAM_STR);
   print_r($sanitizedNameInput);
   echo '<br />';
   $dbConn->query("SELECT * FROM `test` WHERE `name` = '$sanitizedNameInput'");  // SQL Syntax Error!
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // File that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   echo "[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br /><br />';
   // continue
}
 
try {
   // Use prepared statement to guard against SQL injection.
   $pstmt = $dbConn->prepare('SELECT * FROM `test` WHERE `name` = :name');
   $pstmt->bindParam(':name', $nameInput, PDO::PARAM_STR);  // Bind with unsanitized user input
   $pstmt->execute();  // Run prepared statement.
   // Check result
   echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />';  // 0 rows
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // File that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />');
}
?>

Connected
DROP TABLE: 0 rows
CREATE TABLE: 0 rows
INSERT INTO: 3 rows

Array ( [id] => 1 [0] => 1 [name] => peter [1] => peter )
Array ( [id] => 2 [0] => 2 [name] => paul [1] => paul )
Array ( [id] => 3 [0] => 3 [name] => patrick [1] => patrick )

'peter\' OR \'1\'=\'1'
[PdoSQLInjectionTest][49] Database error: SQLSTATE[42000]: Syntax error or access violation:
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'peter\' OR \'1\'=\'1''' at line 1

SELECT: 0 rows

Dissecting the Program

The first SELECT uses regular statement which is susceptible to SQL injection.
We could use PDO's quote() function to escape special characters to prevent SQL injection, which resulted in SQL syntax error. However, the quote() function is NOT recommended.
Instead, it is recommended to use prepared statement, which accepts but sanitizes and confines the input to a particular parameter.

Example 4: Transaction

Transaction ensures the so-called ACID (Atomic, Consistency, Integrity, Durability) properties. For example, to transfer money from one bank account to another account, it typically involves two SQL UPDATE statements. What if one of the statement fails? A transaction batches a group of SQL statements. The statements either ALL succeed or NONE succeed (atomic).

By default, MySQL statements are executing in an auto-commit mode, which commits every single statement. To enable transaction, use beginTransaction(), which disables the auto-commit. You can then commit() if all statements succeed; or rollBack() if any of the statement fails.

<?php
/**
 * Testing PDO MySQL Transaction.
 * beginTransaction() -> query()/exec()/execute() -> commit() or rollBack().
 */
// Define the MySQL database parameters.
$DB_HOST = 'localhost'; // MySQL server hostname
$DB_PORT = '3306';      // MySQL server port number (default 3306)
$DB_NAME = 'test';      // MySQL database name
$DB_USER = 'testuser';  // MySQL username
$DB_PASS = 'xxxx';      // password
 
try {
   // Create a database connection to MySQL server.
   $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS);
   $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception
   echo 'Connected', '<br />';
 
   // Create an accounts table
   $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `accounts`');
   $pstmt->execute();
   echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   $pstmt = $dbConn->prepare(
         'CREATE TABLE IF NOT EXISTS `accounts` (
            `id`      INT AUTO_INCREMENT PRIMARY KEY,
            `name`    VARCHAR(20),
            `balance` INT)');
   $pstmt->execute();
   echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />';
 
   // Initialize the table
   $pstmt = $dbConn->prepare(
         'INSERT INTO `accounts` (`name`, `balance`) VALUES (:name, :balance)');
   $pstmt->bindParam(':name', $name, PDO::PARAM_STR);
   $pstmt->bindParam(':balance', $balance, PDO::PARAM_INT);
 
   $name = 'peter';
   $balance = 1000;
   $pstmt->execute();
   $name = 'paul';
   $pstmt->execute();  // Same initial balance
 
   // Check the table
   $pstmt = $dbConn->prepare('SELECT * FROM `accounts`');
   $pstmt->execute();
   $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC);
   foreach ($resultset AS $row) {
      print_r($row);
      echo '<br />';
   }
   echo '<br />';
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // File that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />');
}
 
// Use a transaction to transfer money from one account to another account.
try {
   $dbConn->beginTransaction();  // Begin transaction by turning off the auto-commit mode
 
   $pstmt = $dbConn->prepare(
         'UPDATE `accounts` SET `balance` = `balance` + :amount WHERE `name` = :name');
   $pstmt->bindParam(':name', $name, PDO::PARAM_STR);
   $pstmt->bindParam(':amount', $amount, PDO::PARAM_INT);
 
   $name = 'peter';
   $amount = -50;
   $pstmt->execute();
 
   // Uncomment to force an exception
   // throw new PDOException('test');
 
   $name = 'paul';
   $amount = 50;
   $pstmt->execute();
 
   // Commit the transaction if both statements succeed.
   $dbConn->commit();
   // Back to auto-commit mode until another beginTransaction().
 
} catch (PDOException $e) {
   // Roll back all update if any of statement fails.
   echo 'Rolling back all updates...<br />';
   $dbConn->rollBack();
}
 
try {
   // Check the table after the transaction (commit or rollBack).
   $pstmt = $dbConn->prepare('SELECT * FROM `accounts`');
   $pstmt->execute();
   $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC);
   foreach ($resultset AS $row) {
      print_r($row);
      echo '<br />';
   }
 
} catch (PDOException $e) {
   $fileName = basename($e->getFile(), ".php"); // File that trigger the exception
   $lineNumber = $e->getLine();         // Line number that triggers the exception
   die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />');
}
?>

Connected
DROP TABLE: 0 rows
CREATE TABLE: 0 rows
Array ( [id] => 1 [name] => peter [balance] => 1000 )
Array ( [id] => 2 [name] => paul [balance] => 1000 )

Array ( [id] => 1 [name] => peter [balance] => 950 )
Array ( [id] => 2 [name] => paul [balance] => 1050 )

Dissecting the Program

The beginTransaction() method start a transaction, by disabling auto-commit.
The commit() method commits the transaction (if all statements succeed).
The rollBack() method, in the catch clause, rolls back the transaction (if any of the statements fails). No database update will be performed.
commit() and rollBack() also end the transaction, and return the connection to auto-commit, until another beginTransaction() is issued.
To try the rollBack(), either replace the commit() with rollBack(), or issue a SQL statement that throws an exception to trigger the rollBack() in the catch clause. Observe that no record will be updated without a commit().

Example 5: Stored Procedures and Functions

Procedure/Functions are defined in the database, instead of PHP scripts. They are typically more efficient. The drawback is the codes are separated from your PHP codes, and could be hard to maintain.

[TODO]

mysqli By Examples (Deprecated)

Using mysqli's query()

The steps for using mysqli to perform database operations are:

Allocate a mysqli object, which establishes a connection to the database server, via
```
$mysqli = new mysqli(hostname, username, password, databaseName, port);
```

Use the mysqli's query() method to execute a SQL statement. For SELECT, query() returns a result set; for other queries like CREATE TABLE and INSERT, query() returns TRUE or FALSE. For example,

// query() returns TRUE/FALSE for INSERT INTO
$mysqli->query('INSERT INTO ......');
// query() returns a result set for SELECT
$resultSet = $mysqli->query('SELECT * FROM ......');

For SELECT query, process the result set. The result set is kept in an object of mysqli_result, which contains properties and methods for operating the result set. A result set is associated with a row cursor, which initially pointing at the first row. The method fetch_assoc() fetches the current row from the result set, advanced the result set's cursor to the next row, and returns an associative array indexed by column names. The property num_rows stores the number of rows in the result set.
```
for ($r = 0; $r < $resultSet->num_rows; ++$r) {
   // fetch a row as associative array
   $row = $resultSet->fetch_assoc();  
   // Get each cell of the row as $row['columnName']
   ......
}
$resultSet->close();   // close the result set
```
Close the database connection:
```
$mysqli->close();
```

Example: mysqli->query()

<?php   // mysqli_query.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
// Assert no error
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
 
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// Invoke query() to execute a DROP TABLE statement, which returns TRUE/FALSE
$mysqli->query('DROP TABLE IF EXISTS Coffees')
      or die("Error: DROP TABLE failed: ({$mysqli->errno}) {$mysqli->error}");
echo 'INFO: Table Coffees dropped<br />';
 
// CREATE TABLE string
$sqlStr = <<<_END
CREATE TABLE Coffees (
  id     SMALLINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name   VARCHAR(32)        NOT NULL,
  price  DECIMAL(5,2)       NOT NULL DEFAULT 999.99,
  PRIMARY KEY (id),
  INDEX (name)
)
_END;
 
// Invoke query() to execute a CREATE TABLE statement, which returns TRUE/FALSE
$mysqli->query($sqlStr)
      or die("Error: CREATE TABLE failed: ({$mysqli->errno}) {$mysqli->error}");
echo 'INFO: Table Coffees created<br />';
 
// INSERT string
$sqlStr = <<<_END
INSERT INTO Coffees (name, price) VALUES
  ('Espresso',      3.19),
  ('Cappuccino',    3.29),
  ('Caffe Latte',   3.39),
  ('Caffe Mocha',   3.49),
  ('Brewed Coffee', 3.59)
_END;
 
// Invoke query() to execute an INSERT INTO statement, which returns TRUE/FALSE
$mysqli->query($sqlStr)
      or die("Error: INSERT failed: ({$mysqli->errno}) {$mysqli->error}");
// Get the number of rows affected from property affected_rows
echo "INFO: {$mysqli->affected_rows} row(s) inserted";
 
// Invoke query() to execute a SELECT query, which returns a buffered resultset
$resultSet = $mysqli->query('SELECT * FROM Coffees')
      or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}");
 
// Process the resultset and display in an HTML table
echo <<<_END
  <table>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
 
_END;
 
// Fetch each row and print table detail row
// fetch_assoc() returns FALSE if there is no more row
while ($row = $resultSet->fetch_assoc()) {
   echo <<<_END
    <tr>
      <td>{$row['id']}</td>
      <td>{$row['name']}</td>
      <td>{$row['price']}</td>
    </tr>
 
_END;
}
 
echo <<<_END
  </table>
 
_END;
 
$resultSet->close();  // Close the resultset
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
?>

Notes

The fetch_assoc() returns an associative array, containing the current row in the resultset. You can then retrieve the values of the columns via the column names. fetch_assoc() returns FALSE when there is no more row in the resultset.

By default (with resultMode of MYSQLI_STORE_RESULT), running query() with SELECT statement returns a buffered resultset, with the number of rows of the resultset stored in property num_rows. You can also use the num_rows to control the loop:

// The query() returns a buffered resultset, with number of rows in num_rows.
for ($r = 0; $r < $resultSet->num_rows; ++$r) {
   // Fetch current row into an associative array called $row
   $row = $resultSet->fetch_assoc();
   echo <<<_END
    <tr>
      <td>{$row['id']}</td>
      <td>{$row['name']}</td>
      <td>{$row['price']}</td>
    </tr>
 
_END;
}

mysqli's query(), real_query() and multi_query()

mysqli provides 3 query methods: query(), real_query(), multi_query().

query() is the most commonly used. For SELECT, query() combines executing SQL statement with fetching of the result set. For other SQL statement, query() returns TRUE or FALSE.
real_query() executes the SQL statement and returns TRUE or FALSE. For SELECT, it does not return the result set. You need to issue a store_result() to fetch and buffer the result set (or use_result() to initiate fetching of result set but without buffering).
multi_query() is used for executing multiple SQL statements (separated by semi-colons) without fetching the result sets. You can use store_result() to fetch the first result set; more_results() to check if there is more result set, and next_result() to prepare the next result set. multi_query() is needed for executing SQL procedure which returns more than one result set.

Example: real_query() and multi_query()

<?php   // mysqli_real_and_multi_query.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// Use query() to execute a DROP TABLE statement, which returns TRUE/FALSE
$mysqli->query('DROP TABLE IF EXISTS Coffees')
      or die("Error: DROP TABLE Coffees failed: ({$mysqli->errno}) {$mysqli->error}");
echo 'INFO: Table Coffees dropped<br />';
 
// CREATE TABLE
$sqlStr = <<<_END
CREATE TABLE Coffees (
  id     SMALLINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name   VARCHAR(32)        NOT NULL,
  price  DECIMAL(5,2)       NOT NULL DEFAULT 999.99,
  PRIMARY KEY (id),
  INDEX (name)
)
_END;
 
// Use query() to execute a CREATE TABLE statement, which returns TRUE/FALSE
$mysqli->query($sqlStr)
      or die("Error: CREATE TABLE Coffees failed: ({$mysqli->errno}) {$mysqli->error}");
echo 'INFO: Table Coffees created<br />';
 
// INSERT
$sqlStr = <<<_END
INSERT INTO Coffees (name, price) VALUES
  ('Espresso',      3.19),
  ('Cappuccino',    3.29),
  ('Caffe Latte',   3.39),
  ('Caffe Mocha',   3.49),
  ('Brewed Coffee', 3.59)
_END;
 
// Use query() to execute an INSERT INTO statement, which returns TRUE/FALSE
$mysqli->query($sqlStr)
      or die("Error: INSERT INTO Coffees failed: ({$mysqli->errno}) {$mysqli->error}");
// Get the number of rows affected from affected_rows
echo "INFO: {$mysqli->affected_rows} row(s) inserted";
 
/*
 * Use real_query() to execute a single SELECT query, which returns TRUE/FALSE
 */
$mysqli->real_query('SELECT * FROM Coffees')
      or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}");
// real_query(), unlike query(), does not return the result set.
// Need to use store_result() (or use_result()) to fetch the resultset
$resultSet = $mysqli->store_result()
      or die("Error: Store resultset failed: ({$mysqli->errno}) {$mysqli->error}");
// Call helper function to tabulated the resultset
tabulate_resultset($resultSet);
$resultSet->close();  // Close the result set
 
/*
 * Use multi_query() to execute multiple SELECT statements, which returns TRUE/FALSE
 */
$mysqli->multi_query('SELECT * FROM Coffees; SELECT * FROM Coffees WHERE price >= 3.49')
      or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}");
do {
   // Store and process the resultset
   $resultSet = $mysqli->store_result()
         or die("Error: Store resultset failed: ({$mysqli->errno}) {$mysqli->error}");
   tabulate_resultset($resultSet);
   $resultSet->close();
} while ($mysqli->more_results() and $mysqli->next_result());
   // more_results() checks if there is more resultset
   // next_result() prepares the next resultset for fetching
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
 
/*
 * Helper function to tabulate resultset
 */
function tabulate_resultset($resultSet) {
   echo '<table><tr>';
   // Get fields' name and print table header row
   $fields = $resultSet->fetch_fields();  // Get all fields
   foreach ($fields as $field) {
      echo "<th>{$field->name}</th>";
   }
   echo '</tr>';
 
   // Fetch each row and print table detail row
   while ($row = $resultSet->fetch_assoc()) {
      echo '<tr>';
      foreach ($row as $item) {
         echo "<td>$item</td>";
      }
      echo '</tr>';
   }
   echo '</table>';
}
?>

Notes

Two methods are available for fetching the resultset after real_query() or multi_query(): store_result() and use_result().
- store_result() fetches the entire resultset from the server and returns to the client, i.e., buffered. You can use properties such as num_rows to get the number of rows of the result set, or data_seek() to position the cursor.
- use_result() fetches the rows one by one, i.e., unbuffered. You cannot use num_rows or data_seek().
Always use store_result() to retrieve a buffered resultset, unless you have a good reason not to do so.
resultset->close() is the same as resultset->free() and resultset->free_result().
If you get this error: "Commands out of sync; you can't run this command now", you need to close() the previous resultset, before executing another query.

Prepared Statements

A prepared statement (or parameterized statement) contains placeholders for input parameters. You can reuse the same prepared statement many times, with different inputs. Prepared statements are often pre-compiled, which are more efficient than the normal statements. They are also not susceptible to SQL injection attack. Prepared statements are, therefore, RECOMMENDED for production, over the normal statements.

The steps in using prepared statements are:

Allocate a prepared statement, with placeholders indicated as ?, via prepare().
Set the inputs for the placeholders, via bind_param().
Execute the prepared statement with the inputs, via execute().
For SELECT query, retrieve the buffered result set via store_result(); followed by bind_result() to bind the columns with variables; and fetch() to fetch a row with columns bound to the variables.
Repeat Step 2 and 4 for another execution.
Deallocate the prepared statement, via close().

<?php     // mysqli_prepared_statement.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// Allocate a prepared statement with placeholders indicated by ?
$pStmt = $mysqli->prepare("SELECT id, name, price FROM Coffees WHERE price BETWEEN ? AND ?")
      or die("Error: create prepared failed: ({$mysqli->errno}) {$mysqli->error}");
 
// Bind inputs to the placeholders
$priceLow  = 3.29;
$priceHigh = 3.48;
$pStmt->bind_param('dd', $priceLow, $priceHigh)   // 'dd': two doubles
      and $pStmt->execute()
      and $pStmt->store_result()
      and $pStmt->bind_result($id, $name, $price) // bind columns to these variables
      or die("Error: Run prepared failed: ({$pStmt->errno}) {$pStmt->error}");
 
echo <<<_END
<table>
  <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Price</th>
  </tr>
 
_END;
 
while ($pStmt->fetch()) {
   echo <<<_END
  <tr>
    <td>$id</td>
    <td>$name</td>
    <td>$price</td>
  </tr>
 
_END;
}
echo '</table>';
 
// Deallocate prepared statement
$pStmt->close();
 
// An INSERT INTO prepared statement
$pStmt = $mysqli->prepare("INSERT INTO Coffees (name, price) VALUES (?, ?)")
      or die("Error: create prepared failed: ({$mysqli->errno}) {$mysqli->error}");
 
$name = 'Caffe Americano';
$price = 3.69;
$pStmt->bind_param('sd', $name, $price)   // 'sd': string, double
      and $pStmt->execute()
      or die("Error: run prepared failed: ({$pStmt->errno}) {$pStmt->error}");
echo "INFO: {$pStmt->affected_rows} row(s) inserted<br />";
 
// Run the INSERT INTO prepared statement again with different inputs
$name = 'Vanilla Latte';
$pStmt->bind_param('sd', $name, $price)   // 'sd': string, double
      and $pStmt->execute()
      or die("Error: run prepared failed: ({$pStmt->errno}) {$pStmt->error}");
echo "INFO: {$pStmt->affected_rows} row(s) inserted<br />";
 
$pStmt->close();
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
?>

Explanations:

prepare(sqlstr): Allocate a prepared statement with the given SQL string.
bind_parm(types, var1, var2, ...): The types indicate the type of variables, with i for integer, d for double, s for string and b for blog. For example, "sssd" means 3 strings followed by a double.
execute() runs the prepared statement, and set affected_rows for INSERT, DELETE, etc.
store_result() fetches the buffered resultset, and set property num_rows.
bind_result() binds the columns of the resultset with the given variables.
fetch() fetches the current row from the resultset. You can retrieve the columns via the variables bound in bind_result(). fetch() returns FALSE when there is no more row in resultset.
The order for using prepared statement for query with resultset (such as SELECT) is prepare() ⇒ bind_param() ⇒ execute() ⇒ store_result() ⇒ bind_result() ⇒ fetch(). The store_result() retrieves a buffered resultset and sets the num_rows.
The order for using prepared statement for query without resultset (such as INSERT, DELETE and UPDATE) is prepare() ⇒ bind_param() ⇒ execute(). The execute() sets the affected_row.
All these functions return FALSE on error. Hence, they can be chained via "and"; with error reporting via "or".
Take note that prepare() reports error via $mysqli ($mysqli->error and $mysqli->errno); while execute() reports the error via the prepared statement ($pStmt->error and $pStmt->errno). However, bind_param() and bind_result() do not report error on prepared statement (RDBMS), but via the PHP.
In bind_param(), you can bind a PHP's NULL to SQL's NULL with 's' flag.
Don't use the get_result() function, which is not supported in many PHP platforms.

Stored Procedures and Functions

Example: Stored Procedure without Resultset

<?php     // mysqli_procedure.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// A PROCEDURE with IN parameters, no result set
$mysqli->query("DROP PROCEDURE IF EXISTS addItem")
      or die("Error: DROP PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}");
 
$sqlStr = <<<_END
CREATE PROCEDURE addItem (IN inName VARCHAR(32), IN inPrice DECIMAL(5,2))
BEGIN
  INSERT INTO Coffees (name, price) VALUES (inName, inPrice);
END
_END;
 
$mysqli->query($sqlStr)
      or die("Error: CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}");
 
// CAll the procedure
$mysqli->query("CALL addItem('Green tea', 2.89)")
      or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}");
echo "INFO: $mysqli->affected_rows row(s) inserted.<br />";
 
// CAll the procedure again
$mysqli->query("CALL addItem('Yellow tea', 2.99)")
      or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}");
echo "INFO: $mysqli->affected_rows row(s) inserted.<br />";
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
?>

Explanation [TODO]

Notes

We cannot use the prepared statement to run SQL procedure. (Error: This command is not supported in the prepared statement protocol yet.)

Example: Stored Procedure with Result Set

<?php     // mysqli_procedure_resultset.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// A PROCEDURE with resultset
$sqlStr = <<<_END
CREATE PROCEDURE listItemInPriceRange (IN low DECIMAL(5,2), IN high DECIMAL(5,2))
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
  SELECT id, name, price FROM Coffees WHERE price BETWEEN low AND high;
END
_END;
 
$mysqli->query("DROP PROCEDURE IF EXISTS listItemInPriceRange")
      and $mysqli->query($sqlStr)
      or die("Error: DROP/CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}");
 
// CALL procedure with result set
$resultSet = $mysqli->query("CALL listItemInPriceRange(1.99, 3.29)")
      or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}");
tabulate_resultset($resultSet);
$resultSet->close();
 
// The CALL returns two resultsets?! Discard 2nd resultset before issuing another CALL.
// Else: "Commands out of sync; you can't run this command now".
$mysqli->next_result();
 
// Another call to procedure using real_query()
$mysqli->real_query("CALL listItemInPriceRange(3.29, 3.49)")
      or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}");
$resultSet = $mysqli->store_result()
      or die("Error: Store failed: ({$mysqli->errno}) {$mysqli->error}");
tabulate_resultset($resultSet);
$resultSet->close();
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
 
/*
 * Helper function to tabulate resultset
 */
function tabulate_resultset($resultSet) {
   echo '<table><tr>';
   // Get fields' name and print table header row
   $fields = $resultSet->fetch_fields();
   foreach ($fields as $field) {
      echo "<th>{$field->name}</th>";
   }
   echo '</tr>';
 
   // Fetch each row and print table detail row
   while ($row = $resultSet->fetch_assoc()) {
      echo '<tr>';
      foreach ($row as $rowItem) {
         echo "<td>$rowItem</td>";
      }
      echo '</tr>';
   }
   echo '</table>';
}
?>

Explanation [TODO]

Example: Procedure with OUT parameter

<?php     // mysqli_procedure_outparam.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// A PROCEDURE with out parameter
$sqlStr = <<<_END
CREATE PROCEDURE getMinPrice(OUT outMinPrice DECIMAL(5,2))
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
  SELECT min(price) INTO outMinPrice FROM Coffees;
END
_END;
 
$mysqli->query("DROP PROCEDURE IF EXISTS getMinPrice")
      and $mysqli->query($sqlStr)
      or die("Error: DROP/CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}");
 
$mysqli->query("SET @minPrice = 0")
      and $mysqli->query("CALL getMinPrice(@minPrice)")
      or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}");
 
$resultSet = $mysqli->query("SELECT @minPrice AS result")
      or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}");
$row = $resultSet->fetch_assoc();
$minPrice = $row['result'];
echo "The min price is $minPrice.<br />";
$resultSet->close();
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
?>

Explanation [TODO]

Example: Function

<?php     // mysqli_function.php
// Define database related constants
define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', 'myuser');
define('DB_PASSWORD', 'xxxx');
define('DB_DATABASE', 'test');
define('DB_PORT',     3306);
 
// Connect to the MySQL server and set the default database
$mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);
!$mysqli->connect_errno
      or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}");
echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE
      . ' (' . DB_USERNAME . ')<br />';
 
// A function with a return value
$sqlStr = <<<_END
CREATE FUNCTION getMaxPrice()
  RETURNS DECIMAL(5,2)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE maxPrice DECIMAL(5,2);
  SELECT max(price) INTO maxPrice FROM Coffees;
  RETURN maxPrice;
END
_END;
 
$mysqli->query("DROP FUNCTION IF EXISTS getMaxPrice")
      and $mysqli->query($sqlStr)
      or die("Error: DROP/CREATE FUNCTION failed: ({$mysqli->errno}) {$mysqli->error}");
 
// Invoke function
$resultSet = $mysqli->query("SELECT getMaxPrice() AS result")
      or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}");
$row = $resultSet->fetch_assoc();
$maxPrice = $row['result'];
echo "The max price is $maxPrice.<br />";
$resultSet->close();
 
// Disconnect From MySQL server
$mysqli->close();
echo 'INFO: Connection Closed<br />';
?>

Explanation [TODO]

Transaction

[TODO]

Processing HTML Forms

Handing Form Data

PHP Pre-defined Superglobal Variables

PHP pre-defined a few Superglobal variables - Superglobals are built-in variables that are always available in all scopes. They are associative array.

$_GET: an associative array of GET request key-value pairs.
$_POST: an associative array of POST request key-value pairs.
$_COOKIE: an associative array of key-value pairs in the request cookie.
$_REQUEST: contains GET, POST and COOKIE data from the request message
$_FILES: uploaded files
$_SESSION: for session management
Others: $_SERVER, $GLOBALS, $_ENV,

You can retrieve form data from associate array $_GET (for GET method parameters) $_POST (for POST method parameters), or $_REQUEST (both GET and POST parameters and COOKIE data). You can use the isset() function to check if a given element exists in an associative array.

Always sanitize inputs from the client before using them or echoing back to prevent malicious attacks such as SQL injection and HTML injection. You can the PHP function htmlentities() (or htmlspecialchars()), which convert special characters to HTML entities (e.g., > to >, < to <, " to ", & to &).

Example

[TODO]

Uploading Files

To upload a file to the server

On the client-side, provide an HTML <form> with attributes enctype="multipart/form-data" and method="POST" (attribute action default to the current page); and an <input> element with type="file" which displays a text field and a browse file button and name="upFilename".
Upon submitting the form, the file is uploaded to a temporary directory in the server. Information about the uploaded file is kept in the superglobal associative array $_FILES['upFilename']. You can invoke move_uploaded_file(tmpFile, newFile) to move the file from the tmp directory to the desired location.

Example

<!DOCTYPE html>
<html>
<head><title>PHP File Upload</title></head>
 
<body>
<h2>File Upload</h2>
<form method='post' enctype='multipart/form-data'>
  Select File: <input type='file' name='upFile' /><br />
  <input type='submit' value='Upload' />
  <input type='hidden' name='max_file_size' value='4096' />
</form>
 
<?php   // file_upload.php
echo '<br /><p>The upload_max_filesize in php.ini is ' . ini_get('upload_max_filesize') . '</p>';
echo '<p>The post_max_size in php.ini is ' . ini_get('post_max_size') . '</p>';
 
if ($_FILES) {
   if ($_FILES['upFile']['error'] !== 0) {
      // Handle upload error
      $errMsg = '';
      switch ($_FILES['upFile']['error']) {
         case 1: $errMsg = 'File exceeded upload_max_filesize in php.ini'; break;
         case 2: $errMsg = 'File exceeded max_file_size in form hidden field'; break;
                 // max_file_size is not supported in any browser.
                 // You should rely on upload_max_filesize (and post_max_size)
                 // in php.ini to limit file size
         case 3: $errMsg = 'File only partially uploaded'; break;
         case 4: $errMsg = 'No file uploaded'; break;
         case 6: $errMsg = 'Missing temp directory'; break;
         case 7: $errMsg = 'Failed to write to disk'; break;
         case 8: $errMsg = 'A PHP extension stopped the file upload'; break;
         default: $errMsg = 'Other errors?!';
      }
      echo "$errMsg<br />";
   } else {
      // Print information about the uploaded file
      echo <<<_END
        <p><strong>File Uploaded</strong><br />
          The file name is {$_FILES['upFile']['name']}<br />
          The file mime-type is {$_FILES['upFile']['type']}<br />
          The file size is {$_FILES['upFile']['size']}<br />
          The file tmp-name is {$_FILES['upFile']['tmp_name']}</p>
_END;
 
      // Move the uploaded file from tmp to desired location
      $upFilename = $_FILES['upFile']['name'];
      $tmpFilename = $_FILES['upFile']['tmp_name'];
      // Need to create an "images" sub-directory
      move_uploaded_file($tmpFilename, "images/$upFilename");
      echo "<img src='images/$upFilename' />";
   }
}
?>
</body>
</html>

The $_FILES associative array maintains these information about the uploaded file:

$_FILES['upFilename']['name']: the filename and file type of the uploaded file, e.g., myimage.jpg.
$_FILES['upFilename']['type']: the MIME type of the uploaded file, e.g., image/jpeg, text/plain, text/html, text/xml.
$_FILES['upFilename']['size']: the size of the uploaded file.
$_FILES['upFilename']['tmp_name']: the temporary filename stored in the server.
$_FILES['upFilename']['error']: the error code. See the codes for the possible error codes.

For security:

You may check the MIME type of the uploaded file and process only certain file types.
Check the size of the uploaded file.
Do not use the uploaded filename. Instead, hardcode your filename.
If you wish to use the uploaded filename, strip away all the non-alphanumeric and dot characters and possibly convert it to lower case, e.g.,
```
$upFilename = strtolower(preg_replace("/[^A-Za-z0-9.]/", "", $upFilename));
```

Uploading Multiple Files with multiple <input type="file"> Elements

We can upload multiple files in one <form>, by providing multiple <input type="file" name="xxx"> elements with different names.

Uploading Multiple Files with one <input type="file"> Element

The <input> element shall be as follows, where [] indicating multiple items.

<input type="file" name="upFile[]" multiple />

In PHP, the $_FILES['upFile']['tmp_name'] becomes an array, e.g.,

<?php print_r($_FILES['upFile']['tmp_name']); ?>

You can access each of the file via $_FILES['upFile']['tmp_name'][0], $_FILES['upFile']['tmp_name'][1], etc. You can use count($_FILES['upFile']['tmp_name']) to get the number of files uploaded.

For example,

<!DOCTYPE html>
<html>
<head><title>PHP File Upload</title></head>
 
<body>
<h2>Multiple File Upload</h2>
<p>Hold down the Ctrl-key or Shift-key to select multiple file.</p>
<form method='post' enctype='multipart/form-data'>
  Select File: <input type='file' name='upFile[]' multiple /><br />
  <input type='submit' value='Upload' />
  <input type='hidden' name='max_file_size' value='4096' />
</form>
 
<?php   // file_upload.php
echo '<br /><p>The upload_max_filesize in php.ini is ' . ini_get('upload_max_filesize') . '</p>';
echo '<p>The post_max_size in php.ini is ' . ini_get('post_max_size') . '</p>';
 
if ($_FILES) {
   $numFiles = count($_FILES['upFile']['tmp_name']);
   /*
    * Process each file uploaded
    */
   for ($i = 0; $i < $numFiles; ++$i) {
      if ($_FILES['upFile']['error'][$i] !== 0) {
         // Handle upload error
         $errMsg = '';
         switch ($_FILES['upFile']['error'][$i]) {
            case 1: $errMsg = 'File exceeded upload_max_filesize in php.ini'; break;
            case 2: $errMsg = 'File exceeded max_file_size in form hidden field'; break;
                    // max_file_size is not supported in any browser.
                    // You should rely on upload_max_filesize (and post_max_size)
                    // in php.ini to limit file size
            case 3: $errMsg = 'File only partially uploaded'; break;
            case 4: $errMsg = 'No file uploaded'; break;
            case 6: $errMsg = 'Missing temp directory'; break;
            case 7: $errMsg = 'Failed to write to disk'; break;
            case 8: $errMsg = 'A PHP extension stopped the file upload'; break;
            default: $errMsg = 'Other errors?!';
         }
         echo "$errMsg<br />";
      } else {
         // Print information about the uploaded file
         echo <<<_END
           <p><strong>File Uploaded</strong><br />
             The file name is {$_FILES['upFile']['name'][$i]}<br />
             The file mime-type is {$_FILES['upFile']['type'][$i]}<br />
             The file size is {$_FILES['upFile']['size'][$i]}<br />
             The file tmp-name is {$_FILES['upFile']['tmp_name'][$i]}</p>
_END;
 
         // Move the uploaded file from tmp to desired location
         $upFilename = $_FILES['upFile']['name'][$i];
         $tmpFilename = $_FILES['upFile']['tmp_name'][$i];
         // Need to create an "images" sub-directory
         move_uploaded_file($tmpFilename, "images/$upFilename");
         echo "<img src='images/$upFilename' />";
      }
   }
}
?>
</body>
</html>

Managing Session

Session

HTTP is a stateless protocol. That is, the current page has no knowledge of what have been done in the previous pages.

PHP supports session management, via the superglobal associative array called $_SESSION. When a session is created, a web user is assigned a unique session_id. This session_id is propagated in all subsequent requests for that session via either cookie or URL-rewriting. PHP processor associates this session_id with a server-side $_SESSION associative array, which can be used to store session information such as username, role, and shopping cart.

To use session:

Start a session via session_start(). The session_start() starts a new session or resume the existing session.
The superglobal associative array $_SESSION maintain data of the session.
1. You can check if a key-value pair exists via isset($_SESSION['key']).
2. You can set a key-value pair for this session via $_SESSION['key'] = value.
3. You can retrieve value of a particular key via $_SESSION['key'].
4. You can remove a key-value pair via unset($_SESSION['key']).
5. You can use function session_id() to retrieve the session ID.
To terminate the session explicitly, use session_write_close().

Example

This script starts a new session and maintains the access count in $_SESSION['count'].

<?php   // session_start.php
// start a session or resume the existing session
session_start();
 
// Increment counter
if (!isset($_SESSION['count'])) {
  $_SESSION['count'] = 0;
} else {
  $_SESSION['count']++;
}
echo <<<_END
<p>You have accessed this page {$_SESSION['count']} times</p>
<p><a href="session_start.php">Refresh</a></p>
<p><a href="session_end.php">End Session</a></p>
_END;
?>

This script unsets the access count and ends the session.

<?php   // session_end.php
// start a session or resume the existing session
session_start();
 
// Empty the session data
$_SESSION=array();
// Expire Cookie
if (session_id() != "" || isset($_COOKIE[session_name()]))
   setcookie(session_name(), '', time()-2592000, '/');
 
// Terminate session
session_destroy();
 
echo <<<_END
<p>Session Ended</p>
<p><a href="session_start.php">Start Session</a></p>
_END;
?>

PHP Session Timeout

The maximum duration (in seconds) for PHP session timeout can be configured in "php.ini" (e.g., In Ubuntu, "/etc/php5/apache2/php.ini"):

session.gc_maxlifetime = 1440
# default of 1440 seconds = 24 minutes

You can retrieve the configuration parameters during runtime via ini_get() function, e.g.,

echo ini_get('session.gc_maxlifetime');

You can use function ini_set('session.cookie_lifetime', 1200) to set the lifetime of as session cookie to 1200 seconds (20 minutes).

Login/Logout

The procedure is:

Authenticate the user's credential.
Create a new session via session_start(), and place some tokens inside the session, e.g. $_SESSION['isLogin'] = TRUE.
On all the other pages, resume the session ALSO via session_start(), and check if $_SESSION['isLogin'] is there, which indicates a resumed session rather than a new session.

For example,

[TODO]

Logging

Proper logging of information and errors is important in a production environment, for security, debugging and auditing. There are many logging plug-ins for PHP available.

kLogger

A simple and lightweight logging plug-in, available at http://codefury.net/projects/klogger/ or https://github.com/katzgrau/KLogger. It can log various priority messages (e.g., DEBUG, INFO, ERROR) with time-stamp. It, however, logs only to file, which is named log_YYYY_MM_DD.txt under a directory which is configurable.

To use kLogger:

<?php  // kLogger_example.php
 
require_once('/path/to/kLogger.php');
$logDir = "logs";    // The log directory
 
// Set the default timezone for the timestamp in log messages
date_default_timezone_set('Asia/Singapore');
 
// Get a singleton instance of kLogger, by providing
// the log directory name. By default, the log file is named
// log_YYYY-MM-DD.txt under the log directory.
// Log messages with DEBUG-level and above.
// The levels, in order of priorities, are:
//   EMERG, ALERT, CRIT, ERR, WARN, NOTICE, INFO, DEBUG and OFF
// Setting at DEBUG level logs all messages with DEBUG and above.
// Set to OFF to turn-off logging.
$log = KLogger::instance($logDir, KLogger::DEBUG);  // Try vary the level
 
$log->logInfo('Info Test');
$log->logNotice('Notice Test');
$log->logWarn('Warn Test');
$log->logError('Error Test');   // ERR
$log->logFatal('Fatal Test');
$log->logAlert('Alert Test');
$log->logCrit('Crit test');
$log->logEmerg('Emerg Test');
 
// Can also log arrays, objects and NULL, as second parameter
$args1 = array('a' => array('b' => 'c'), 'd');
$args2 = NULL;
$log->logInfo('Testing passing an array or object', $args1);
$log->logWarn('Testing passing a NULL value', $args2);
 
echo 'DONE';
?>

A log entry contains the timestamp, log-level and message.

LAMP Webapp Security

Sanitize User Inputs against SQL Injection

Use prepared statements instead of regular statement, to prevent SQL injection. See PDO example 3.

Sanitize User Inputs against Cross-Site Scripting (XSS)

Using htmlentities() function

The htmlentities($str) replaces special characters with their HTML entity, e.g., < for <, > for >, " for ".

The htmlentities($str, ENT_QUOTES) also replaces single quote to ', which is recommended.

For example,

$str = <<<_END
This is a 'quoted' "<b>string</b>"
_END;
 
var_dump(htmlentities($str));
   // output: This is a 'quoted' &quot;&lt;b&gt;string&lt;/b&gt;&quot;
var_dump(htmlentities($str, ENT_QUOTES));
   // output: This is a &#039;quoted&#039; &quot;&lt;b&gt;string&lt;/b&gt;&quot;

Using HTMLPurifier Library

Check http://htmlpurifier.org.

Where and How to Store MySQL Password (and Configuration Data) used by PHP

To create a database connection in PHP, you need the username/password of a MySQL account (as seen in the above examples). There is no other way but to store database user credentials (most likely in plain text) in a configuration file. This is not just with PHP, but pretty much every language. But, I am certain that you are concern with the security!

Save them in a configuration file called "DBConfig.inc.php" outside the web document root. That is, no direct access for the web users, but PHP scripts can include the file.
Use .php as file extension (not .inc or .xml) - so that the file is parsed by the PHP processor if it ever exposes.
Use .inc.php for all include files, and configure Apache to deny access to all *.inc.php by including the following directive in virtual host configuration or .htaccess (The directive FilesMatch accepts an regular expression for filename matching):
```
<FilesMatch "\.inc\.php$">
   Order deny,allow
   Deny from all
</FilesMatch>
```
[Disable directory listing for production Apache system.]
The file shall be readable by www-data, which runs the Apache (and the PHP scripts), read-write by developers; BUT NOT world-readable. That is, this file is ultimately protected by the underlying operating system. The only way that people can view the file is actually hack into the server?!

Check for a handshaking token before delivering the include file contents, for example,

// In config files, e.g., 'DBConfig.inc.php'
if (!empty('SECURE_INCLUDE_TOKEN')) die('Access error');
......

// In master files
define('SECURE_INCLUDE_TOKEN', 1);
include('DBConfig.inc.php');
......

Do not store credentials as global variables, who live longer than necessary. In addition, do not return them directly. Instead, define them as local variables in a function which returns a PDO object upon request (the PHP scripts need a PDO connection, not the user credentials). Unset them after use. For example, [TODO]
Define different MySQL accounts (hostname/username/password) for different roles, with least privileges. You can fine tune the privilege up to table/column-level. Restrict these accounts from only the required hostname (localhost if it is running on the same machine). Don't use these account for other purposes. Needless to say, don't use root.
```
CREATE USER 'user'@'localhost' identified by 'xxxx';
GRANT SELECT, UPDATE(isActive) ON test.Users to 'user'@'localhost';
  // user@localhost can SELECT (all columns) from the Users table,
  // UPDATE only isActive column, No INSERT, DELETE, etc.
```
For highly secured systems: Store credentials in a password-protected file, in such a way that the administrator needs to enter a password to retrieve its content during start up.

Storing Users' Password in MySQL

References:

http://alias.io/2010/01/store-passwords-safely-with-php-and-mysql/.
http://stackoverflow.com/questions/4795385/how-do-you-use-bcrypt-for-hashing-passwords-in-php

Don't

Don't store password in plain text - obvious!
Don't invent your own password security algorithm, use an established one.
Don't encrypt passwords. Anyone with access to your code can find the decryption key.
Simple hash don't work - because they are susceptible to dictionary attack and brute force attack. It is quite easy to make a list of millions of hashed (a rainbow table) and compare the hashes to recover the password (or one that matches the hash).
Add salt to the hash, but don't use the same salt for all passwords - attacker can generate another set of rainbow tables.
Don't use a weak and fast hashing algorithm such as MD5 and SHA. MD5 is even less secure than SHA, because MD5 is prone to collision, thus easier to find a match for a hash. Fast hashing algorithm is susceptible to brute force attack, as the attacker can also generate the hash dictionary fast.

Do

Use a strong hashing function like bcrypt in PHP.
Use a random salt for each password.
Use a slow hashing algorithm to make brute force attacks practically impossible (because the attacker also needs times to run the hashing algorithm, even worst if checking millions of hashes).
For bonus points, regenerate the hash every time a users logs in, with the same password, by using different salt or different hash strength.

Using bcrypt in PHP

bcrypt is an hashing algorithm which uses salt, and is scalable with hardware, via a configurable number of rounds. The random salt, multiple rounds and "slowness" can effectively prevent dictionary and brute-force attack. bcrypt uses the Eksblowfish algorithm, which is a one-way hashing algorithm. You cannot retrieve the plain-text password without knowing the salt, rounds and key (password).

PHP 5.5 - password_hash()/password_verify()

PHP 5.5 introduces new functions called password_hash() and password_verify() to create and verify a bcrypt hash. Read the PHP manual on password_hash() and password_verify(). For example,

// Generating hash password
$options = array('cost' => 11);   // Set the cost, use default salt
$pwHash =  password_hash('rasmuslerdorf', PASSWORD_BCRYPT, $options);
  // Use bcrypt hashing algorithm with salt and cost.
echo $pwHash;
  // The generated hash contains the algorithm, salt, and cost.

// Verifying hash password
$inputPassword =  ....
$storeHash = ....       // Contain the algorithm, salt, and cost
if (password_verify($inputPassword, $storeHash)) {
    echo 'valid!';
} else {
    echo 'Invalid!';
}

Using crypt() function

The crypt($str, $salt) takes two parameters, the input string and a salt. The salt is also used to identify the hashing algorithm. For blowfish, the salt shall be in this format: $2a$dd$.... where 2a specifies blowfish, dd is a two-digit cost (from 04 to 31), and .... are 22 alphanumeric characters for salt. Extra characters after the salt are ignored.

<?php
/**
 * Testing bcrypt password.
 */
// Generating Password Hash
$password = 'rasmuslerdorf';
$cost = 10;
 
// Create a random salt
$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');
echo $salt, '<br />';
   // psYGmg2b5s60WDwfrawQ4g==
   // First 22 characters are psYGmg2b5s60WDwfrawQ4. The rest are ignored.
 
// Prefix salt with blowfish header $2a$dd$.
$salt = sprintf("$2a$%02d$", $cost) . $salt;
echo $salt, '<br />';
   // $2a$10$psYGmg2b5s60WDwfrawQ4...
 
// Hash the password with the salt
$pwHash = crypt($password, $salt);
echo $pwHash, '<br />';
   // $2a$10$psYGmg2b5s60WDwfrawQ4eThJYeC0esqWNS.KdIKZsCil3gk/BCnS
 
// Verifying User Input Password
$inputPassword = 'rasmuslerdorf';  // Try an invalid password
$storeHash = $pwHash;
if (crypt($inputPassword, $storeHash) === $storeHash) {
   // Use the same function for verification
   // $storeHash has the blowfish header and 22 character salt.
   echo 'Valid!';
} else {
   echo 'Invalid!';
}
?>

Sending Email

Use the PHP mail() function to send email. You can test the mail() without setting up a mail server by configuring php.ini as follows:

$ sudo gedit /etc/php5/apache2/php.ini
......
[mail function]   
sendmail_path = tee mail.out > /dev/null
......
// Restart Apache
$ sudo service apache2 restart

The above directs the email to the file mail.out

Try running the following script to send an email via the mail()

<?php
mail('test@example.com', 'the subject', 'the body', "From: noreply@nowhere.com \r\n");
?>

To send actual email, you need to send up a Mail Server (such as postfix). Read "How to set up postfix Mail Server on Ubuntu"

Edit php.ini:

$ sudo gedit /etc/php5/apache2/php.ini
......
[mail function]   
sendmail_path = "/usr/sbin/sendmail -t -i"
......
// Restart Apache
$ sudo service apache2 restart

You can also leave it to the default.

A Webapp Case Study

Logging in/out and User Management

Shopping Cart

REFERENCES & RESOURCES

PHP mother site @ http://www.php.net; "PHP Manual" @ http://php.net/manual/en/index.php; "PHP Language Reference" @ http://www.php.net/manual/en/langref.php.
PHP manual "mysqli quick start guide" @ http://www.php.net/manual/en/mysqli.quickstart.php.
PHP manual "PHP Data Objects (PDO)" @ http://www.php.net/manual/en/book.pdo.php.
PHP manual "MySQL Driver and Plugin" @ http://www.php.net/manual/en/set.mysqlinfo.php.

Latest version tested: PHP 5.4.9-4 (Ubuntu 13.04)
Last modified: December, 2013