PDO将数组绑定到Where IN [英] PDO bind array to Where IN
问题描述
我想将字符串数组绑定到SQL命令的WHERE IN
部分,然后在SQL Server上运行该命令.问题可能是我尝试绑定字符串数组而不是整数数组.
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>";
}
我为您回应了$ refIdsPartial,所以您知道这是什么:
54469c27c687b332339627,54469ba0dec3e703865612,54469c77945c7091266617
它只是字符串/varchars的内爆数组. 我已经使用Managementstudio测试了SQL命令,并且可以确保此SQL命令有效,只要我对每个String/Varchar使用引号即可.示例:
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
我的问题:
在上面的代码中,它永远不会进入foreach,因此查询的结果似乎为空.那里出了什么问题(当然,我只测试了应该有结果的查询)?
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)?
推荐答案
您可以使用一些字符串操作.
You could use some string manipulation.
您可以使用str_repeat("?", count(explode(",", $refIdsPartial)))
来count
您需要的?
数.这将创建您的占位符.
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) );
这篇关于PDO将数组绑定到Where IN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!