PDO bind array to Where IN

Question

I want to bind a array of Strings to the WHERE IN part of a SQL command, which I want to run afterwards on a SQL Server. The problem is probably that I try to bind an array of Strings and not an array of integers.

$totalCount = 
"SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE referral IN ($refIdsPartial) GROUP BY referral";

$ps_totalCounts = $dbh->prepare($totalCount);
$ps_totalCounts->execute();

//loop over total counts
foreach($ps_totalCounts as $row){
    echo "Test<br>";
}

I echoed $refIdsPartial for you, so you have an idea what this is:

54469c27c687b332339627,54469ba0dec3e703865612,54469c77945c7091266617

Its just an imploded array of strings/varchars. I tested the SQL command with my Managementstudio and I can ensure that this SQL command works, as long das I use the quote signs for each String/Varchar. Example:

SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE referral IN ('54469c27c687b332339627','54469ba0dec3e703865612') GROUP BY referral

My problem:

In the code above it never goes into the foreach, so the result of the Query seems to be empty. What is wrong there (Ofcourse I tested only queries which should have results)?

ʰᵈˑ · Accepted Answer · 2014-11-10T14:30:20.963

2

You could use some string manipulation.

You can count the number of ? you'd need by using str_repeat("?", count(explode(",", $refIdsPartial))). This will create your placeholders.

$totalCount = 
"SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE referral IN (". str_repeat("?,", count(explode(",", $refIdsPartial))-1) . "?) GROUP BY referral";

Now that the placeholders are in place, you can explode the , from the string and execute

$ps_totalCounts->execute( explode(",", $refIdsPartial) );

edited Nov 10 '14 at 14:30

answered Nov 10 '14 at 14:18

ʰᵈˑ

11,279
3
26
49

but what happens if you use value bind instead of " ? " and when you have more than 1 type of fields at the same time..? – Miguel Sep 06 '16 at 15:09

The Humble Rat · Answer 2 · 2014-11-10T14:25:11.793

1

Here is the snippet I use when trying to achieve an IN statement with an array.

This works dynamically, so whether you have an array of 2 or 200 it should execute as expected.

$ids = array(1,2,3);
$in  = str_repeat('?,', count($ids) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

Your code will look like so:

$refIdsPartial = array('54469c27c687b332339627','54469ba0dec3e703865612','54469c77945c7091266617');
$in  = str_repeat('?,', count($refIdsPartial ) - 1) . '?';
$totalCount = "SELECT referral, COUNT(username) AS cnt FROM accounts WHERE referral IN ($in) GROUP BY referral";
$ps_totalCounts = $dbh->prepare($totalCount);
$ps_totalCounts->execute();

//loop over total counts
foreach($ps_totalCounts as $row)
{
    echo "Test<br>";
}

edited Nov 10 '14 at 14:25

answered Nov 10 '14 at 14:18

The Humble Rat

4,586
6
39
73

Will this work for Strings/Varchars as well ?? And I guess $arr should be $ids ? – kentor Nov 10 '14 at 14:22
@kentor This should work for all data types regardless. – The Humble Rat Nov 10 '14 at 14:23
Hmm I still don't have results from my query. Not sure how I can find out whats the problem. Is there a way to see the whole query which gets executed afterwards, so I can test it on my own ? – kentor Nov 10 '14 at 14:29
@kentor One way would be to enable to mysql query log, this will show you all executed queries: http://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log Have you tried manually changing the mysql line ie like so: `"SELECT referral, COUNT(username) AS cnt FROM accounts WHERE referral IN ('54469c27c687b332339627','54469ba0dec3e703865612','54469c77945c7091266617') GROUP BY referral"` – The Humble Rat Nov 10 '14 at 14:32

score 1 · Answer 3 · answered Sep 20 '17 at 06:53

I faced similar problem with quite a big array to bind. Instead of skipping binding and injecting whole array directly to query or making workarounds with dynamically generating multiple unique placeholders to bind each record of array, I went for using find_in_set mysql function. Read more here.

In your case it would be:

$totalCount = 
"SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE find_in_set(referral,$refIdsPartial) GROUP BY referral";

PDO bind array to Where IN

3 Answers3

Linked