I was just putting together a small test program and I thought I would try using PDO. I really haven’t done anything serious with PDO, just try it a couple times. After recompiling PHP to include the mysql driver for PDO, I coded up the first version of my test program:
$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";
Unfortunately, this didn’t work and it took me a few minutes to figure out why. Actually, I still don’t know exactly why it doesn’t work, but I did find a way to make it work: by using two separate connections, one for each prepared statement. It doesn’t seem like you can have two active statements at the same time on the same connection. I find this hard to believe, so I’m probably doing something wrong.
The other thing I didn’t care for with this PDO code is the non-standard method of iteration with the while loop. Well, the while loop is perfectly standard if you are coming from the PHP 4 style functional DB APIs. However, it doesn’t seem to fit in with the PHP 5 Iterator and foreach integration. PDO doesn’t seem to provide a distinct result set object, or a method of iterating over a result set using the standard PHP Iterator interface.
Now, I can understand why this may be the case. The PDO interface seems to be designed to bind to php variables. Thats not going to work with the Iterator interface. However, I am not using that mode and don’t want to use that mode. It would be nice to be able to acquire an iterator for an example of use such as the one above without having to use fetchAll and ArrayIterator.
Using the Iterator style makes it easier for me to decouple my code from the data source and makes it easier to write test cases for that code.
I was a little bit disappointed. So I moved on to MDB2 with the same code …
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";
I have about the same level of non-experience with MDB2 as with PDO, but my code worked perfectly on the first try and allowed me to use Iterator, which will be helpful to the next stage of my test.
I was a bit impressed. I’ll see how well it handles the next stage of what I want to do.
(And yes, I know there is no error checking in the above code.)
Feed
Subscribe to this blog…
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.
Have you tried ADOdb?
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
You’ve discover the limitation in mysql client api.
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
will allow nested loops.
In MDB2 unbuffered queries are optional, the default is buffered.
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!
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.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.
Jeff Moore’s Blog: PDO versus MDB2
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
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.
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.
[...] PDO (PHP Data Objects) is regarded as state-of-the-art for database access in PHP. But as useful as an abstraction layer may be, the usage of PDO can really be painful. Why? In default mode, PDO uses unbuffered queries to access MySQL. This results in a somewhat strange behaviour: You cannot use two result statements at the same time on the same database connection. (see an example) [...]
besides some minor aspects the main difference between mdb2 and pdo is a huge peformance gain using pdo.
Dapra is correct. This should be done with a single query using a JOIN. It always surprises me how the high majority of web developers have a fairly shallow understanding of SQL.
In default mode, PDO uses unbuffered queries to access MySQL.
bookmark_tags.bookmark_id = ? AND
i do not know what is the mean
Was looking for info on that. I wrote it off as yet another charge, but I am going to examine it all over again.
Regrettably, for most cloud computing platforms the car example you allow doesn’t calculate. If you notice an automobile as a means to an end, and all you value is getting there, then you need an application platform like Google’s AppEngine. Here you merely add an application, and it will be covered. If on the other hand you run ec2, you’re no longer thinking about computer hardware, but still about instances and virtual servers. They all run OS’s that need to be maintained; it’s like taking taxi’s but still having to know everything about the taxi’s engines. I predict that sometime soon we’ll move towards AppEngine like versions or ‘managed clouds’.
deangelo …
[...]PDO versus MDB2 – Professional PHP[...]…
Just desire to say your article is as surprising. The clearness on your put up is simply spectacular and i could suppose you are knowledgeable in this subject. Fine along with your permission let me to grab your RSS feed to stay up to date with imminent post. Thanks 1,000,000 and please continue the gratifying work.
Wow, amazing! Great job! I love my bike, but sometimes I’m seriously limited because it’s so heavy (it’s a 1950s era cruiser).
Ob dies auf diese Weise zu trifft was mein Vorgänger geschrieben hat mag ich mal zu zweifeln
I’m not sure where you’re getting your information, but great topic. I needs to spend some time learning more or understanding more. Thanks for great information I was looking for this info for my mission.
Very nice post. I just stumbled upon your weblog and wanted to say that I’ve really enjoyed browsing your blog posts. In any case I’ll be subscribing to your rss feed and I hope you write again soon!
I’m really impressed with your writing skills and also with the layout on your blog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice quality writing, it’s rare to see a nice blog like this one nowadays..
I loved as much as you’ll receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an impatience over that you wish be delivering the following. unwell unquestionably come more formerly again as exactly the same nearly very often inside case you shield this increase.
Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I acquire in fact enjoyed account your blog posts. Anyway I will be subscribing to your augment and even I achievement you access consistently rapidly.
Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but instead of that, this is wonderful blog. An excellent read. I will certainly be back.
Just want to say your article is as amazing. The clearness in your post is simply cool and I can assume you’re an expert on this subject. Fine with your permission let me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the enjoyable work.
Pretty nice post. I just stumbled upon your blog and wanted to say that I’ve truly enjoyed browsing your blog posts. After all I’ll be subscribing to your rss feed and I hope you write again soon!
It is perfect time to make some plans for the future and it’s time to be happy. I have read this post and if I could I want to suggest you some interesting things or suggestions. Maybe you could write next articles referring to this article. I want to read more things about it!
You actually make it seem so easy with your presentation but I find this matter to be actually something which I think I would never understand. It seems too complicated and extremely broad for me. I am looking forward for your next post, I’ll try to get the hang of it!
Excellent goods from you, man. I have understand your stuff previous to and you’re just extremely fantastic. I really like what you’ve acquired here, certainly like what you are saying and the way in which you say it. You make it enjoyable and you still care for to keep it smart. I can’t wait to read far more from you. This is really a wonderful website.
Excellent goods from you, man. I have understand your stuff previous to and you’re just extremely
magnificent. I really like what you have acquired here, really like what you are stating and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I can’t wait to read much more from you. This is actually a tremendous site.
Excellent post. I was checking constantly this blog and I’m impressed! Very useful info specially the last part
I care for such info much. I was looking for this particular information for a very long time. Thank you and best of luck.
Oh my goodness! an wonderful post dude. Thank you Even so We are experiencing problem with ur rss . Do not know why Not able to join it. Could there be any person acquiring identical rss dilemma? Everyone who knows kindly respond. Thnkx.
Hello there, You’ve done a great job. I will certainly digg it and personally recommend to my friends. I am confident they’ll be benefited from this website.
This consistently amazes me just how blog owners for example your self can find the time along with the dedication to keep on composing exceptional discussions. Your blog isterrific and one of my own have to read weblogs. I just wished to thank you.
Hello, I think that I saw you visited my web site so I came to “return the favorâ€.I’m trying to find things to enhance my web site!I suppose its ok to use some of your ideas!!
Hey there, You have done a fantastic job. I will certainly digg it and personally recommend to my friends. I am confident they will be benefited from this site.
Nice post at 301 Moved Permanently. I was checking continuously this blog and I’m impressed! Extremely helpful info specifically the last part
I care for such info much. I was seeking this certain info for a very long time. Thank you and good luck.
This is obtaining a tad bit more very subjective, but We much choose to Zune Marketplace. The actual screen will be colorful, offers a lot more pizzazz, plus some awesome functions such as Mixview’ that will let you quickly see associated photos, songs, or even some other consumers linked to precisely what you might be following. Clicking on among those will certainly target that piece, and also yet another list of “neighbors” will come into look at, allowing you to navigate close to looking at simply by identical artists, tunes, or maybe customers. Speaking of end users, the particular Zune “Social” is usually wonderful entertaining, permitting you to locate other people together with distributed preferences and turning out to be good friends with these. After this you can pay attention to any playlist produced determined by an amalgamation associated with precisely what your entire good friends are usually hearing, which can be furthermore enjoyable. These related to comfort will probably be happy to recognise you possibly can avoid the public via finding your personal being attentive habits if you and so choose.
Hi this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding knowledge so I wanted to get guidance from someone with experience. Any help would be greatly appreciated!