If you ever use the deprecated PHP PEAR DB API for using prepared SQL statements that are portable across different database management systems (stop using the mysql_*() functions), I have a fun bug for you! It kept reporting the simple message “DB Error: mismatch”, which generally means the number of parameters you’re passing into the prepared query don’t match the number of markers you’ve made where parameters should be placed (i.e., the question marks).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | /** * Retrieve list of categories that aren't under a given category, * at least one level deep. * * @param int $categoryId * @return array of BusinessCategory objects */ public static function getNotUnderCategory($categoryId) { $query = "SELECT `businessCategory`.`id`" .", `businessCategory`.`parentId`" .", `businessCategory`.`name`" .", `businessCategory`.`description`" .", COUNT(`Category`.`categoryId`) AS `numBusinesses` " ."FROM `businessCategory` " ."LEFT JOIN `businessCategory` `ChildCategory` " ."ON `ChildCategory`.`parentId` = `businessCategory`.`id` " ."LEFT JOIN `businessCategoryEntry` `Category` " ."ON `Category`.`categoryId` = `businessCategory`.`id` " ."OR `Category`.`categoryId` = `ChildCategory`.`id` " ."WHERE `businessCategory`.`id` != ? " ."AND `businessCategory`.`parentId` != ? " ."GROUP BY `businessCategory`.`id` " ."ORDER BY `businessCategory`.`name`"; $db = ElcDatabase::getDb(); $results = $db->query($query, array($categoryId, $categoryId)); if(PEAR::isError($results)) throw new Exception($results->getMessage()); $businessCategories = array(); while($results->fetchInto($row, DB_FETCHMODE_ASSOC)) { $businessCategory = new BusinessCategory($row); array_push($businessCategories, $businessCategory); } $results->free(); return $businessCategories; } |
As you can see, there are two question marks and two parameters passed. After almost an hour of fucking about, I figured out that my dear “DB Error: mismatch” was referring to PEAR not accepting != as a boolean operator even though this is valid in MySQL’s syntax. So no, I had to switch it to <>. Way to go on those error messages. I guess this is why it’s been superseded by the more type-heavy MDB2. Doesn’t make up the fact that PHP is a terrible language to work with. I probably should actually get into frameworks at some point. This would require effort.
Please note, my final code is much cleaner; I just had to merge a few methods together to make this presentable in a post. Stop looking at me !!
