如何使用数据库扩展PDO在PHP中编写多个查询

Question

I would like to write this MYSQL query

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

inside PHP code that using database extention PDO!

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

I've try this

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

But did not works!

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

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

Answer 1

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

to:

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