MySQL query working in phpmyadmin but not in php with PDO

Question

When i run this query in phpmyadmin it runs fine and returns what it should return.
When i run it in php with PDO it fails. Can someone tell me why? The error message shows that the right parameters were used.

Code i run in phpmyadmin.

SELECT  `fd1`.`id` ,  `fd2`.`id` ,  `fd3`.`id` 
FROM  `food` AS  `fd1` ,  `food` AS  `fd2` ,  `food` AS  `fd3` 
WHERE  `fd1`.`food` =  'Empty'
AND  `fd2`.`food` =  'Carrot'
AND  `fd3`.`food` =  'Empty'

Code i run in php

$sql = 'SELECT  `fd1`.`id` ,  `fd2`.`id` ,  `fd3`.`id` 
        FROM  `food` AS  `fd1` ,  `food` AS  `fd2` ,  `food` AS  `fd3` 
        WHERE  `fd1`.`food` = ? 
        AND  `fd2`.`food` = ? 
        AND  `fd3`.`food` = ?'; 
$stmt = $db->prepare($sql);
$stmt->bindParam(1, $food1, PDO::PARAM_STR);
$stmt->bindParam(2, $food2, PDO::PARAM_STR);
$stmt->bindParam(3, $food3, PDO::PARAM_STR);
$stmt->execute();


if (!$stmt->rowCount())
    die("ERROR:FOOD_SEARCH_FAILURE food:$food1, $food2, $food3 rows returned". $stmt->rowCount());

I also tried to run it with:

$stmt->execute(array($food1, $food2, $food3));

Same results

You are 100000% sure $food1 is Empty, $food2 is Carrot, and so on? Does the query succeed? (see [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) — Pekka, Apr 07 '13 at 14:07
It doesn't have to be Empty, Carrot, Empty, it can be any combination of values that are stored in the table. I used various values, and in `phpmyadmin` it works, in `php` it doesn't — Not Amused, Apr 07 '13 at 14:08
@Pekka I hardcoded the values, 'Empty', 'Cherries", 'Empty' in `php` code so i can be 100000% sure, and same results. — Not Amused, Apr 07 '13 at 14:17
@ArunKillu that's unnecessary. Uncaught error will be shown as well. — Your Common Sense, Apr 07 '13 at 14:20
@DeusDeceit what happens, if you put instead of question marks the real values? I mean like the query you execute in phpmyadmin without having to bind params — agim, Apr 07 '13 at 14:24
`it runs fine and returns what it should return` ...what returns? — Amir, Apr 07 '13 at 14:44
rowCount() does not return the number of rows affected by a SELECT statement for most databases [Manual](http://www.php.net/manual/en/pdostatement.rowcount.php) — david strachan, Apr 07 '13 at 14:51
@david strachan... seems like you're right. Now I feel embarrassed. Put that as an answer pls. — Not Amused, Apr 07 '13 at 14:58

score 2 · Accepted Answer · answered Apr 07 '13 at 15:10

2

rowCount() does not return the number of rows affected by a SELECT statement for most databases
Manual

answered Apr 07 '13 at 15:10

david strachan

7,174
2
23
33

For mysql it could be an issue only in case of ancient version of PDO. – Your Common Sense Apr 07 '13 at 15:12
@YourCommonSense I didn't quite understand why it should be an issue only for ancient versions? Obviously it is always an issue, since `rowCount` returns the number of affected (deleted/updated/inserted but not selected) rows! – agim Apr 08 '13 at 12:17

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

First, connect to PDO the way described in the PDO tag wiki and set error reporting on to be sure you will see any error occurred.
Make an intentional error in the query to test if you can see them.

Next, if no errors anyway - check your data, both in database and bound variables (there is nowhere in your code you define them)

edited May 23 '17 at 12:03

Community

1
1

answered Apr 07 '13 at 14:17

Your Common Sense

156,878
40
214
345

MySQL query working in phpmyadmin but not in php with PDO

2 Answers2