如何在另一个查询的while循环中执行第二个PDO mysql查询? [英] How to do a second PDO mysql query in a while loop from another query?

问题描述

所以我在使用PDO MySQL时遇到的一个障碍是，如果我像这样运行查询:

So a snag I am hitting in using PDO MySQL is that if i run a query like so:

$db->pquery("SELECT `category_id`, `category_name` FROM `database_categorys` ORDER BY `category_name` ASC");
while ($category = $db->fetch())
{
}

在while循环中，我无法执行其他查询，否则它将取消上一个查询，是否有解决方法?

Inside the while loop i cannot do another query or it will cancel out the previous query, is there a way to get around that?

这是我的查询btw:

// A plain query
public function pquery($sql)
{
    $this->STH = $this->database->prepare($sql);  

    $this->counter++;

    return $this->STH->execute();
}

还有我的提取函数:

public function fetch()
{
    $this->STH->setFetchMode(PDO::FETCH_ASSOC); 
    return $this->STH->fetch();
}

推荐答案

这不是PDO限制，它是MySQL客户端库的限制. MySQL一次仅支持一个正在进行的查询.当第一个查询仍然具有打开的游标(即仍然有要返回的结果)时，您将无法执行另一个查询.

This isn't a PDO limitation, it's a limitation of the MySQL client library. MySQL supports only one query in progress at a time. You can't execute another query while the first query still has an open cursor (i.e. it still has results to return).

您有以下选择:

• 使用 PDOStatement :: fetchAll()并收集PHP数组中外部查询的整个结果集.这样就完成了外部查询的查询结果.然后，您可以遍历数组并为每次循环迭代运行一个附加的SQL查询.

• Use PDOStatement::fetchAll() and collect the whole result set of the outer query in a PHP array. This finishes the query result of the outer query. Then you can loop over the array and run an additional SQL query for each loop iteration.

但是对于外部结果集的每个循环迭代运行新查询效率不高.这是杀死应用程序性能的好方法.

But running a new query for every loop iteration of the outer result set is not efficient. It's a good way to kill the performance of your application.

有人将其称为 N + 1选择问题，因为您在跑步第一个选择，它返回N行，然后您根据第一个选择的结果运行N个选择.

Some people call this the N+1 Selects Problem because you run the first select, which returns N rows, and then you run N selects based on the results of the first select.

如果使用MySQL，请使用 PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY基本上执行相同操作的会下载内部保存在数组中的所有行.然后，随后对fetch()的调用仅对缓冲的结果进行迭代.

If you use MySQL, use PDO::MYSQL_ATTR_USE_BUFFERED_QUERY which basically does the same thing, downloads all the rows, saved in an array internally. Then subsequent calls to fetch() just iterate over the buffered results.

但这还涉及N + 1个选择反模式.

But this also involves the N+1 Selects antipattern.

最好编写一个SQL查询来获取所需的值.从您的评论中猜测，您需要类别和来自category_id匹配的另一个表中的相关行数.这是一个这样的SQL查询的示例:

It's better to write a single SQL query that gets you the values you want. Guessing from your comments, you want categories and the count of related rows from another table where category_id matches. Here's an example of such an SQL query:

$db->pquery("SELECT c.`category_id`, c.`category_name`, COUNT(*) AS `count`
FROM `database_categorys` AS c 
LEFT OUTER JOIN `other_table` AS t ON t.category_id = c.category_id
GROUP BY c.category_id
ORDER BY c.`category_name` ASC");

联接是SQL的基本部分.如果您在不学习使用联接的情况下尝试使用SQL，这就像在不学习使用while循环的情况下使用PHP.

Joins are a fundamental part of SQL. If you try to use SQL without learning to use joins, this is like using PHP without learning to use while loops.

从此处开始:视觉对象SQL联接的说明.

这篇关于如何在另一个查询的while循环中执行第二个PDO mysql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！