Professional PHP

 
$db = new PDO('mysql:host=localhost;dbname=example', 'example', 'secret');
 
$tags = $db->prepare("
    SELECT 
        * 
    FROM 
        bookmark_tags, tags 
    WHERE 
        bookmark_tags.bookmark_id = ? AND 
        tags.id = bookmark_tags.tag_id 
    ORDER BY 
        tags.name");
 
$bookmarks = $db->prepare("SELECT * FROM bookmarks ORDER BY Title");
$bookmarks->execute();
while ($bookmark = $bookmarks->fetchObject()) {
    echo "<li><a href='{$bookmark->url}'>{$bookmark->title}</a> ";
    
    $tags->execute(array($bookmark->id));
    while ($tag = $tags->fetchObject()) {
        echo $tag->name, " ";
    }
 
    echo "</li>\n";
}
echo "\n";
 

 
require_once 'MDB2.php';
require_once 'MDB2/iterator.php';
 
$db = MDB2::connect("mysql://example:secret@localhost/example");
 
$tagLookup = $db->prepare("
    SELECT 
        * 
    FROM 
        bookmark_tags, tags 
    WHERE 
        bookmark_tags.bookmark_id = ? AND 
        tags.id = bookmark_tags.tag_id 
    ORDER BY 
        tags.name");
 
$bookmarkFinder = $db->prepare("SELECT * FROM bookmarks ORDER BY Title");
$bookmarks = new MDB2_Iterator($bookmarkFinder->execute(), MDB2_FETCHMODE_OBJECT);
    
echo "<ul>\n";
foreach($bookmarks as $bookmark) {
    echo "<li><a href='{$bookmark->url}'>{$bookmark->title}</a> ";
 
    $tags = new MDB2_Iterator($tagLookup->execute($bookmark->id), MDB2_FETCHMODE_OBJECT);
    foreach($tags as $tag) {
        echo $tag->name, " ";
    }
 
    echo "</li>\n";
}
echo "</ul>\n";
 

PHP

mdb2, mysql, pdo, PHP, prepared-statement

13 Responses to “PDO versus MDB2”

1. #1 George S. responds...
   December 26th, 2006 at 3:49 pm

   The requirement is actually ‘need to finish consuming one query before using the same connection for another’, and is a characteristic of using unbuffered mysql connections (i.e. unavoidable aspect of the C client library). Unbuffered connections are used by default in PDO_MySQL.

2. #2 Berislav Lopac responds...
   December 26th, 2006 at 4:21 pm

   Have you tried ADOdb?

3. #3 ian responds...
   December 26th, 2006 at 5:15 pm

   Some useful PDO links covering buffered mysql queries and iterators.

   http://ilia.ws/archives/53-PDO_MySQL-Buffered-Query-Support.html

   http://netevil.org/node.php?uuid=420ccd50-adb9-5159-5209-20ccd507e106

4. #4 JW responds...
   December 26th, 2006 at 5:15 pm

   You’ve discover the limitation in mysql client api.

   
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);


   will allow nested loops.

5. #5 Lukas responds...
   December 26th, 2006 at 5:42 pm

   In MDB2 unbuffered queries are optional, the default is buffered.

6. #6 Rafael Dohms responds...
   December 26th, 2006 at 7:30 pm

   I begun experimenting with PDO in the last two weeks, and i actually liked it.
   As was comented before, the problem you had is just that, can’t begin a new query before finishing the last one.

   One solution is just one that you missed and complained about short after, the method $query->fetchAll(), which does what you are looking for, dumps all the results and you can go through with a foreach loop.

   PDO has some hidden gems in the documentation, take a deeper look and you might see more interesting things.

   I actually compared a prepared statment looped using WHILE and the FOREACH, and actully came back with a 0.0001 to 0.0002 diference, making while faster than foreach, so i added while to my PDO best practices, i should post something about this on my blog soon.

   Cheers!

7. #7 David Zülke responds...
   December 26th, 2006 at 7:40 pm

   You can only have one prepared statement because PDO uses mysql’s native prepared statements, which suck anyway (see http://netevil.org/node.php?uuid=444a6017-0548-2459-2943-44a601714d58).

   Calling $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); after setting up the connection should make that problem go away.

8. #8 Lukas responds...
   December 27th, 2006 at 2:46 am

   One more thing .. you can tell MDB2 to always return result sets as an instance of the iterator (or even custom iterator) class.

   Similarly PDO also allows for fairly flexible “casting” to a class or even class instance in its fetch methods. The API was unfortunately designed to be very “I know what I am doing”. The idea was that PDO was to be light and efficient, leaving the work of adding convenience to layers on top of PDO. I think this was overdone in some places. Like reusing the rowCount() method for affected rows when doing manipulation queries.

   The fact that there is no separate result object is again a speed thing, but also because most RDBMS with proper prepared statement support do not allow result sets to live longer than the prepared statement they were created with. Also remember that PDO by default always uses the prepared statement API. Personally I think a separate object makes more sense, and that is why I stuck with it in MDB2.

   Unfortunately my API concerns were of little interest to the PDO developers. Which I found kind of disappointing given that I had spend the previous 4 years studying all the database API’s in PHP (and other scripting languages). So it goes.

   That being said PDO does make life a lot easier for people who want to build convenience/abstraction layers and even while it is slightly slower for many common operations compared to the native API’s its still what I would base any future work on.

9. PHPDeveloper.org trackbacked on December 27th, 2006 at 6:58 am
10. #10 Wez Furlong responds...
    December 27th, 2006 at 7:42 am

    I haven’t tried running this myself, but the following code should address both your concerns about buffered queries and iteration.

    The fact that people keep asking these same questions about buffering and iteration highlights that we have a documentation problem. May I suggest that people considering PDO take a look at the slides from one of my talks on PDO (try the latest set)

    
$db = new PDO('mysql:host=localhost;dbname=example', 'example', 'secret');
$tags = $db->prepare("
SELECT
*
FROM
bookmark_tags, tags
WHERE
bookmark_tags.bookmark_id = ? AND
tags.id = bookmark_tags.tag_id
ORDER BY
tags.name");
$tags->setFetchMode(PDO::FETCH_OBJECT);
foreach ($db->query("SELECT * FROM bookmarks ORDER BY Title", PDO::FETCH_OBJECT)->fetchAll() as $bookmark) {
echo "<li><a href='{$bookmark->url}'>{$bookmark->title}</a> ";
$tags->execute(array($bookmark->id));
foreach ($tags as $tag) {
echo $tag->name, " ";
}
echo "</li>\n";
}
echo "\n";


    PS: I’m clearly a combination of too {retarded,impatient} to make a code sample readable on your blog. I can’t remedy either condition (and perhaps a portion of that is due to your blog software) so you get what you’ve got

11. #11 Jeff responds...
    December 27th, 2006 at 4:57 pm

    Wow. Thanks, George for the quick answer. I remember reading that, but it never occurred to me that was applicable in this case. I thought someone would know if I posted here.

    Bernislav, I have used ADODB, I just wanted to try something new.

    PDO does have some interesting API elements.

    Wez, sorry about the formatting and thanks for responding. Stock WordPress sucks for code and I’ve just recently switched code formatting plugins and haven’t updated the commenting directions to reveal the correct syntax, which is a BBCODE style [ CODE=PHP ] [ /CODE ] but without the spaces. Sorry again.

    Thanks for the links. I’m reading them now.

12. #12 Dapra responds...
    May 18th, 2007 at 6:33 am

    Why on earth wouldn’t you be using an JOIN there anyway? I can’t see any point at all to having those queries separated.

13. PDO, MySQL and Nested Queries pingbacked on October 29th, 2008 at 3:19 am

Leave a Reply