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.

Is a PDO basically the same as an Object Relational Mapper? If so, there’s DataMapper and Ignited Record

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.

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.

kgill - 02 July 2009 04:20 PM

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.

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.

kgill - 02 July 2009 04:20 PM

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

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’].’: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.

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:

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

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. 

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.

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

Thanks Chad.  This wasn’t working but I assume this

Chad Fulton - 06 July 2009 01:02 PM

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

should be

.

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.

Also I did add…

Still when I print_r($data), I get nothing!!  There is data in the table and the sql query on its own works.  I am sure I am missing something.  Anyone?

I think that there is some confusion in this thread about the way PDO and CI are being integrated. In kgill’s DB.php hack, he essentially just makes it so that $this->db is a PDO object.

So, in his approach, $this->db is identical to the following:

In other words, $this->db is no longer connected to CodeIgniter in any way. Maybe this is where your confusion is coming from. Since you are making $this->db a PDO object, you can use it exactly as you normally would (I think you mentioned that you have some familiarity with PDO?).

Edit:
Whoops, posted this too late, and you’d already gotten to the $stmt->fetch(); part.

As far as I can see, there is some bug in your code somewhere, unfortunately since I can’t see it and your database I don’t know what it is.

Try this, for debugging:

Chad Fulton - 06 July 2009 01:39 PM

As far as I can see, there is some bug in your code somewhere, unfortunately since I can’t see it and your database I don’t know what it is.

Try this, for debugging:

//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);

// Notice that execute() returns TRUE on success, FALSE on failure
if($stmt->execute()) {
    echo count($stmt->fetchAll(PDO::FETCH_ASSOC));
}
else {
    print_r($stmt->errorInfo());
}
die;

Stupid me!  There was an issue with my db connection.  Thanks everyone for the help on this.  Even though it was very painful, and probably painful for all involved, I learned a lot. 

By using PDO like this, it does remove it from CodeIgniter’s syntax.  While the idea with CI is to reduce code by using Active Record, I think I will keep my db stuff in PDO.  If someone is against this approach, I would love to hear why.  I am definitely open minded for change.

Thanks again everyone!