html form and PDO function with array

Question

I have some choices in checkboxes in HTML, and I need to pass these choices through an array :

<div>
<input type="checkbox" name="language[]" id="anglais"  value="anglais"  /> Anglais
<input type="checkbox" name="language[]" id="allemand" value="allemand" /> Allemand
<input type="checkbox" name="language[]" id="espagnol" value="espagnol" /> Espagnol
<input type="checkbox" name="language[]" id="francais" value="francais" /> Francais
<input type="checkbox" name="language[]" id="italien"  value="italien"  /> Italien
<input type="checkbox" name="language[]" id="russe"    value="russe"    /> Russe
<input type="checkbox" name="language[]" id="chinois"  value="chinois"  /> Chinois

<br/>
<input type="submit" value="Actualiser !">
</div>

Then, this array is sent to the following PDO query :

function retrieve_events_by_type ($DB, $type, $language, $date) {
        $sql = "SELECT organizer, eventname, eventplace, language, eventdate, eventhour, eventminutes FROM events where eventtype = :type";

        if (!empty($language)) {
            $place_holders = implode(',', array_fill(0, count($language), '?'));
            $sql .= " AND language IN ($place_holders)";
    }

        $sql .= empty($date) ? "" : " AND eventdate = :date ";

        $req = $DB->prepare($sql);
        $req -> bindParam(':type', $type, PDO::PARAM_STR);
        if (!empty($language)) {

            $req -> bindParam(':language', $language, PDO::PARAM_STR);
    }

    if (!empty($date)) {
            $req -> bindParam(':date', $date, PDO::PARAM_STR);
    }

    $req -> execute();
    $events = $req->fetchAll(PDO::FETCH_ASSOC);
    $req->closeCursor();
    return ($events);

}

I tried to pass the array to PDO with PDO::execute or PDO::PARAM_STR, but it's not working..

Is it possible to pass named parameters AND placeholders to PDO ?
Is there a way to pass multiple strings in the 'IN' sql clause ?

see http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Tamil Selvan C, Apr 04 '15 at 17:28

bloodyKnuckles · Accepted Answer · 2015-04-04T23:02:36.040

1

One problem is, you're inserting placeholders ? into the SQL query but then you're binding to a named parameter :language. How is PDO to make the connection between the two? Named parameters work when you have a fixed list of known variables, but when you have an array of values, placeholders are simpler.

Here, using PHP PDO placeholders ? for all variables, rather than named parameters, then passing an array of all values to the PDO execute method.

function retrieve_events_by_type ($DB, $type, $language, $date) {
    $sql = 'SELECT organizer, eventname, eventplace, language, eventdate, eventhour, eventminutes FROM events where eventtype = ?';
    $exec_array = array($type);

    if (!empty($language)) {
        //$place_holders = str_repeat('?,', count($language) - 1) . '?'; // alternate
        $place_holders = implode(',', array_fill(0, count($language), '?'));
        $sql .= " AND language IN ($place_holders)";
        $exec_array = array_merge($exec_array, $language);
    }

    if (!empty($date)) {
        $sql .= ' AND eventdate = ?';
        $exec_array = array_merge($exec_array, array($date));
    }

    $req = $DB->prepare($sql);
    $req -> execute($exec_array);

    $events = $req->fetchAll(PDO::FETCH_ASSOC);
    $req->closeCursor();
    return ($events);
}

edited Apr 04 '15 at 23:02

answered Apr 04 '15 at 18:33

bloodyKnuckles

11,551
3
29
37

Thanks, I have some issues to get that working. Am I forced to change $date and $type to an array, merge it, etc... ? Even if just the $language is really an array ? And I don't understand the end of the line `. '?');` Why this last placeholder ? My error is concerning the line `$place_holders = str_repeat` : Unsupported operand types. (I already tried to change brackets.) – Isador Apr 04 '15 at 22:42
Regarding the use of `str_repeat`, yours works fine. I just think the `str_repeat` method is simpler. I rem'd out `str_repeat` and put yours back in. *FYI*, the extra `?` is to accommodate only having commas between each character, and not one after each. Notice there's also a `count($language) - 1`. If I used `str_repeat` with the full count and without the extra `?` I'd end up having an extra comma at the end: `?,?,?,?,?,`. – bloodyKnuckles Apr 04 '15 at 22:45
The reason I put all the values in an array is so I could then pass them all in one shot to the `execute` method. You *can* also use `bindParam` and pass the values individually as scalars (not array). But then you'd need to loop over the `$language` array to pass each one into a `bindParam`. I think pushing all the scalars into an array, merging it with the `$language` array, and passing that one array to `execute` is simpler. – bloodyKnuckles Apr 04 '15 at 22:53
It seems working after checking some str_repeat syntax. And yes, I see why you've change all in array, and then pass a global array of values. Nice explanations, thanks ;-) – Isador Apr 04 '15 at 22:58
Oh, just found the error in that `str_repeat` function. There was an extra paren at the very end of that line, after the final `?`. Fixed it in the post. – bloodyKnuckles Apr 04 '15 at 22:59

html form and PDO function with array

1 Answers1