SQL Query not completing correctly - not sure why

Question

Alright, I've got a multiple select dropdown on a page called week-select, its selections get passed via ajax to my php page.

I can get the data just fine, but when the query runs it doesn't complete appropriately.

I've got this:

    //Deal with Week Array
    $weekFilter = $_GET['week']; /*This is fine, if it's 1 week the query works great (weeks are numbered 12-15), but if it is 2 weeks the result is formatted like this 12-13 or 13-14-15 or whichever weeks are selected*/
    $weekFilter = str_replace("-",",",$weekFilter); /*This works to make it a comma separated list*/

    .../*I deal with other variables here, they work fine*/

    if ($weekFilter) {
        $sql[] = " WK IN ( ? ) ";
        $sqlarr[] = $weekFilter;
    }
    $query = "SELECT * FROM $tableName";
    if (!empty($sql)) {
        $query .= ' WHERE ' . implode(' AND ', $sql);
    }   
    $stmt = $DBH->prepare($query);
    $stmt->execute($sqlarr);
    $finalarray = array();
    $count = $stmt->rowCount();
    $finalarray['count'] = $count;
    if ($count > 0) { //Check to make sure there are results 
    while ($result = $stmt->fetchAll()) { //If there are results - go through each one and add it to the json
            $finalarray['rowdata'] = $result;
        } //end While
    }else if ($count == 0) { //if there are no results - set the json object to null
            $emptyResult = array();
            $emptyResult = "null";
            $finalarray['rowdata'] = $emptyResult;
    } //end if no results

If I just select one week it works great and displays the appropriate data.

If I select multiple options (say weeks 12, 14 and 15) it runs the query but only displays week 12.

When I manually input the query in SQL, how I imagine this query is getting entered - it runs and displays the appropriate data. So if I put SELECT * FROM mytablename WHERE WK IN ( 12, 14, 15 ) it gets exactly what I want.

I can't figure out why my query isn't executing properly here. Any ideas?

**EDIT: I make the array from the multiple selections a string using javascript on the front end before it is passed to the backend.

why don't u simply echo the query when u do multipe select, than u see the problem in a sec ... — Yordi, May 01 '13 at 17:44
Duplicate of [How can I create a prepared statement for IN () operator?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991146) — Your Common Sense, May 01 '13 at 17:47

Gumbo · Accepted Answer · 2013-05-01T18:07:32.110

1

Your resulting query with values probably looks like this with a single value in IN:

…  WK IN ("12,14,15") …

Either use one placeholder for each atomic value:

if ($weekFilter) {
    $values = explode(",", $weekFilter);
    $sql[] = " WK IN ( " . implode(",", array_fill(0, count($values), "?")) . " ) ";
    $sqlarr = array_merge($sqlarr, $values);
}

Or use FIND_IN_SET instead of IN:

$sql[] = " FIND_IN_SET(WK, ?) ";

edited May 01 '13 at 18:07

answered May 01 '13 at 17:43

Gumbo

643,351
109
780
844

I think you're on the right path here - the IN is probably looking at a single value. Trying to make your example code work (it doesn't work for me as is and I'm still learning). Currently it gives me this error: "syntax error, unexpected ';' in" on the $sql[]= line. This has me going in a good direction though. – Hanny May 01 '13 at 17:54
@Hanny There was just a `)` missing. – Gumbo May 01 '13 at 17:55
Still getting an error: "array_fill() expects exactly 3 parameters, 2 given in ..." But getting there :) Still digging - I think you've got me on the right path. Seeing if I can make it work. – Hanny May 01 '13 at 18:03
Also, for what it's worth - I make the array a string on the front end using javascript before it's passed to the PHP on the backend. – Hanny May 01 '13 at 18:05
@Hanny Ok, fixed that too. – Gumbo May 01 '13 at 18:08
Excellent. It works now - you're the man Gumbo, thanks for your help. This gives me some great insight into how to dissect this stuff too. Thanks again! – Hanny May 01 '13 at 18:09

score 0 · Answer 2 · answered May 01 '13 at 17:42

0

I don't think you can bind an array to a singular ? placeholder. Usually you have to put in as many ? values as there are elements in your array.

answered May 01 '13 at 17:42

tadman

208,517
23
234
262

score -1 · Answer 3 · answered May 01 '13 at 17:45

If your HTML is correct and your week select has name="week[]", then you will get an array back with $_GET['week'];, otherwise without the [] it will only give you 1 value. Then, you're doing a string replace, but it's not a string. Instead, try this:

$weekFilter = implode(',', $_GET['week']);

SQL Query not completing correctly - not sure why

3 Answers3