1

I have an array of id's in a cookie. When I print the cookie array it prints like this: ["39","580"]. I need to access id 39 and 580.

I have tried everything I need to use this in a query like this:

    $queryordenar = $db->prepare("SELECT id FROM property 
    WHERE id IN (:list) ORDER BY price ASC");
    $queryordenar->execute(array(
    'list' => $array
     ));

Thanks

Helenp
  • 143
  • 2
  • 15
  • The :list won't expand as you expect. You need to split the array using ```explode```, and then build the string needed in your SQL statement before you execute the statement. – Sloan Thrasher Aug 16 '17 at 16:50
  • Possible duplicate of [Can I bind an array to an IN() condition?](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) – James K. Aug 16 '17 at 16:59

1 Answers1

3

You can't pass an array as a parameter. You need to explode the array, pass in a ? for every value, and then bind the correct value to each placeholder:

$bindPlaceholders = [];
foreach ($array as $val) {
    $bindPlaceholders[] = "?";
}
$bindString = "(".implode(",", $bindPlaceholders).")";
$queryordenar = $db->prepare("SELECT id FROM property WHERE id IN ".$bindString." ORDER BY price ASC");
foreach ($array as $i => $val) {
    $queryordenar->bindValue($i + 1, $val); // binding starts at 1
}
$queryordenar->execute();
ishegg
  • 9,685
  • 3
  • 16
  • 31
  • 1
    There must be a ) missing on this line: $bindString = "(".implode(",", $bindPlaceholders.")"; – Helenp Aug 16 '17 at 17:05
  • That means $array is not really an array. Oh, I see it's JSON encoded. So first decode it: $array = json_decode($array). Then, you will have an array and the $bindString value will be populated correctly. – ishegg Aug 16 '17 at 17:18
  • I get: PHP Warning: Invalid argument supplied for foreach() in /home… syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') – The cookie is done like this previously: setcookie("list", json_encode($array) – – Helenp Aug 16 '17 at 17:20
  • That made the trick thanks @ishegg, full code: $array = $_COOKIE["list"]; $array = json_decode($array); $bindPlaceholders = []; foreach ($array as $val) { $bindPlaceholders[] = "?"; } $bindString = "(".implode(",", $bindPlaceholders).")"; $queryordenar = $db->prepare("SELECT id FROM property WHERE id IN ".$bindString." ORDER BY price ASC"); foreach ($array as $i => $val) { $queryordenar->bindValue($i + 1, $val); // binding starts at 1 } $queryordenar->execute(); – Helenp Aug 16 '17 at 17:22
  • Glad I could help. – ishegg Aug 16 '17 at 17:24