PDO in Codeigniter

[eluser]Ickes[/eluser]
Hello All.

Obvious noob here but loving what I have seen and learned so far.

I am trying to wrap my head around the Active Record db handling vs PDO. While I like AR and the simplicity it brings, I am reliant on PDO for its security and find it hard to change.

So, is there a PDO library out there? If not, anyone have any ideas, preferably with an example, of how to implement PDO in a format similar to AR (that is, with minimal code repetition from query to query)?

Great product so far and I can see this saving some big time down the road.

[eluser]Dam1an[/eluser]
Is a PDO basically the same as an Object Relational Mapper? If so, there's DataMapper and Ignited Record

[eluser]xwero[/eluser]
PDO is a database abstraction in the sense that the methods aren't database bound. Query for example executes the sql statement for all supported databases. It doesn't abstract the sql statements.

So you need to have a query builder that creates the statement for the specific database and can output it.

[eluser]kgill[/eluser]
I just hacked DB.php, basically everything between:

// Load the DB classes. Note: Since the active record class is optional

and just before the return statement I commented out and replaced with:

$DB =& new PDO($params['dbdriver'].':host='.$params['hostname'].';dbname='.$params['database'], $params['username'], $params['password']);

It's a quick and dirty hack but it got me what I wanted, $this->db gives me my PDO object.

[eluser]Ickes[/eluser]
[quote author="kgill" date="1246587626"]I just hacked DB.php, basically everything between:

// Load the DB classes. Note: Since the active record class is optional

and just before the return statement I commented out and replaced with:

$DB =& new PDO($params['dbdriver'].':host='.$params['hostname'].';dbname='.$params['database'], $params['username'], $params['password']);

It's a quick and dirty hack but it got me what I wanted, $this->db gives me my PDO object.[/quote]

Thanks. I believe this is what I was looking for.

So to make a call I would then do something like
$this->db->prepare(....);
$this->db->bindParam(...);
$this->db->execute();

?

Again, sorry for being a Noob. Also, if anyone else has any other ideas on implementing PDO, I'd love to hear it.

Thanks.

[eluser]Ickes[/eluser]
[quote author="kgill" date="1246587626"]
$DB =& new PDO($params['dbdriver'].':host='.$params['hostname'].';dbname='.$params['database'], $params['username'], $params['password']);
[/quote]

OK, can I have a little more help?

The arguments to create a new PDO instance are:
new PDO ($dsn, $username, $password, $options)

So as given in the kgill code...
$username = $params['username']
$password = $params['password']

Now I believe the ; should actually be : which gives us
$dsn = $params['dbdriver'].':host='.$params['hostname'].'<b>:</b>dbname='$params['database']

But this isn't working for me. I see:
* $dsn = 'driver://username:password@hostname/database';

so should it be reconfigured to
$params['dbdriver'].'://'.$params['username'].':'.$params['password'].'@'.$params['hostname'].'/'.$params['database']

?

This works but sure seems lengthy and not necessarily correct.

Does anyone have any feedback? Thanks.

[eluser]kgill[/eluser]
What I pasted in was directly from my working set-up, the semicolon is correct. What you want is something that once all the variables are subbed in ends up like this:

Code:

new PDO(
    'mysql:host=hostname;dbname=yourdbname',
    'username',
    'password'
);

// and your database config would look something like this

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "user";
$db['default']['password'] = "password";
$db['default']['database'] = "yourdbname";
$db['default']['dbdriver'] = "mysql";

[eluser]Ickes[/eluser]
Thanks kgill. I am connected to the db and am good to go on that part. However, I can't bind parameters. Any ideas?

model example

Code:

//dummy variable
$pass_thru = 5;

$this->db->prepare("SELECT * FROM tbl_blog WHERE blog_id <= :test");
$this->db->bindParam(':test', $pass_thru, PDO::PARAM_INT);
$data = $this->db->execute();

return $data;

I receive
Fatal error: Call to undefined method PDO::bindParam()

Any help? Also, if there is a place where I can learn this by reading, instead of bugging, please let me know. I try to google and research as much as possible before posting but it seems most just stick with the Active Record method. Or maybe they just know what they are doing.

[eluser]Chad Fulton[/eluser]
Your problem here is that you haven't assigned the prepared statement to a variable. You need to call bindParam on the prepared statement variable, rather than on the PDO object itself.

Code:

//dummy variable
$pass_thru = 5;

$stmt = $this->db->prepare("SELECT * FROM tbl_blog WHERE blog_id <= :test");
$stmt->bindParam(':test', $pass_thru, PDO::PARAM_INT);
$data = $this->db->execute();

return $data;

At this point, you're pretty much using the raw PDO class, so your best bet for documentation is the PHP Manual for PDO.

[eluser]Ickes[/eluser]
Thanks Chad. This wasn't working but I assume this
[quote author="Chad Fulton" date="1246921367"]

Code:

$data = $this->db->execute();

[/quote]

should be

Code:

$data = $stmt->execute();

.

Well that executes but it never returns a result set. If I print_r($stmt), I receive the query but with the unbound variable :test

PDOStatement Object ( [queryString] => SELECT * FROM tbl_blog WHERE blog_id = :test )

If I print_r($data) in the model or the controller, it returns nothing.

So now what am I missing? I can create a PDO connection and execute a query but the result set returns nothing. Maybe Active Record is best but I'm not ready to give up yet.