How to use PDO with IN clause array with PHP?

Spread the love

Sometimes, we want to use PDO with IN clause array with PHP.

In this article, we’ll look at how to use PDO with IN clause array with PHP.

How to use PDO with IN clause array with PHP?

To use PDO with IN clause array with PHP, we can interpolate a string into the prepared statement.

For instance, we write

$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; 
        
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; 
    $in_params[$key] = $item;
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

to create the $params array with the items we want to interpolate into the prepared statement.

Then we create the $in string with the values we want to go into between the parentheses after IN.

Next, we create the $sql string with $in interpolated into the SQL string.

And then we call $db->prepare to prepare the $sql statement.

We call execuite with $params and $in_params to interpolate both values into the prepared statement.

And then we call fetchAll to fetch all the results.

Conclusion

To use PDO with IN clause array with PHP, we can interpolate a string into the prepared statement.

Leave a Reply

Your email address will not be published. Required fields are marked *