Hi folks,

 

I'm kind of burned out so I might be overlooking something simple here, but I have a piece of SQL code that works in PHPMyAdmin but not PHP. Basically it's just the num_expressions column that comes up empty (0) in PHP but has good values in SQL. The SQL statement is:

 

SELECT ca.category_name, ca.category_description, ca.category_slug, COUNT( e.id ) AS num_expressions
FROM Categories ca
LEFT JOIN Expressions e ON ( e.category_id = ca.id )
GROUP BY ca.category_name

 

In PHP I have it in an array, like so:

 

Output by print_r()

Array ( [0] => SELECT ca.category_name, ca.category_description, ca.category_slug, COUNT(e.id) AS num_expressions FROM Categories ca LEFT JOIN Expressions e ON ( e.category_id = ? ) GROUP BY ca.category_name [1] => Array ( [0] => ca.id ) )

 

Which is processed by this function...

 

	public function preparedQuery(array $values) {
	$dbh = self::connect(); //returns a PDO DBH object
	$sth = $dbh->prepare($values[0]);
	$sth->execute($values[1]);
	if (empty($sth)) {
		throw new Exception("Nothing was queried.");
	}
	$sth->setFetchMode(PDO::FETCH_ASSOC);
	return $sth->fetchAll();
}

 

And it results in a perfectly fine multi-dimensional array with all good values, except that the num_expressions column is filled with 0s. Like I said, the exact same query on PHPMyAdmin fills that column up with good values.

 

Anyone?

Edited November 14, 201114 yr by troyfawkes

I found the solution.

 

PDO doesn't allow you to pass table names as prepared values - presumably it converts the table name into a string.

 

$sql = "SELECT * FROM table_name t1 LEFT JOIN other_table t2 ON ( t1.id = ? )";
$value = "t2.id";
$sth = $dbh->prepare($sql);
$sth->execute($value);

 

Doesn't work.

 

You have to have t1.id = t2.id hardcoded. You could of course have t1.id = 2 or something like that. No errors are thrown.

+1 for actually posting the solution so anyone else with the same problem can see it.

Edited November 18, 201114 yr by Renaissance-Design