PHP MySQL PDO execute function not giving any result when same parameter is used twice

Question

If I manually execute the following query replaced with values instead of : , its working but the same query is not returning anything if execute through php...

$sql_check = $dbConnection->prepare("select sum(case when 
upper(main_category)=upper(:main_category) and 
upper(sub_category1)=upper(:sub_category1) then 1 
when upper(main_category)=upper(:main_category) then 1 else 0 end) cnt from dbs.sales;");
$sql_check->execute(array(':main_category' => $main_category,':sub_category1' =>
$sub_category1,':sub_category2' => $sub_category2));

foreach ($sql_check as $row) {
$cnt=$row['cnt'];
echo "cnt ".$cnt."<br>"; //No rows returned upon execution
}//end of for loop

Here :main_category is being used twice

The above query is working fine if i DO NOT use :main_category twice...

Please help me and let me know how to run the query if the same parametrised field is to be used in the query?

Then don't use `:main_category` twice. Call it something else. [**RTFM**](http://www.php.net/manual/en/pdostatement.execute.php) — Bojangles, Nov 17 '13 at 18:54

score 2 · Accepted Answer · answered Nov 17 '13 at 18:46

2

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

first to make it work

and

$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

would be useful too

answered Nov 17 '13 at 18:46

Your Common Sense

156,878
40
214
345

Please let me know the functionality of these setting? – logan Nov 17 '13 at 18:51
http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks/8265319#8265319 and http://stackoverflow.com/tags/pdo/info – Your Common Sense Nov 17 '13 at 18:56
Thanks.. But is there any disadvantage of setting to PDO::ATTR_EMULATE_PREPARES = TRUE ? – logan Nov 17 '13 at 19:00

PHP MySQL PDO execute function not giving any result when same parameter is used twice

1 Answers1