Why use PDO instead of the standard MySQL functions?

There are several ways to connect to a MySQL database in PHP. You can use mysql_* functions that have become deprecated, old, and slow. You can also use mysqli_* functions that are slowly replacing mysql_* functions; however, they are also slow.

PDO stands for PHP Data Objects; it recently replaced the original MySQL library for the purpose of talking to databases. It also has support for PostgreSQL and SQLite.

The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

PDO is also nice because it provides a data-access abstraction layer, which means that, regardless of the database you use, you employ the same functions to issue queries and fetch data.

The process we use to talk to our MySQL databases is a class called pdo.class.php. We use it to talk to our databases using the PDO library.

Our pdo.class.php file will contain the following code:

<?php

class Db {
  private $pdoInstance;
  private static $instance;
  private function __construct() {
    global $dbhost,$dbname,$dbuser,$dbpass;
    $this->pdoInstance = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
    $this->pdoInstance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $this->pdoInstance->exec("set names 'utf8'");
  }
  private function __clone() {}
  public static function singleton() {
    if (!isset(self::$instance)) {
      $c = __CLASS__;
      self::$instance = new $c;
    }
    return self::$instance;
  }
  /* pdo functions */
  public function quote($str){
    return $this->pdoInstance->quote($str);
  }
  public function lastInsertId(){
    return $this->pdoInstance->lastInsertId();
  }
  public function query($str){
    try {
      return $this->pdoInstance->query($str);
    } catch (PDOException $e) {
      echo "Error : <br />".$str."<br />". $e->getMessage() . "<br />".$e->getTraceAsString();
      exit;
    }
  }
  public function exec($str){
    try {
      return $this->pdoInstance->exec($str);
    } catch (PDOException $e) {
      echo "Error : <br />".$str."<br />". $e->getMessage() . "<br />".$e->getTraceAsString();
      exit;
    }
  }
   
}

Our PDO class is a handy database wrapper that we will use for most chapters in this book.

The main functions of our class that you need to know are mentioned below.

To establish a connection, or use an already established connection, use the following code snippet:

$pdo = Db::singleton();

This call populates the $pdo variable using a singleton. This way, we only have one database connection and, essentially, only one instance of our database class running.

This prevents incidents that may accidentally result in the creation of multiple connections, and also prevents having to pass global variables throughout the site. Instead, we can just call $pdo = Db::singleton(); and we return our PDO class object.

To perform queries, such as select statements, we use the following:

$result = $pdo->query("SELECT * from table");
$total = $result->rowCount();
while( $row = $result->fetch() ){
echo $row['name'];
}

This query will return a result set based on our query, which is stored in the $result variable.

We can then retrieve a total row count using the $result->rowCount(); function.

We can also set up a while loop to populate the $row variable with a new record on each iteration; that is, the $row = $result->fetch() call.

If we want to perform a query that doesn't actually return any results, we can use the following call:

$db->exec("INSERT INTO table SET name='test';");

This will let us make a call to our table and insert, update, or delete data without caring about the result returned.

Oh, and one more thing; if you do perform an insert, you may want the last inserted ID, which you can get by calling the following function after you call the $db->exec() function:

$pdo->lastInsertId();

This only works on inserts, not updates.

You may also notice that, in the class, we wrap all of our queries in a try{}exception{} function, which lets us kill the system in case of errors and display the problem right away.

This has come in handy many times for me during development on projects.

Ok, now let's continue with our chapter.