1

Trying to a run a simple select query with WHERE IN on a couple ID's. 

$idArray = array(492, 493, 494, 495);
$csvList = implode(",", $idArray);

DB::select("SELECT id, name FROM table WHERE id IN (?)", array($csvList));

That's what I'm running, no errors. The problem is, my query outputs $csvList as a string with quotes. '492, 493, 494, 495' which MySQL does not interpret properly. I only get 1 result back for 492 instead of all 4 results. If I remove the quotes from the query, it works fine.

This is the full query that runs with the page: 

SELECT id, name FROM table WHERE id IN ('491,493,494,495');

I need to get rid of the quotes, anyone know how I can do that? Or what I'm doing wrong?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Envious
  • 478
  • 1
  • 8
  • 28
  • 2
    Placeholders can only bind strings/numbers, not lists. You need to expand your IN list into `?,?,?,?` and pass all params. – mario Sep 21 '15 at 21:30
  • If you are using Laravel what's the point of using raw SQL? Just use Eloquent – ntzm Sep 21 '15 at 21:31
  • @mario That isn't ideal because the array is dynamically generated from a foreach. I won't always have 4 id's. – Envious Sep 21 '15 at 21:31
  • See also: [Laravel 4 db raw query with IN in the WHERE clause doesn't work with parameter with MySql](http://stackoverflow.com/q/22387487) or [Can I bind an array to an IN() condition?](http://stackoverflow.com/q/920353) – mario Sep 21 '15 at 21:33
  • @natzim I don't have my models set up. Also, the actual query has 3 joins in it and is pretty confusing to get working with eloquent. I've always done raw sql for selects and it's been fine up until now. – Envious Sep 21 '15 at 21:33

1 Answers1

0

You have two options.

Either use Laravel's query builder:

$ids = [492, 493, 494, 495];

$results = DB::table('table')->select('id', 'name')->whereIn('id', $ids)->get();

Or create an array of question marks, and implode them:

$ids = [492, 493, 494, 495];

$placeholders = implode(', ', array_fill(0, count($ids), '?'));

$results = DB::select("SELECT id, name FROM table WHERE id IN ({$placeholders})", $ids)->get();
Joseph Silber
  • 214,931
  • 59
  • 362
  • 292
  • Why not explain first why you reopened the question only to duplicate the answers from there? – mario Sep 21 '15 at 23:12
  • @mario - 1. That's not a reason to downvote an answer. 2. You should have given me the benefit of the doubt. I'm in same rep level as you. I don't really need the few points I could get here. 3. None of those links is an actual duplicate. One of them doesn't have an accepted answer, while the other one doesn't explain it in a Laravel context. – Joseph Silber Sep 21 '15 at 23:15
  • Actually, the one that doesn't have an accepted answer has a dangerous answer! – Joseph Silber Sep 21 '15 at 23:16
  • 1. That's not a sufficient reason to reopen stuff. You could have posted your answer there if you wanted to improve on those. 2. "Not an accepted answer" also doesn't make it less of a duplicate. 3. And this topic is covered sufficiently by now, even for those insisting on the more tedious Laravel way. – mario Sep 21 '15 at 23:18
  • I thought of posting the answer there, but that query involves a join, which would make the answer more complicated. So I don't consider that a duplicate. There might well be other questions that are duplicates of this one, but none were provided. – Joseph Silber Sep 21 '15 at 23:26