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
– ScaisEdge, Commented Aug 18, 2018 at 6: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
– Reham Fahmy, Commented Aug 18, 2018 at 6:36

Nick · Accepted Answer · 2018-08-18 08:00:24Z

2

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

answered Aug 18, 2018 at 8:00

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reham Fahmy Over a year ago

that was my question is about, thank you a lot .. you've given me a flash light for the first time knowing about nextRowset().

ScaisEdge · Accepted Answer · 2018-08-18 06:27:04Z

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, 2018 at 6:27

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

1 Comment

Reham Fahmy Over a year ago

thank you and i've upaded the question and added the error that appears to me when using that way.

chinazaike · Accepted Answer · 2018-08-18 06:50:38Z

1

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>

answered Aug 18, 2018 at 6:50

chinazaike

5177 silver badges21 bronze badges

1 Comment

Reham Fahmy Over a year ago

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

Pawnesh Kumar · Accepted Answer · 2018-08-18 07:06:34Z

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, 2018 at 7:06

Pawnesh Kumar

4942 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to write multi query in PHP using database extension PDO

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related