kentor kentor - 2 months ago 16
PHP Question

PDO bind array to Where IN

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)?

Answer

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) );
Comments