UoL CS Notes
Menu

The PDO Class (Databases in PHP)

COMP284 Lectures

The PHP Data Objects (PDO) extension defines an interface for accessing databases in PHP.

Connections

We create a connection to the database server by creating an instance of the PDO class. We can use the following constructor to this:

$pdo = new PDO(dsn, username, password, options);

The connection remains open for the lifetime of the object. Therefore, we can close the connection by running the following:

$pdo = NULL

A full example would look like so:

# Connection information for the Departmental MySQL Server
$host = "studdb.csc.liv.ac.uk";
$user = "sgfsurn"; # your University username
$passwd = "-------" ; # your MySQL server account password
$db = "sgfsurn"; # your University username
$charset = "utf8mb4";
$dsn = "mysql:host = $host; dbname = $db; charset = $charset " ;

# Useful options
$opt = array (
	PDO::ATTR_ERRMODE	=> PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => false
);

try {
	$pdo = new PDO($dsn, $user, $passwd, $opt);
} catch(PDOException $e) {
	echo 'Connection failed: ', $e->getMessage();
}

Queries

The query() method of PDO object can be used to execute an SQL query:

$result = $pdo->query("SELECT * FROM meetings");

The exec() method of PDO objects executes an SQL statement, returning the number of roes affected by the statement:

$rowNum = $pdo->exec("DELETE * FROM meetings");

Processing Result Sets

Getting results in this way can leave the server open to SQL injection attacks. You should use prepared statements instead.

fetch()

To get a single row as an array from a result set stored in a PDOStatement object, we can use the fetch() method.

By default, PDO returns each row as an array indexed by the column name and a 0-indexed column position in the row:

$row = $result - > fetch ()

array(
	'slot' => 1,
	'name' => 'Michael North',
	'email' => 'M.North@student.liverpool.ac.uk',
	0 => 1,
	1 => 'Michael North',
	2 => 'M.North@student.liverpool.ac.uk'
)

After the last call of fetch() the result set should be released using:

$rows = $result->closeCursor();

fetchAll()

To get all rows as an array of arrays from a resut set stored in a PDOStatement object, we can use the fetchAll() method:

$rows = $result->fetchAll();

We can use a while-loop together with the fetch() method to iterate over all rows in a result set:

while ($row = $result->fetch()) {
	echo "Slot: ", $row["slot"], "<br>\n";
	echo "Name: ", $row["name"], "<br>\n";
	echo "Email: ", $row["email"], "<br>\n";
}

alternatively we can use a foreach loop:

foreach ($result as $row) {
	echo "Slot: ", $row["slot"], "<br>\n";
	echo "Name: ", $row["name"], "<br>\n";
	echo "Email: ", $row["email"], "<br>\n";
}

fetch() uses a cursor that moves through the rows in a result set and does not reset at the end. You should store the result set in an array first, then iterate over the array as often as you like.

bindColumn()

We can bind a variable to a particular column in the result set of a query:

  • Columns can be specified with a number (indexed at 1).
  • Columns can be specified by name.

We can use it like so:

$result->bindColumn(1, $slot);
$result->bindColumn(2, $name);
$result->bindColumn("email", $email);
while($row = $result->fetch (PDO::FETCH_BOUND)) {
	echo "Slot: ", $slot, "<br>\n" ;
	echo "Name: ", $name, "<br>\n" ;
	echo "Email: ", $email, "<br>\n" ;
}
  • Each call to fetch() and fetchAll() will then update all the variables that are bound to columns.
  • The binding needs to be renewed after each query call.

Prepared Statements

The use of parameterised prepared statements is preferable over queries.

  • Prepared statements are pared, analysed, compiled and optimised only once.
  • Prepared statements can be executed repeatedly with different arguments.
  • Arguments to prepared statements do not need to be quoted and biding of parameters to arguments will automatically prevent SQL injection.
  • PDO can emulate prepared statements for a DBMS state does not support them.

    • MySQL supports prepared statements, so PDO emulation should be turned off:

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

