As of PHP version 5.3 PDO_MYSQL driver has been repleaced in favour of PDO_MYSQLND. It introduced support for multiple queries.
Though, I can't figure out how to get both result sets if more than one SELECT query has been passed. Both queries have been executed, it can't be that the second one was just dumped.
$db->query["SELECT 1; SELECT 2;"]->fetchAll[PDO::FETCH_ASSOC];
Returns:
array[1] {
[0]=>
array[1] {
[1]=>
string[1] "1"
}
}
hakre
187k48 gold badges419 silver badges802 bronze badges
asked Jun 30, 2012 at 5:06
2
It turns out that you need to use PDOStatement::nextRowset.
$stmt = $db->query["SELECT 1; SELECT 2;"];
$stmt->nextRowset[];
var_dump[ $stmt->fetchAll[PDO::FETCH_ASSOC] ];
This will return result for the second query.
It is a bit odd implementation. It would certainly be easier if multi-query statement would just return both results sets under one array. However, the advantage is that this implementation allows to fetch every query using different FETCH styles.
answered Jun 30, 2012 at 5:27
GajusGajus
64.5k68 gold badges262 silver badges417 bronze badges
MySQL optionally allows having multiple statements in one statement string, but it requires special handling.
Multiple statements or multi queries must be executed with mysqli::multi_query[]. The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.
Example #1 Multiple Statements
The above example will output:
Error executing query: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE mysql.user' at line 1
Prepared statements
Use of the multiple statement with prepared statements is not supported.
See also
- mysqli::query[]
- mysqli::multi_query[]
- mysqli::next_result[]
- mysqli::more_results[]
velthuijsen ¶
4 years ago
Suggested improvement[s] to example 1.
reasons:
Multi_query only returns a non false response if a data/result set is returned and only checks for the first query entered. Switching the first SELECT query with the INSERT query will result in a premature exit of the example with the message "Multi query failed: [0]".
The example assumes that once the first query doesn't fail that the other queries have succeeded as well. Or rather it just exits without reporting that one of the queries after the first query failed seeing that if a query fails next_result returns false.
The changes in the example comes after the creation of the string $sql.