0

I have a comma separated array of ids stored in a string that looks like this :

$array_string = "4,6,15,94,122,145";

I'm trying to query the database to return results based on these ids but I'm only getting results for the first id in the string, the rest are being ignored.

$sql = 'SELECT * FROM table WHERE user_id IN (:array) ORDER BY last_updated DESC';

$db = static::getDB();
$stmt = $db->prepare($sql);
$stmt->bindParam(':array', $array_string, PDO::PARAM_STR);
$stmt->execute(); 

return $stmt->fetchAll(PDO::FETCH_OBJ);

This will only return results for id 4 and none of the others. What am I doing wrong?

spice
  • 1,442
  • 19
  • 35
  • Thanks, that post had the answer I needed which was to use `FIND_IN_SET()` – spice May 27 '18 at 12:13
  • 1
    `FIND_IN_SET()` can't use an index and will be slow on big tables. What you need is to create the query dynamically `.. IN (?,?,?,?,?,?) ..` and `$stmt->execute([4,6,15,94,122,145]);`. – Paul Spiegel May 27 '18 at 12:17
  • Ahh okay, was unaware of that. How would I write that query when I will never know the amount of numbers that will be in the array though? `IN (?,?,?,?,?,?)` It is being generated dynamically and will be different each time it is called. – spice May 27 '18 at 12:19
  • Looking at your reply again I guess I would have to create the array string at the same time that I'm writing this query and do a `foreach` right? – spice May 27 '18 at 12:21
  • 2
    Read the second answer: https://stackoverflow.com/a/10722827/5563083 – Paul Spiegel May 27 '18 at 12:24
  • Just read through it properly, the second answer will do the job great but the solution provided by @prograhammer on that page seems perfect for what I need here. Thanks again. – spice May 27 '18 at 12:27
  • 1
    I wouldn't use prograhammer's solution. One little mistake will make the code hard to debug due to *pass-by-reference* (`&$bindArray`). And it doesn't even make the code shorter or simpler (compared to Donamite's 3-liner). – Paul Spiegel May 27 '18 at 13:07
  • You're absolutely correct, found that out the hard way. Ended up using the solution you pointed to originally. – spice May 28 '18 at 15:05

0 Answers0