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.
Column aliases can be used for derived columns. Column aliases can be used with GROUP BY and ORDER BY clauses. We cannot use a column alias with WHERE and HAVING clauses.
Sort Items Based on Host Variable Values You must specify fields in a CASE expression by column name. Column aliases and column numbers are not permitted in this context.
Yes, you can certainly use column aliases in your "order by" clause.
To create column aliases: Type: SELECT column1 [AS] alias1, column2 [AS] alias2, ... columnN [AS] aliasN FROM table; column1, column2, ..., columnN are column names; alias1, alias2, ..., aliasN are their corresponding column aliases; and table is the name of the table that contains column1, column2, ....
I've managed to reproduce the problem with PHP 5.4 and SQL Server 2012.
The problem seems to lie in the ODBC driver for PDO. The successful tests give the same result using both drivers, but the below uses test3 as a sample.
Using the native SQL Server PHP driver from Microsoft (3.0) gives the correct result;
$db = new PDO('sqlsrv:server=.\\SQLEXPRESS');
array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }
array(1) { [0]=> array(4) {
["date"]=> string(23) "2013-07-23 10:34:24.497"
[0]=> string(23) "2013-07-23 10:34:24.497"
["name"]=> string(4) "Test"
[1]=> string(4) "Test"
}
}
...while running the exact same code using ODBC gives your exact failed result;
$db = new PDO('odbc:driver={SQL Server Native Client 11.0};server=.\SQLEXPRESS;Trusted_Connection=yes');
array(4) { [0]=>string(5) "00000" [1]=> int(0)
[2]=> string(24) " ((null)[0] at (null):0)" [3]=> string(0) "" }
array(0) { }
In other words, it's not a limitation in PDO itself or in SQL Server, it's a limitation/bug in the ODBC driver.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With