如何使用数据库扩展 PDO 在 PHP 中编写多查询 [英] How to write multi query in PHP using database extension PDO

问题描述

我想写这个 MYSQL 查询

I would like to write this MYSQL query

SET @val := 0;
SELECT `my_table`.* FROM `my_table` HAVING (@val := @val + 1) > 0;

在PHP代码中使用了数据库扩展PDO！

inside PHP code that using database extention PDO!

我已经看到 mysqli 有 mysqli_multi_query() 但我不知道 PDO 是否支持多查询?！

I've seen that mysqli have mysqli_multi_query() but i do not know does PDO support multi queries by some how?!

我试过了

<?PHP
// i'm connected to db

    $sql = "SET @val := 0;";
    $sql .= "SELECT `my_table`.* FROM `my_table` HAVING (@val := @val + 1) > 0;";
?>
<table>
    <tr>
        <th>id</th>
        <th>name</th>
    </tr>
    <?PHP foreach($db->query($sql) as $row){ ?>
    <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['name']; ?></td>
    </tr>
    <?php } ?>
</table>

但是没有用！

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error in \path\test.php:25 Stack trace: #0 {main} thrown in \path\test.php on line 25

和第 25 行是: <?PHP foreach($db->query($sql) as $row){ ?> 看起来它没有通过查询！所以任何想法

and line 25 is : <?PHP foreach($db->query($sql) as $row){ ?> looks like it not passing the query! so any idea

推荐答案

查询工作正常.您的问题是 $db->query($sql) 返回了两个结果集:第一个用于 SET @val := 0，第二个用于您实际的 SELECT 查询.当您循环 $db->query() 的结果时，您将获得第一个结果集，即来自 SET @val := 0 的结果，这会导致碰撞.您需要做的是切换到下一个结果集，这将需要稍微重写您的代码.更改:

The query is working fine. Your issue is that there are two result sets returned by $db->query($sql): the first for the SET @val := 0 and the second for your actual SELECT query. When you loop on the result of $db->query() you are getting the first result set i.e. the results from SET @val := 0 which is causing a crash. What you need to do is switch to the next result set, which will require rewriting your code slightly. Change:

<?PHP foreach($db->query($sql) as $row){ ?>

到:

<?php
$result = $db->query($sql);
$result->nextRowset();
while ($row = $result->fetch()) {
?>

这篇关于如何使用数据库扩展 PDO 在 PHP 中编写多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！