Note: the only difference in the following examples is the ORDER BY clause.
Good code:
$sql = 'SELECT [date], ? AS [name]
FROM [transactions]
WHERE [category_id] = 10
GROUP BY [date]
ORDER BY [date] ASC';
$stmt = $db->prepare($sql);
$stmt->bindValue(1, 'Test', PDO::PARAM_STR);
$stmt->execute();
$data = $stmt->fetchAll();
//returns rows in $data
Bad code:
$sql = 'SELECT [date], ? AS [name]
FROM [transactions]
WHERE [category_id] = 10
GROUP BY [date]
ORDER BY [date] ASC, [name] ASC';
$stmt = $db->prepare($sql);
$stmt->bindValue(1, 'Test', PDO::PARAM_STR);
$stmt->execute();
$data = $stmt->fetchAll();
//returns an empty array
Why is my second block of code not working? If I run either version of this query directly (in SQL Management Studio), it works either way. And if I get rid of the question mark in PHP and hardcode the value into the query (rather than binding it), that works too! What is going on here?
Update: Here is a sample PHP script that better illustrates the problem: http://snipt.org/ALhd1. In this linked sample code, I include 5 "tests." Tests #1, 2, and 4 all return results, while tests #3 and 5 do not and should illustrate the problem.