My PHP5 and PDO (PHP Data Objects) experience is very young (to be exact: one day), but I have already learned an important lesson, which is rather a mysql lesson, than PDO, I guess.
PDO default is unbuffered query
I had the simple code:
$dbh = new PDO(’mysql:host=localhost;dbname=test’, ‘root’, ”);
$dbh->prepare(’SELECT * FROM test WHERE test_id=?’);
$dbh->prepare(’SELECT * FROM test1 WHERE test1_id=?’);
It always gave me a “Call to a member function execute() on a non-object” in the line of the last execute(). I was really speechless. I even tried to create a new PDO object every time, that seg-faulted on me. It wasn’t the right thing to do anyway, so that was ok.
With Johann’s help we found out that the unbuffered query is the problem. I didn’t retreive the complete result set (I only do a fetchColumn()), so there is still an open result set on the server. And a new execute() is awry. Calling a fetchAll() after the fetchColumn() helps, but is not what you want to do. The solution is to use buffered queries, create the new PDO object with the attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY set to true, like so:
$dbh = new PDO(’mysql:host=localhost;dbname=test’, ‘root’, ”
,array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
Ilia already mentioned this problem last year. Anyway it still seems to be a point that needs to be stated clearer in the docs. I am happy I learned something. That’s not bad for one day PHP5 and PDO.
Why not Python?
If you followed this blog and are wondering how come that I am doing PHP again, well it is the simplest to get running. I would love to do it in Python, but getting set and started simply takes much more time, and I just want to get a prototype out the door. I am also much more familiar using PHP and my latest experience with the EXIF stuff for Python doesn’t make me very optimistic, that Python is easy enough to use for all the web tasks I expect to do.
I am sure that if I did about a month of this stuff in Python I will have understood it all too, but I don’t have this time now, unfortunately. But at least I have the plan to redo it all in Python (btw. I have many plans).