-1

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;
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Dave
  • 84
  • 9
  • 1
    This might be helpful http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition – FoolishSeth Mar 27 '13 at 04:59
  • I am curious, did you ever try to echo out the result of your program? Program that creates a query I mean. Is it any different from the handwritten one? if so - what's the difference? – Your Common Sense Mar 27 '13 at 05:05
  • @FoolishSeth: Thank you for the heads up. I changed my query from ANY to IN, and it works. – Dave Mar 27 '13 at 05:16
  • @Your Common Sense: I did echo the queries after creating the parameter placeholders. I have not figured out how to get the query after inserting the parameter values to be able to see the completed query string. – Dave Mar 27 '13 at 05:18
  • @FoolishSeth: I need to correct my above comment. It returns the first id encountered. So out of the three (1, 2, 3), only the record for the first is returned. I tried both binding the parameters, and then just $execute($values), and both returned the same thing (1 record). Again, thanks for pointing me to something, it was a partial fix. – Dave Mar 27 '13 at 22:38

2 Answers2

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;
MatheusOl
  • 10,870
  • 3
  • 30
  • 28
  • Thank you for the example. I had attempted to use the IN clause last night before showing my below down-voted solution. The ANY clause returns 0 results, the IN clause returns one result (the first encountered). The only way so far I have found to get all results is my currently down-voted solution. I'll have to RTFM on pgAdmin tonight and figure out how to access the log to see what values are hitting the server. Thanks for taking a look at the issue. – Dave Mar 27 '13 at 22:34
  • @Dave, that's because you need to iterate over the results. I edited my answer to show how (the loop). – MatheusOl Mar 28 '13 at 15:27
-2

@FoolishSeth: Thank you for pointing me to the answer. I was unable to understand what ws being discussed at first due to formatting of code. I was able to make it work, but not using ANY (no result) or IN (only the first result).

The execute and fetch were moved into the loop, and each item from the $values array is queried individually and added to a $results array.

$values = array(1,2,3);

$sql = "SELECT column FROM table WHERE id = ?";

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

$result = array();

foreach($values as $key => $val)
{
    $sth->bindValue(1, $val, PDO::PARAM_INT);
    $sth->execute();
    $result[$key] = $sth->fetch(PDO::FETCH_NUM);
}

echo var_dump($result);

The above returned the three rows as desired. I did not have an opportunity to test this against large $values[], but for the small amount of records intended this should work.

Dave
  • 84
  • 9