Posted by: phillipnb | May 15, 2011

Using PHP Data Objects – PDO


There are plenty of write ups and tutorials on PHP Data Objects (PDO) and you must be wondering why one more!. Well, in spite of so many write ups and tutorials I find that not many people are using PDO. So, in order to give a slight boost to the usage of PDO among the readers of this blog, I thought a new write-up on how to use PDO was necessary. Without more blah blah let me dive straight into the usage of PDOs.

PDO is basically a data abstraction layer. This means regardless of which database you are using, you will be using the same functions to run sql queries. According to the PHP manual. “We cannot perform any database functions using the PDO extension by itself; we must use a database-specific PDO driver to access a database server”. In order to know what PDO drivers are supported by our current php installation, we need to run the php code, print_r(PDO::getAvailableDrivers()). Thus PDO is a layer providing uniform method of access to all types of databases.

While using PDO, we will come across a term called prepared statement. Particularly, to protect the application from SQL injection attacks, we need a precompiled sql statement which can be executed again and again just by sending the required data to the server. Such a sql statement is called a prepared statement. Of course,prepared statement has other advantages which we are not going to discuss here.

We can have three types of prepared statements, namely – one with named placeholders,one with unnamed placeholders and one without placeholders. In our examples, we will be using named place holders simply for its orderliness. Let us get into an example. In this example we will try to select a few records from a table by using PDO.

$host   = 'localhost';
$dbname = 'alpha';

try {
    $dbHandle = new PDO("mysql:host=$host;dbname=$dbname",'localroot','localpass');
    $dbHandle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stHandle = $dbHandle->prepare('SELECT student_id,student_name,student_age,student_major FROM student');
    $stHandle->execute();
    $result = $stHandle->fetchAll();
    print_r($result);
}
catch(Exception $e) {
    echo "Message : ".$e->getMessage();
}

The entire code is bundled in a try-catch block so that exceptions thrown can be caught and displayed. In the first line of code, $dbHandle = new PDO() we are creating an instance of the PDO class. While instantiating this class, we will be passing the following data as its parameters:

  • Type of database.In this example, we will be using ‘mysql’
  • The host on which the database server will be running
  • The name of the database that we will be working on
  • The user name and password to login to the database

In the second line, $dbHandle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ) we will be telling PDO how it should behave when it encounters an error. We can tell PDO not to display any errors, or, act only during an exception etc etc.You can read more about this here. PDOStatement->setAttribute sets a statement attribute and using that we are setting the type of error mode. The third statement prepares a statement for execution. At the end of execution, it returns a statement object. According the php manual, “$obj->prepare() prepares an SQL statement to be executed by the PDOStatement::execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style”. If the prepare statement was successful from the database side, it returns a PDOStatement object which can then be used to run the execute() statement. The next statement, PDOStatement->fetchAll() returns an array containing all of the result set rows.

Let us look at another example, this time we will use the ‘INSERT’ sql statement:

$host   = 'localhost';
$dbname = 'alpha';

try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname",'localroot','localpass');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $data = array('name' => 'Phillip', 'age' => 29, 'major' => 'Music');
    $sqlQuery = "INSERT INTO student (student_name, student_age, student_major) values (:name,:age,:major)";
    $STH = $dbh->prepare($sqlQuery);
    $STH->execute($data);
}
catch(Exception $e) {
    die("Exception :".$e->getMessage());
}

In the above example, we will leave named place holders for the input values. These names placed holders will be populated using the variable called $data which is an associative array with the names found at placeholders as the key followed by their corresponding values.

Let us take a look at a few other examples, one each for ‘UPDATE’ and one for ‘DELETE’.

$host   = 'localhost';
$dbname = 'alpha';

try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname",'localroot','localpass');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $data = array('name' => 'Katherine', 'id' => 3);
    $sqlQuery = "UPDATE student SET student_name = :name WHERE student_id = :id";
    $STH = $dbh->prepare($sqlQuery);
    $STH->execute($data);
}
catch(Exception $e) {
    die("Exception :".$e->getMessage());
}
$host   = 'localhost';
$dbname = 'alpha';

try {
    $dbh = new PDO("mysql:host=$host;dbname=$dbname",'localroot','localpass');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $data = array('id' => 3);
    $sqlQuery = "DELETE FROM student WHERE student_id = :id";
    $STH = $dbh->prepare($sqlQuery);
    $STH->execute($data);
}
catch(Exception $e) {
    die("Exception :".$e->getMessage());
}

These four examples should be enough to get anybody started about PDO. So, that is what I want to show you all about PHP Data Objects. It looks pretty and ordered compared to using mysql_ functions(mysql_connect,mysql_query etc) to access a database.

OK – till next time keep working on PDO.

Happy PHPing.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: