PDO in PHP : problem in binding parameter

Question

I want to pass (1,2,3) this to an IN query of where clause. something like this.

$sql = $db->prepare("
    SELECT 
        id, attribution,..........................
    FROM
        filterdaiict
    WHERE Checkbox
        IN (:checkbox)
    HAVING
        distance < :radius
    ORDER BY
        distance ASC
    LIMIT
        0, 50 
");

$sql->bindParam(
    ':checkbox',
    GetCheckboxValue ( $value['CHECKBOXLIST'] ),
    PDO::PARAM_STR 
);

where the function GetCheckboxValue ( $value['CHECKBOXLIST'] ) returns string like 1,2,3. This code does not work. I do not know where the errors could be. Let me know. Thanks in advance.

3 revs, 3 users 94% · Accepted Answer · 2011-08-11 09:25:38Z

This example builds on SamT's answer above to build a query string but still bind the parameters.

// Get your IDs into an array
$ids = explode(',', GetCheckboxValue($value['CHECKBOXLIST']));

// Build a list of placeholders that we can use in the query
$params = array();
foreach ($ids as $idx => $val) {
     $key = ':checkbox' . $idx;
     $params[$key] = $val;
}    

// Join the keys to use as placeholders
$querystr = implode(', ', array_keys($params));

// Prepare our statement using the placeholders we made above
$sql = $db->prepare( " SELECT id, attribution,...... .................... 
    FROM filterdaiict where Checkbox IN ($querystr) 
    HAVING distance < :radius 
    ORDER BY distance ASC LIMIT 0, 50 " );

// Bind a value for each key
foreach ($params as $key => &$val) {
    $sql->bindParam(
        $key,
        $val,      
        PDO::PARAM_STR 
    );
 }

Yes you're right with your comment on SamT's answer. Removed my answer.

SamT · Accepted Answer · 2011-06-03 08:46:41Z

1

You will have to build they query yourself.

$ids = array(1, 2, 3);
$querystr = implode(',', $ids);

$sql = $db->prepare( " SELECT id, attribution,...... .................... 
    FROM filterdaiict where Checkbox IN ($querystr) 
    HAVING distance < :radius 
    ORDER BY distance ASC LIMIT 0, 50 " );

answered Jun 3, 2011 at 8:46

SamT

10.7k2 gold badges33 silver badges39 bronze badges

2 Comments

Matthieu Napoli Over a year ago

@papu Do not use this solution, use rather the one given by TheCandyMan.

El Yobo Over a year ago

Do not use the one given by TheCandyMan either, because it will not work. The value will be bound as '1,2,3', which will not match 1, 2 or 3 as intended. This approach will work, but requires sanitisation of the parameters; a better approach is to build the query string dynamically; I'll add an answer below.

Collectives™ on Stack Overflow

PDO in PHP : problem in binding parameter

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related