PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

user_368691 0
Viewed 135059

I do know that PDO does not support multiple queries getting executed in one statement. I've been Googleing and found few posts talking about PDO_MYSQL and PDO_MYSQLND.

PDO_MySQL is a more dangerous application than any other traditional MySQL applications. Traditional MySQL allows only a single SQL query. In PDO_MySQL there is no such limitation, but you risk to be injected with multiple queries.

From: Protection against SQL Injection using PDO and Zend Framework (June 2010; by Julian)

It seems like PDO_MYSQL and PDO_MYSQLND do provide support for multiple queries, but I am not able to find more information about them. Were these projects discontinued? Is there any way now to run multiple queries using PDO.

pdo mysql php
7 Answers

Like thousands of people, I'm looking for this question:
Can run multiple queries simultaneously, and if there was one error, none would run I went to this page everywhere
But although the friends here gave good answers, these answers were not good for my problem
So I wrote a function that works well and has almost no problem with sql Injection.
It might be helpful for those who are looking for similar questions so I put them here to use

function arrayOfQuerys($arrayQuery)
{
    $mx = true;
    $conn->beginTransaction();
    try {
        foreach ($arrayQuery AS $item) {
            $stmt = $conn->prepare($item["query"]);
            $stmt->execute($item["params"]);
            $result = $stmt->rowCount();
            if($result == 0)
                $mx = false;
         }
         if($mx == true)
             $conn->commit();
         else
             $conn->rollBack();
    } catch (Exception $e) {
        $conn->rollBack();
        echo "Failed: " . $e->getMessage();
    }
    return $mx;
}

for use(example): 

 $arrayQuery = Array(
    Array(
        "query" => "UPDATE test SET title = ? WHERE test.id = ?",
        "params" => Array("aa1", 1)
    ),
    Array(
        "query" => "UPDATE test SET title = ? WHERE test.id = ?",
        "params" => Array("bb1", 2)
    )
);
arrayOfQuerys($arrayQuery);

and my connection:

    try {
        $options = array(
            //For updates where newvalue = oldvalue PDOStatement::rowCount()   returns zero. You can use this:
            PDO::MYSQL_ATTR_FOUND_ROWS => true
        );
        $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password, $options);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
        echo "Error connecting to SQL Server: " . $e->getMessage();
    }

Note:
This solution helps you to run multiple statement together,
If an incorrect a statement occurs, it does not execute any other statement

user_61764370

PDO does support this (as of 2020). Just do a query() call on a PDO object as usual, separating queries by ; and then nextRowset() to step to the next SELECT result, if you have multiple. Resultsets will be in the same order as the queries. Obviously think about the security implications - so don't accept user supplied queries, use parameters, etc. I use it with queries generated by code for example.

$statement = $connection->query($query);
do {
  $data[] = $statement->fetchAll(PDO::FETCH_ASSOC);
} while ($statement->nextRowset());
user_5122990
Related