Use of prepared statements requires the following three steps:

SQL Templates

An SQL template is an SQL query possibly containing either:

  • Named parameters of the form :name, where name is a PHP identifier.
  • Question marks ?, where the values will be substituted when the query is executed:

The PDO method prepare() turns an SQL template into a prepared statement:

  • On Success - A PDOSatement object is returned.
  • Of Failure - FALSE or an error will be returned.

Binding

bindValue()

We can bind the parameters of a PDOStatement object to a value using the bindValue() method:

  • Named parameters are bound by name.
  • Question mark parameters are bound by position (starting from 1).
  • The value is bound to the parameter at the time bindValue() is executed.

The datatype of the value can optionally be declared.

$stmt1->bindValue(':name', 'Ben', PDO::PARAM_STR);
$email = 'abc:example.com';
$stmt1->bindValue(':email', $email);
$stmt2->bindValue(1, 20, PDO::PARAM_INT);

bindParam()

We can bind the parameters of a PDOStatement object to a variable using the bindParam() method:

These are bound in a similar way to bindValue() except:

  • The variable is bound to the parameter as a reference.
$name = 'Ben';
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':email', $email);

Execution

Prepared statements are executed using the execute() method. Parameters must:

  • Previously have been bound using bindValue() or bindParam().

  • Be given as an array of values to execute()

    These values take precedence over previous values and are bound using bindValue().

When running execute():

  • On Success - The PDOStatement object stores a result set which can be processed in the same way as the result set returned by query().
$stmt1->execute();
$stmt1->execute(array(':name' => 'Eve', ':email' => $email));

Transactions

By default PDO runs in auto-commit mode. To execute a sequenc of SQL statements atomically then you must use the following commands to construct a transaction:

beginTransaction()
  • Turns off auto-commit mode. Changes to the database are not commited until commit() is called.
  • Returns TRUE on success or FALSE on failure.
  • Throws an exception if another transaction is already active.
commit()
  • Changes to the database are made permanent and auto-commit mode it turned back on.
  • Returns TRUE on success or FALSE on failure.
  • Throws an exception if no transaction is active.
rollBack()
  • Discards the changes to the database and re-enables auto-commit mode.
  • Returns TRUE on success or FALSE on failure.
  • Throws an exception if no transaction is active.

Here is an example for transferring money from one account to another:

$balance = array();

// this function stores a key-value pair to a global array called balance

function storeBalance($id, $b) {
	global $balance;
	$balance[$id] = $b;
}

$pdo = new PDO('mysql:host=...; dbname=...' , '...' , '...',
	array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_EMULATE_PREPARES => false));
try {
	// Details of the transaction: payer, payee, amount
	$payerId = 1; $payeeId = 2; $paymentAmount = 10.50;
	$pdo->beginTransaction();
	
	// Obtain payee's and payer's account balances and lock access to both records
	$sql1 = "select id, balace from accounts where id=? or id=? for update";
	$stmt = $pdo->prepare($spq1);
	$stmt->execute(array($payerId, $payeeId));
	
	// store the data retrieved from the database in the $balance array
	$smt->fetchAll(PDO::FETCH_FUNC, 'storeBalance');
	
	// Check whether there is enough money in the payer's account
	if ($balance[$payerId] < $paymentAmount) {
		echo "Insufficient funds in payer's account";
	} else {
		$sql = "UPDATE account SET balance = balance + ? WHERE id = ?";
		$stmt = $pdo->prepare($sql);
		
		// Increase balance of payee's account by payment ammount
		$stmt->execute(array($paymentAmmount, $payeeId));
		
		// Decrease balance of payer's account by payment ammount
		$stmt->execute(array(-$paymentAmount, $payerId));
	}
	
	// Commit the transaction (whether money was transferred or not)
	$pdo->commit();
} catch (PDOException $e) {
	echo "Error: ", $e->getMessage(), "<br>\n";
	
	// roll back the transaction
	$pdo->rollBack();
}