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

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

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

should work multiquery is supported by PDO so you should check for typo in your code eg: add space beetween clause (table HAVING and not tableHAVING ) and use backticks for resevred word eg:

$sql = "SET @val := 0;";
$sql .= "SELECT `table`.* FROM `table` HAVING (@val := @val + 1) > 0;";

First your script is vulnerable to XSS Attack. I do not see where you are escaping the outputted variables with htmlentities or htmlspecialchars() functions.

This code will do the trick for you. I have tested it and it works.

create a table name called table1 with following field id and name

create table table1(id int primary key auto_increment, name varchar(100));

insert values into it and use the code below but remember to change database connection propertise

 <?php

    //connect to db



    $db = new PDO (
        'mysql:host=localhost;dbname=test_db;charset=utf8', 
        'root', // username

        '' // password
    );


    $result = $db->prepare("SET @val := 0");
    $result->execute(array());



    $result = $db->prepare("SELECT `table1`.* FROM `table1` HAVING (@val := @val + 1) > 0");
    $result->execute(array());


    while ($row = $result->fetch()) 
    {

    //prevent xss Attack using htmlentities
    $id = htmlentities($row['id'], ENT_QUOTES, "UTF-8");
    $name = htmlentities($row['name'], ENT_QUOTES, "UTF-8");

    //}



    ?>

    <table>
        <tr>
            <th>id</th>
            <th>name</th>
        </tr>
        <tr>
            <td><?php echo $id; ?></td>
            <td><?php echo $name; ?></td>
        </tr>
        <?php } ?>
    </table>

Look at this question on stack overflow PHP & MySQL: How can I use "SET @rank=0;" in $query=

or try this

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