PEAR::DB and MySQL’s AUTO_INCREMENT Fields

January 31st, 2004

In which our protagonist begins a more thorough exploration of PEAR, and quickly runs into a challenge. Non-PHP hackers should feel free to avert their eyes. Maybe this is a well known hack, maybe (hopefully!) there is a better way to do this, but a quick Google search turned up a deafening silence, a marked dearth, of information on how to access a MySQL insert_id (the value created by inserting a row into a table with an AUTO_INCREMENT column).

I understand that using AUTO_INCREMENT columns is the “wrong” way to do things as far as PEAR is concerned, I’m supposed to use its implemented in PHP sequences, rather then the native and implemented in C method I’ve been using quite happily for years. (If I find myself migrating my MySQL app to Oracle sometime soon, I promise you AUTO_INCREMENT columns are going to be the least of my worries.)

Perl DBI faced a similar dilemma many years ago, and decided, wisely, to punt on the whole issue. They didn’t provide a solution, and they didn’t preclude you from using your native solution. Turns out sometimes doing less is more.

And lastly, when the hell did PHP get so high and holy that it refuses to let me code the way I want to code? Stuff like this constantly turns me off of PEAR, the whole thing just kind of has a bad smell about it.

But enough with the ranting, so here is my current, hold-me-over-until-someone-points-out-the-error-of-my-ways solution.

Your PEAR::DB object $dbh holds a reference to the MySQL connection object in $dbh->connection. Which allows you take a stroll down memory lane to the bad old days with this little gem:

$id = mysql_insert_id($dbh->connection)

For all our veneer of OO civilization, it doesn’t take much to scratch away the surface and expose our naked barbarian PHP soul does it?

Tagged: Uncategorized , , , ,

5 responses to “PEAR::DB and MySQL’s AUTO_INCREMENT Fields”

  1. showman says:

    quote:(For all our veneer of OO civilization, it doesn’t take much to scratch away the surface and expose our naked barbarian PHP soul does it?)

    No it doesn’t.

    I agree with you entirely. (sorry realise this is a rely to an old post, but I’m sure it still applies) A data abstraction layer, which is what pear::db is, should not try and force you to code something as standard as this in a different way. Only just started using PEAR, your comment of 3 years old still applies. Thanks

  2. Rafik Kocharians says:

    Ok, so PEAR doesn’t provide a way to get the auto-increment id. How does that reflect badly (like a bad smell) on PHP ?

    Pear is only a library. You don’t have to use Pear, you can still use the mysql calls made available to you in PHP. Which work very nicely. If you want a wrapper, write your own. I found problems using DAO and RDO (Microsoft junk). So I wrote my own wrapper around ODBC calls and it is faster than the rubbish that Microsoft forces down your throat.

    So write your own wrapper around the mysql calls and sell the library. You never know, you might end up owning an open source company that sells a good db library with PHP……

    My saying is: Where there is failure, there is opportunity.

  3. Jochen says:

    Rafik, sorry, but PHP database handling is laughable. MDB2 just numbs the worst pains at the expense of performance.

    Why are you defending PHP so much?

  4. NoSCO says:

    Thanks for this little Gem. Just saved hours of frustration :)

  5. Whoskear says:

    Thanks man! The previous coder of my website used a lot of PEAR, jQuery and fancy stuff that when something is repetitive is ok, but when you try something new and special is a pain in the a$$. How many languages we have to use now at the same time to do a stupid website? 9! And I suspect that this will increase if another “genius” create “a new and revolutionary vision of how to say ‘Hello World!'”.