What is the deal? I’m working on updating my web based video game, Ferp, to run against Sqlite instead of MySQL. Simple, right? Well, not when you wrote it a year or two earlier and most of the DB calls were using the mysqli object in PHP. I know, I know, I learned my lesson. I might actually WANT to use another database some day. Who knew.
So I was using the Zend_Db_Adapter and calling the query method and getting a Zend_Db_Statement_Pdo object back. That’s cool. Just call the rowCount method to see how many records are in the cursor after the SELECT statement was executed. Easy, right? WRONG!
The behavior of rowCount is not consistent from one RDBMS to another. It can’t be relied upon. So I go to the docs (I hate when I do that) and they say that Zend_Db_Statement_Pdo is a wrapper class for PDOStatement, part of the PHP5 distribution. Cool, so I go to their web site and check it out. This is the good part.
Best as I can tell, the solution that some of folks put forward to solve the problem is to run a SELECT COUNT(*) with the same conditional as your base statement first. Then you check the return value to see if your statement would actually return anything. Then if it would, you actually run your statement and process the results. Hmmm.
Will that solution work? Yes. Will it provide performance that most programmers would accept? Sure, if your DB will never hold more than 100 records or so. But if you want to write an application of any size this is not a real solution. It’s kind of nuts!
Ok, I got it off my chest. I’m not giving up on PDOStatement, but I am less convinced this is a good idea than when I started. If you are reading this and you are one of the people that posted on the PHP English documentation about using SELECT COUNT(*) as a solution to this problem, please understand that I did not intend to hurt your feelings. I only wanted to point out that optimization should be your constant companion. Performance is, in fact, a lynch pin for programming success. As a programmer you will constantly be pressured to weigh the trade offs between maintainable code and fast code. In my opinion, SELECT COUNT(*) was TOO far on the maintainable side. It would work, but the performance hit would be unacceptable for most solutions.
Peace
