weird PDO query result

Question

Well, the weakest point of PDO is inability to debug it because prepared statements are actually stored on a server side. I spent the whole day around the problem that I cannot understand at all. So, here is the case:

//the query itself
            //Did other customers from the list buy the same style in last 3 months?
            $end = date("Y-m-d H:i:s");
            $start = strtotime("-3 month", strtotime($end));
            $start = date('Y-m-d 00:00:00', $start);
            $q = $db->prepare('SELECT COUNT(*) as totals FROM web_order
                            JOIN web_order_item ON web_order_item.od_id = web_order.od_id
                            WHERE web_order.p_vendor IN (:id)
                            AND od_pcode = :pcode
                            AND od_date BETWEEN :start AND :end
                            ');
            $q->bindValue(":pcode", '1008GD');
            $q->bindValue(":id", $custList);
            $q->bindValue(":start", $start);
            $q->bindValue(":end", $end);
            $q->execute();
            echo "SELECT COUNT(*) as totals FROM web_order
                            JOIN web_order_item ON web_order_item.od_id = web_order.od_id
                            WHERE web_order.p_vendor IN ($custList)
                            AND od_pcode = '1008GD'
                            AND od_date BETWEEN '$start' AND '$end'";
            $res = $q->fetchAll();
die(print_r($res));

And here comes the funny bit - print_r($res) prints the empty array.

Array ( [0] => Array ( [totals] => 0 [0] => 0 ) )

While echo statement with exactly the same query prints

SELECT COUNT(*) as totals FROM web_order JOIN web_order_item ON web_order_item.od_id = web_order.od_id WHERE web_order.p_vendor IN ('210','1107','2295','2452') AND od_pcode = '1008GD' AND od_date BETWEEN '2012-06-13 00:00:00' AND '2012-09-13 13:15:36'

If I execute the echoed query by itself I get 'totals' column to have a value of 1. At the same moment PDO statement returns nothing.

Any help will be appreciated.

UPDATE: Although the answer was posted, I still do not get why this did not work. $custList is actually a string, not an array, because I used

$custList = implode(",", $custList);

By the way I heard from someone that it is possible to set up PDO to store prepared statements locally for debugging purposes. Has anyone heard of it? — Vladimir Hraban, Sep 13 '12 at 12:31
Your problem is at IN(:id) ... take a look at http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition for approaches to this problem — Ken, Sep 13 '12 at 12:52
Value binding is not the same as variable interpolation or copy and paste. `:id` means a placeholder for exactly *one* value, not a number of values. — deceze, Sep 13 '12 at 13:09
But deceze, I used the implode function so there is only one string I bind — Vladimir Hraban, Sep 13 '12 at 13:32
Yes, so you are binding the value `'210','1107','2295','2452'`, literally. You are not binding the four separate values `'210'`, `'1107'`, `'2295'` and `'2452'`, you are binding the *one* value `'210','1107','2295','2452'`. Unless you have a field that contains literally "'210','1107','2295','2452'" in your database, it won't match anything. — deceze, Sep 13 '12 at 13:46
Okay, seems I got it. I'll try to find any info regarding value binding and how it works internally — Vladimir Hraban, Sep 13 '12 at 14:12
It doesn't really matter how it works internally, it's enough if you understand it on a conceptual level. It does not matter what the contents of the bound values are, it's all understood to be one value. A comma in the bound value does not separate the next value, a quote does not close the string. You are entirely separating the *syntax* from the *content*. Maybe see [The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/). — deceze, Sep 13 '12 at 14:34

score 3 · Accepted Answer · answered Sep 13 '12 at 13:05

3

As Ken mentioned in the comments on your question, your problem is the :id binding, namely, that PDO doesn't allow you to bind an array as a param. You'll need to loop through the array and bind the individual elements into the query.

Generally, I handle this with a foreach loop that adds a ? to the query for each element of the array, then I bind each of the elements into the query in the correct order with the other prepared variables (since ? is positional, instead of named).

answered Sep 13 '12 at 13:05

Joe C.

1,538
11
14

Yes, this works now, thank you. Although this makes the code less readable and overall this does not look good. I used the implode function so basically I am not binding an array, but I am binding a string. Why did not it work? – Vladimir Hraban Sep 13 '12 at 13:35

score 0 · Answer 2 · answered Sep 13 '12 at 16:53

0

What I see is two big errors : first is explained by @Joe but the second is here :

$q->bindValue(":start", $start);
$q->bindValue(":end", $end);

You forgot to say "those parameters are integers so do not quote them" by passing a third argument. $q->bindValue(":start", $start,PDO::PARAM_INT); $q->bindValue(":end", $end,PDO::PARAM_INT);

answered Sep 13 '12 at 16:53

artragis

3,677
1
18
30

These are actually not integers but strings representing dates – Vladimir Hraban Sep 14 '12 at 17:35
Ok... something weird in my brain, I checked three times before posting my answer that you put :start and :end in LIMIT close, but I was wrong. I think the "implode" solution is the best. If you want readability, wrap the implode inside a function called "toMysqlSet" that takes an array and output a string formate as you want in your query. – artragis Sep 14 '12 at 17:41

weird PDO query result

2 Answers2