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.

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.

Leave a Reply

Your email address will not be published.