2

I'm trying to get a MySQL query similar to this one to work using PHP and PDO

select user_id from  users where user_id in (7,8,9)

If I do this:

$userlist ='7,8,9';
$stmt->bindValue(':userlist', $userlist, PDO::PARAM_STR);

The MySQL server log records:

2016-01-27T16:51:52.644453Z   32 Prepare    select user_id from  users where user_id in (?)
2016-01-27T16:51:52.644489Z   32 Execute    select user_id from  users where user_id in ('7,8,9')

And only the row where user_id is 7 is returned.

If I do this:

$userlist ='7,8,9';
$stmt->bindValue(':userlist', (int)$userlist, PDO::PARAM_INT);

MySQL logs this:

2016-01-27T16:54:09.110990Z   33 Prepare    select user_id from  users where user_id in (?)
2016-01-27T16:54:09.111026Z   33 Execute    select user_id from  users where user_id in (7)

And again I only see one of three rows. I feel this must have a very basic solution, but I've not been able to find it..

KevInSol
  • 2,560
  • 4
  • 32
  • 46
  • 1
    You need to make as many parameters in your IN clausel as many ids you want to return. – Charlotte Dunois Jan 27 '16 at 17:11
  • @Charlotte Dunois - thanks, just seen this. re your comment in the answer below, I feel it would be nice to have something like PDO::PARAM_array for this common case – KevInSol Jan 27 '16 at 17:34
  • You may take a look at [this SO question](http://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array). – Rajdeep Paul Jan 27 '16 at 17:42

1 Answers1

0

You must bind each value

select user_id from  users where user_id in (:id1,:id2,:id3)

For this query:

$stmt->bindValue(':id1', 7);
$stmt->bindValue(':id2', 8);
$stmt->bindValue(':id3', 9);

Or you may use some query builder. For example, what you may do with Yii2's query builder:

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);
Nick
  • 9,735
  • 7
  • 59
  • 89
  • 1
    Really?????? (my incredulity is not aimed at you, but the fact that this is the solution). This seems to be an incredible amount of work for what must be a very common requirement. – KevInSol Jan 27 '16 at 17:15
  • Yes, it is not very handy, but you may use one of query builders, what solves this and similar tasks – Nick Jan 27 '16 at 17:20
  • 1
    @KevInSol One paramater equals to one binding value. You cannot just tell the database server you have one parameter and send 3 values and expect the database server to know you meant this one parameter is for all 3 values. – Charlotte Dunois Jan 27 '16 at 17:22
  • Thank you, I saw your edit there. I'd rather not have to go learn something else or add in extra libraries etc. I'll have a go at my own function. Will post it here is I get something working. – KevInSol Jan 27 '16 at 17:24