PHP: Using PDO to query a database

PHP Data Objects (PDO) is a database abstraction layer that is available in PHP 5.1 and above.  Before PDO, PHP developers used extensions like MySQL and MySQLi.  So, why is PDO better than MySQLi?  It supports more database systems than MySQL.

What database systems does PDO support?

 

Driver	Supported DBs
PDO_CUBRID	Cubrid
PDO_DBLIB	FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD	Firebird
PDO_IBM	IBM DB2
PDO_INFORMIX	IBM Informix Dynamic Server
PDO_MYSQL	MySQL 3.x/4.x/5.x
PDO_OCI	Oracle Call Interface
PDO_ODBC	ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_PGSQL	PostgreSQL
PDO_SQLITE	SQLite 3 and SQLite 2
PDO_SQLSRV	Microsoft SQL Server / SQL Azure
PDO_4D	4D

 

Please, keep in mind that not every PDO driver is necessarily installed on your PHP system, though.  If you would like to see which ones are available to you, you can run …

<?php phpinfo(); ?>

This will output a detailed listing of all of the settings for your PHP instance.  If you search for PDO, you should find a listing of supported PDO drivers.

For this demo, we will be working with a ‘users’ table that I created within MySQL.  It has columns for an ‘ID’, ‘first name’, ‘last name’, and ‘username’.

So, how do we query it?

<?php
	// Create a DSN for MySQL 
        // (Syntax: 'mysql:host=[server];dbname=[database]')
	$MySQLDSN = 'mysql:host=localhost;dbname=TestDatabase';

	try {
		// Create a new (MySQL) PDO object 
                // (Syntax: 'PDO([DSN], [UserName], [Password]')
		$db = new PDO($MySQLDSN, 'TestUser', 'TestPassword');
		// What is your intended query?
		$sql = 'select * from users';
		// Store the result of the query in a variable
		$result = $db->query($sql);
		// Store any resulting errors
		$errorInfo = $db->errorInfo();
		// If there were any errors, assign them to the error variables
		if (isset($errorInfo[2])){
			$error = $errorInfo[2];
		}

	} catch (PDOException $e) {
		// Catch any PDO exceptions
		$error = $e->getMessage();
	}

	// Did an error occur during the above operation?
	if (isset($error)) {
		// Display the error
		echo $error;
	} else {
		// Indicate that all went well
		echo 'OK';
	}
?>

In the above example, our database is named ‘TestDatabase’, it is hosted at ‘localhost’, the username is ‘TestUser’ and the password is ‘TestPassword’.  At this point, the result of ‘select * from users’ is stored within $result.

Next, we need to get the individual rows of data out of $result.  You can get ‘the next row’ out of a result with fetch().  In order to allow for a little bit of technology agnosticism, let’s wrap it in a function, though …

function getRow($result) {
    return $result->fetch();
}

So, now how do you loop through records?

<?php
	// Loop through the rows in the query result
	while ($row = getRow($result)) {
		// Add a break, to make the output a little more readable
		echo '<br />';
		// Dump the row to the screen
		print_r($row);
	}
?>

So, when you need to output ‘FirstName’, you can refer to $row[‘FirstName’] within your while loop.