如何使用带有IN子句的PDO预处理语句？

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?

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.