Codeigniter with PDO

A quick note - although the code examples here are using Codeigniter, pretty much
everything below applies to any framework with - or without - a PDO driver. This is half
"how to", half making the point of the usefulness of a generic, framework agnostic standard.
Please read on and comment if you have anything to add.

You've heard of PDO, but Codeigniter already has great drivers, and the pdo stuff all seems so confusing...why bother? Well, let's take a quick look and see how much opens up to us when we take advantage of this native PHP library.

First - what is PDO? Nothing scary at all - just a replacement for the old mysql_* API you probably learned php with. Cleaner and more secure and the Next Big Thing - and one other important advantage I'll explain later.

This is not a PDO tutorial (excellent docs are here ), but let's just have a quick look at some code to see how it is different from our Codeigniter database objects, and also to check it is working on our system.

Create a page like this:


// save as test_pdo.php

var_dump(PDO::getAvailableDrivers());

$host       = 'localhost';
$dbname     = 'codeigniter';
$username   = 'codeigniter';
$password   = 'abc123';

$pdo_object = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

$query = $pdo_object->query('SELECT * FROM users');

var_dump($query->fetchAll(PDO::FETCH_ASSOC));

The first line just checks which pdo drivers are setup on your local system - we'll use mysql throughout, but check whatever you are using. Then we create a PDO instance and work with it, doing a simple query to get all rows from our users table.

So if that is all working well, let's set things up in Codeigniter and see how to use this. Create the following two db configurations in your config/database.php file:


// config/database.php

//our standard mysql config

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'codeigniter',
    'password' => 'codeigniter',
    'database' => 'codeigniter',
    'dbdriver' => 'mysqli',
...



// V3.0+  our pdo config

$db['pdo'] = array(
    'dsn'   => 'mysql:host=localhost;dbname=codeigniter',
    //'hostname' => 'localhost',
    'username' => 'codeigniter',
    'password' => 'codeigniter',
    'database' => 'codeigniter',
    'dbdriver' => 'pdo',
...

// V2.0+  our pdo config

$db['pdo']['hostname'] = 'mysql:host=localhost;dbname=codeigniter';
$db['pdo']['username'] = 'codeigniter';
$db['pdo']['password'] = 'codeigniter';
$db['pdo']['database'] = 'codeigniter';
$db['pdo']['dbdriver'] = 'pdo';
...


Notice the only difference is that PDO uses dsn rather than hostname; host & dbname are required, plus you can read about other options here

Now go to your controller and let's run the same query a few different ways


//using our mysql db config, like we normally do
$query = $this->db->query("SELECT * FROM users_groups");
var_dump($query->result());

//load the pdo db config
$this->pdo = $this->load->database('pdo', true);

//using the pdo config
$stmt = $this->pdo->query("SELECT * FROM users");
var_dump($stmt->result());

//using the pdo config with active record
$stmt = $this->pdo->get("users");
var_dump($stmt->result());

Wait a minute! We are using $this->pdo->query() & even active record, but those aren't on a PDO object. What's going on here?

This is what we are talking about when we say that Codeigniter has a PDO driver A driver is nothing more than a set of libraries that translate the PDO object methods to Codeigniter-like method names. In this case, the driver is taking the $query->fetchAll(PDO::FETCH_ASSOC) command and mapping it to $result(). This means that anything that was written for Codeigniter database calls continue to work seamlessly.

But it also means something even better. Suppose someone has written an awesome User Manager library:

class User_manager
{

   public function get_users()
   {
    $query = $this->db->sql_query('SELECT * FROM users');
    $result = $query->get_all_rows(RESULT::AS_ARRAY);

    //passing to $result so you can do the usual error handling, et al

    return $result;
   }
}

Sadly, it isn't written for Codeigniter...not sure what framework that is. Oh, well, maybe there's a port out on google somewhere...

No! No more! The wonderful flipside of PDO is developers can now start to create all their libraries that need database access with PDO and it will work everywhere. PDO is part of the php codebase, so your connection object is just like my connection object, is just like the one they are using in Timbuktu.

class User_manager
{
   protected $db;

   public function __construct($config)
   {
      $this->db = $config['conn'];
   }

   public function get_users()
   {
    $query = $this->db->query('SELECT * FROM users');
    $result = $query->fetchAll(PDO::FETCH_ASSOC);

    //passing to $result so you can do the usual error handling, et al

    return $result;
   }

}

All this library requires is a PDO connection passed in, and it works anywhere. Let's use it in Codeigniter:


$this->pdo = $this->load->database('pdo', true);

// we aren't using this, but if your framework doesn't support PDO just make an object natively...
//  $this->pdo->conn_id = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

// var_dump your db object for a moment...
var_dump($this->pdo);

// you see "conn_id"? that is your connection object. Pass that to your library:
$this->load->library('user_manager', array('conn'=>$this->pdo->conn_id));

$users = $this->user_manager->get_users();
var_dump($users);

How easy is that? So, any library that has been created with this object now becomes infinitely useful to the entire php community

Hope that helped!

Contact me

Do you freelance?

Avoid the stressful ups and downs!
What Mom Forgot to Tell You about Remote and Freelance Work