问题描述
我们假设在一个场景中： $ ids = 2,3 。但由于某种原因，记录被返回，如： $ ids = 2 。
我相信有一些问题在这行代码中，因为当我回显 $ ids 时，它会返回 2,3 查询返回，就像只有一个id。也就是说它只返回来自一个课程的事件（id 2）。
$ statement-> execute（array（ids= > $ ids，'timestart'=> $ timestart，'timeend'=> $ timeend）））{
我的完整代码：
$ ids = array_map（function（$ item）{return $ item-> id;}，$ entitlementsVOs）;
$ ids = implode（'，'，$ ids）; // echo shows 2,3
$ timart = isset（$ _ GET ['start']）&& $ _GET ['start']！=？ $ _GET ['start']：null;
$ timeend = isset（$ _ GET ['end']）&& $ _GET ['end']！=？ $ _GET ['end']：null;
$语句= $ this-> connection-> prepare（SELECT name AS title，timestart AS start，timestart + timeduration AS end FROM event WHERE courseid IN（：id）AND timestart> =：timestart AND timestart + timeduration< =：timeend）;
$ statement-> setFetchMode（\ PDO :: FETCH_CLASS，get_class（new EventVO（）））;
if（$ statement-> execute（array（ids=> $ ids，'timestart'=> $ timestart，'timeend'=> $ timeend） ））{
return $ statement-> fetchAll（）;
} else {
return null;
}
p.s。手动运行这个查询在mysql workbench返回我的两个记录（1从课程id 2和其他从课程3），其中我用（2,3）替换运算符，但在php执行我只得到一个记录。
如果我硬编码php courseid IN（2,3）中的值，例如：
$ statement = $ this-> connection-> prepare（SELECT name AS title，timestart AS start，timestart + timeduration AS end FROM event WHERE courseid IN（2,3）AND timestart> =：timestart AND timestart + timeduration< =：timeend）;
我得到了我的期望，所以我相信有一些问题 implode 或 IN 运算符或 $ statement-> execute 。
编辑：
我已经阅读过这篇文章，从命名占位符开始，完成同样的。我的问题是当我有两个命名参数和IN运算符，dupe使用IN运算符只与位置占位符。
编辑2 p>
我读过第二个dupe，它是不相关的，我的问题使用了IN和命名占位符的混合，链接的问题不解决，但是，
这应该适用于你：
正如已经在评论中所说的，你需要一个占位符来绑定到IN子句中的每个值。
这里我首先创建数组 $ ids 其中只保留纯ids，例如
3]
然后我也创建了数组 $ preparedIds 它将占位符作为数组，然后稍后在预准备语句中使用它。此数组看起来像这样：
[：id2，：id3]
我还创建了一个名为 $ preparedValues 的数组， c> $ preparedIds 作为键和 $ ids 作为值，然后可以用于 execute / code>调用。数组看起来像这样：
[：id2=> 2，：id3=> 3]
之后，在准备语句中，我只是 implode（） $ preparedIds 数组，使SQL语句看起来像这样：
... IN（：id2，：id3）...
然后你可以简单地 execute（）查询。我只是 array_merge（）你的 $ preparedValues 数组与其他占位符数组。
<？php
$ ids = array_map（function（$ item）{
return $ item-> id ;
}，$ entitlementsVOs）;
$ preparedIds = array_map（function（$ v）{
return：id $ v;
}，$ ids）;
$ preparedValues = array_combine（$ preparedIds，$ ids）;
$ timart =（！empty（$ _ GET ['start']）？$ _GET ['start']：NULL）;
$ timeend =（！empty（$ _ GET ['end']）？$ _GET ['end']：NULL）;
$语句= $ this-> connection-> prepare（SELECT name AS title，timestart AS start，timestart + timeduration AS end FROM event WHERE courseid IN（。implode （，，$ preparedIds））AND timestart> =：timestart AND timestart + timeduration< =：timeend
$ statement-> setFetchMode（\ PDO :: FETCH_CLASS，get_class（new EventVO（）））;
if（$ statement-> execute（array_merge（$ preparedValues，[timart=> $ timestart，timeend=> $ timeend]）））{
return $ statement-> fetchAll（）;
} else {
return null;
}
？>
此外，我想你想在查询中加一个if语句，因为你的查询不会运行if $ timestart 和 $ timeend 的值为NULL。
Let's say in a scenario: $ids = 2,3. But for some reason the records are getting returned as if: $ids = 2.
I believe there's some problem in this line from the complete code, because when I echo $ids, it returns 2,3, but the actual query returns as if there's only one id. I.e. it returns events from one course only (of id 2).
$statement->execute(array("ids" => $ids, 'timestart' => $timestart, 'timeend' => $timeend))) {
my full code:
$ids = array_map(function($item) { return $item->id; }, $entitlementsVOs);
$ids = implode(', ', $ids); //echo shows 2,3
$timestart = isset($_GET['start']) && $_GET['start'] != "" ? $_GET['start'] : null;
$timeend = isset($_GET['end']) && $_GET['end'] != "" ? $_GET['end'] : null;
$statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(:ids) AND timestart >= :timestart AND timestart + timeduration <= :timeend");
$statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));
if($statement->execute(array("ids" => $ids, 'timestart' => $timestart, 'timeend' => $timeend))) {
return $statement->fetchAll();
} else {
return null;
}
p.s. manually running this query in mysql workbench returns my two records (1 from course id 2 and the other from course 3) where I substitute in operator with (2,3), but in php execution I get only one record back.
If I hard code the values in php courseid IN(2,3), e.g.:
$statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(2,3) AND timestart >= :timestart AND timestart + timeduration <= :timeend");
I get what I'm expecting, so I believe there's some problem with either implode, or IN operator or $statement->execute.
Edit:
I've read the dupe, but I don't know where to start with the named placeholders to accomplish the same. My question is when I've both named parameters and IN operator, the dupe uses IN operator only with position placeholders.
Edit 2
I've read the second dupe, it's not related, my question uses a mix of both IN and named placeholders, the linked question doesn't address that, however, I've got the solution now in the answer.
This should work for you:
So as already said in the comments you need a placeholder for each value which you want to bind into the IN clause.
Here I create first the array $ids which only holds the plain ids, e.g.
[2, 3]
Then I also created the array $preparedIds which holds the placeholders as array, which you then later use in the prepared statement. This array looks something like this:
[":id2", ":id3"]
And I also create an array called $preparedValues which holds the $preparedIds as keys and $ids as values, which you then later can use for the execute() call. The array look something like this:
[":id2" => 2, ":id3" => 3]
After this you are good to go. In the prepared statement I just implode() the $preparedIds array, so that the SQL statement look something like this:
... IN(:id2,:id3) ...
And then you can simply execute() your query. There I just array_merge() your $preparedValues array with the other placeholders array.
<?php
$ids = array_map(function($item){
return $item->id;
}, $entitlementsVOs);
$preparedIds = array_map(function($v){
return ":id$v";
}, $ids);
$preparedValues = array_combine($preparedIds, $ids);
$timestart = (!empty($_GET['start']) ? $_GET['start'] : NULL );
$timeend = (!empty($_GET['end']) ? $_GET['end'] : NULL );
$statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(" . implode(",", $preparedIds) . ") AND timestart >= :timestart AND timestart + timeduration <= :timeend");
$statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));
if($statement->execute(array_merge($preparedValues, ["timestart" => $timestart, "timeend" => $timeend]))) {
return $statement->fetchAll();
} else {
return null;
}
?>
Also I think you want to put an if statement around your query, since your query will not run if the values of $timestart and $timeend are NULL.
这篇关于具有命名占位符的PDO预准备语句IN子句无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！