Pass Variable Number of Parameters to Postgresql

Question

How does one pass a random number of parameters to a query? The below code blocks show what was attempted. All of the failures displayed bool(false). For testing, three integers are used to represent ids.

The query from item 1 was manually executed in pgAdminIII, and was successful. The query from item 2 and 4 was echo'd and compared. They look close as intended, but the parameters are not binding.

SELECT column FROM table WHERE id = ANY('{1,2,3}'::int[]);
SELECT column FROM table WHERE id = ANY('{?,?,?}'::int[])

1) This works. The desire is that the any clause will be 1-N numbers:

$sql = "SELECT column FROM table WHERE id = ANY('{1,2,3}'::int[])";

$sth = $dbh->prepare($sql);
$sth->execute();

$result = $sth->fetch(PDO::FETCH_NUM);

echo var_dump($result);

$dbh = null;

2) This fails:

$values = array(1,2,3);
$placeHolders = implode(',', array_fill(0, count($values), '?'));

$sql = sprintf("SELECT column FROM table WHERE id = ANY('{%s}'::int[])", $placeHolders);

$sth = $dbh->prepare($sql);
$sth->execute($values);
$result = $sth->fetch();

echo var_dump($result);

$dbh = null;

3) This fails:

$values = array(':qwer' => 1, ':asdf' => 2, ':zxcv' => 3);

$sql = "SELECT colum FROM table WHERE id = ANY(\'{ :qwer , :asdf , :zxcv }\'::int[])";  //Below error caused if single quote not escaped.

$sth = $dbh->prepare($sql);
$sth->execute($values); // Invalid parameter number: :qwer if single quotes not escaped.
$result = $sth->fetch();

echo var_dump($result);

$dbh = null;

4) This fails (explicitly binding the parameters):

$values = array(1,2,3);
$placeHolders = implode(',', array_fill(0, count($values), '?'));

$sql = sprintf("SELECT query FROM nc_reports WHERE id = ANY('{%s}'::int[])", $placeHolders);

$sth = $dbh->prepare($sql);

$i = 1;
foreach($values as $val)
{
    //$sth->bindParam($i, $val, PDO::PARAM_INT);
    $sth->bindValue($i, $val, PDO::PARAM_INT);
    $i++;
}
$sth->execute();
$result = $sth->fetch();

echo var_dump($result);

$dbh = null;

Answer 1

Try to generate the query yourself:

$values = array(1,2,3);
// Generate the query
$params = substr(str_repeat("?,", sizeof($values)), 0, -1);

$sql = "SELECT query FROM nc_reports WHERE id IN ($params)";

$sth = $dbh->prepare($sql);

for ($i = 0; $i < sizeof($values); $i++)
{
    $sth->bindValue($i+1, $values[$i], PDO::PARAM_INT);
}
$sth->execute();
// Iterate over the results
while (($result = $sth->fetch()) !== FALSE) {
    echo var_dump($result);
}
$dbh = null;