PDO and SQL IN statements

Question

Im using a sequel for search like this using PDOs

$states = "'SC','SD'";  
$sql = "select * from mytable where states in (:states)";  
$params = array(':states'=>$states);

and I use my function

$result = $this->selectArrayAssoc($sql, $params);

where my selectArrayAssoc function as following

public function selectArrayAssoc($sql, $params = array())
{
  try {
     $sth = $this->db->prepare($sql);
     $sth->execute($params);
     $result = $sth->setFetchMode(PDO::FETCH_ASSOC);
     return $sth->fetchAll();
  } catch(PDOException $e) {
     print $e->getMessage();
     //Log this to a file later when in production
     exit;
  }
}

it does not take the quoted variables, I think it is suppressing, in such cases how to deal with this.

see http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — Tom Haigh, Apr 21 '10 at 08:25

score 3 · Answer 1 · answered Apr 21 '10 at 08:27

3

When using prepared statement placeholders (parameter binding) in general, each occurrence of a placeholder holds exactly one variable.

You're trying to pass several. What's happening is basically that your parameters are escaped: Your :states is replaced with '''SC'',''SD''' or '\'SC\',\'SD\'' internally, rather than with just the raw 'SC','SD' that you want.

answered Apr 21 '10 at 08:27

pinkgothic

6,081
3
47
72

so how do I exactly pass my values here, I implode it from an array and get them like this('sc','sd') here. I tried various quoting methods, everything seem to be failing. – Sai Apr 21 '10 at 08:37
You don't want to quote them. Given `$states = "'SC','SD'";` and `$sql = "select * from mytable where states in (:states)";`, you'll want `$sql = "select * from mytable where states in ('SC','SD')";`. Take a look at the question **Tom Haigh** linked you to. – pinkgothic Apr 21 '10 at 08:43
got answer from one of other questions, thank you pinkgothic – Sai Apr 21 '10 at 08:45
1

That's not really what true prepared statements do, they don't really replace or add quotes, but instead they treat them as *parameters*, which are "injected" into the prepared query on the server (mysql) side. – Madara's Ghost Apr 18 '13 at 07:29
Yeah, you're right; I just find it's easier to understand in those terms. :) – pinkgothic Apr 18 '13 at 15:22

score 2 · Answer 2 · answered Oct 04 '12 at 18:15

pinkgothic is absolute correct. But I think you got the problem, that you have an array of 'states' and want work with this array. You've to prepare the Placeholder for each value in the query.

$states = array('SC','SD');
$phArray = array();
$valArray = array();

foreach($ids AS $key=>$value){
  array_push($phArray, ':PH' . $key);
  $valArray[':PH' . $key] = $value;
}

$sql = 'select * from mytable where states in (' . implode(',', $phArray) . ')';
$params = array($valArray);
$result = $this->selectArrayAssoc($sql, $params);

PDO and SQL IN statements

2 Answers2

Linked