• Home
  • About
  • Doom II
  • Flog 2010
  • Inspiration

Rip and PEAR

Posted in Computing. on Tuesday, March 25th, 2008 by Derek
Mar 25

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 !!

  • Now Playing: Bitcrush - Epilogue In Waves - 09 - Pearl

Leave a Reply

CAPTCHA Image CAPTCHA Audio
Refresh Image

Derek MacDonald

  • Photo Stream
  • Categories
    • Computing
    • Film & TV
    • Gaming
    • General
    • Music
    • Sports
    • Visual Art
  • Search






  • Home
  • About
  • Doom II
  • Flog 2010
  • Inspiration

© Copyright Derek MacDonald. All rights reserved.
Designed by FTL Wordpress Themes brought to you by Smashing Magazine

Back to Top