How to write multi query in PHP using database extension PDO

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

you have a typo in` "SELECT table.* FROM tableHAVING (@val := @val + 1) > 0;";` you should add a space between `tableHaving` ...`table Having` — ScaisEdge, Aug 18 '18 at 06:22
@scaisEdge OH YEA, sorry that was a typo error when i was writing the post but i've updated it now .. thank you very much — Reham Fahmy, Aug 18 '18 at 06:36

score 2 · Accepted Answer · answered Aug 18 '18 at 08:00

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

that was my question is about, thank you a lot .. you've given me a flash light for the first time knowing about nextRowset(). — Reham Fahmy, Aug 18 '18 at 08:32

score 1 · Answer 2 · answered Aug 18 '18 at 06:27

1

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;";

answered Aug 18 '18 at 06:27

ScaisEdge

131,976
10
91
107

thank you and i've upaded the question and added the error that appears to me when using that way. – Reham Fahmy Aug 18 '18 at 06:46

score 1 · Answer 3 · answered Aug 18 '18 at 06:50

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>

thank you for caring about term of `security comes first` but actually I'm someone beleives in conspiracy theory so i'm using different layers of security on real working code, but this one was just an model for the core problem which is differences than mysqli_multi_query() as you might write all queries as set then execute all of them once... but anyway i might stick with your answer as it seems to be the only way .. thank you again for your clear and straight forward answer — Reham Fahmy, Aug 18 '18 at 07:11

score 1 · Answer 4 · answered Aug 18 '18 at 07:06

1

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;

answered Aug 18 '18 at 07:06

Pawnesh Kumar

484
2
10

How to write multi query in PHP using database extension PDO

4 Answers4

Linked