How to use PDO prepared statements with IN clause?

Question

I stored some data in a field inside MySQL in this format: 1,5,9,4
I named this field related. Now I want to use this field inside an IN clause with PDO. I stored that field contents in $related variabe. This is my next codes:

$sql = "SELECT id,title,pic1 FROM tbl_products WHERE id IN (?) LIMIT 4";
$q = $db->prepare($sql);
$q->execute(array($related));
echo $q->rowCount();

But after executing this code, I can fetch only one record whereas I have to fetch 4 records (1,5,9,4). What did I do wrong?

The common path is to use an extra table for the relationship. This is bad database design. — Gerald Schneider, Jun 15 '16 at 06:11
@GeraldSchneider I know that, this is just a sample for my question. — Mohammad Saberi, Jun 15 '16 at 06:15
Check this link http://stackoverflow.com/questions/2373562/pdo-with-where-in-queries — Vinie, Jun 15 '16 at 06:19

Clint · Answer 1 · 2022-05-15T01:13:40.003

1

using named place holders

$values = array(":val1"=>"value1", ":val2"=>"value2", ":val2"=>"value3");
$statement = 'SELECT * FROM <table> WHERE `column` in(:'.implode(', :',array_keys($values)).')';

using ??

$values = array("value1", "value2", "value3");
$statement = 'SELECT * FROM <table> WHERE `column` in('.trim(str_repeat(', ?', count($values)), ', ').')';

edited May 15 '22 at 01:13

answered Sep 14 '19 at 10:54

Clint

973
7
18

It might have been downvoted because `table` is a reserved keyword, or a duplicate question, or [an answer copied in two places on Stack Overflow](https://stackoverflow.com/a/70082897/2943403). – mickmackusa Apr 02 '22 at 14:05

Sharky · Answer 2 · 2016-06-15T06:39:31.273

You need as many ? placeholders as your "IN" values.

So:

$related = array(1,2,3); // your "IN" values

$sql = "SELECT id,title,pic1 FROM tbl_products WHERE id IN (";

$questionmarks = "";
for($i=0;$i<count($related);$i++)
{
    $questionmarks .= "?,";
}

$sql .= trim($questionmarks,",");
$sql .= ") LIMIT 3;";

// echo $sql; // outputs: SELECT id,title,pic1 FROM tbl_products WHERE id IN (?,?,?) LIMIT 3;

$q = $db->prepare($sql);
$q->execute($related); // edited this line no need to array($related), since $related is already an array
echo $q->rowCount();

https://3v4l.org/No4h1

(also if you want 4 records returned get rid of the LIMIT 3)

More elegantly you can use str_repeat to append your placeholders like this:

$related = array(1,2,3); // your "IN" values

$sql = "SELECT id,title,pic1 FROM tbl_products WHERE id IN (";
$sql .= trim(str_repeat("?,",count($related)),",");
$sql .= ") LIMIT 3;";

// echo $sql; // outputs: SELECT id,title,pic1 FROM tbl_products WHERE id IN (?,?,?) LIMIT 3;

$q = $db->prepare($sql);
$q->execute($related); // edited this line no need to array($related), since $related is already an array
echo $q->rowCount();

https://3v4l.org/qot2k

Also, by reading again your question i can guess that your $related variable is just a string with value comma-separated numbers like 1,40,6,99. If that's the case you need to make it an array. do: $related = explode($related,","); to make it an array of numbers. Then in your execute method pass $related as-is.

How to use PDO prepared statements with IN clause?

2 Answers2

Linked