How to execute multiple select queries in one statement?

Question

I am trying to execute many select statements in mysql in a single query using php.

select * from clients;
select * from users;
select * from products;

Mysql workbench returns only the records of the last sql statement. My PHP code is

$query = "select * from table1;select * from table2;" 
$result = $pdo->prepare($query);
$result->execute();
$rows = $result->fetchAll(PDO::FETCH_NAMED);

I get the error

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other 
unbuffered queries are active.  Consider using PDOStatement::fetchAll().     
Alternatively, if your code is only ever going to run against mysql, 
you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY    
attribute

Is it possible to execute multiple select queries at once and get all results like MSSQL does?

My only workaround for the moment is to loop through single sql executions and build the response dynamically.

keep with your "workaround" which is actually regular way of doing things — Your Common Sense, Jun 03 '14 at 18:54
The method in the above url seems to have SQL injection issues on user input. This is nice for further investigation. Maybe, the manual approach suits better for this purpose — dev, Jun 04 '14 at 07:49

score 0 · Answer 1 · answered Jun 03 '14 at 19:18

0

Try mysqli::multi_query
http://www.php.net/manual/en/mysqli.multi-query.php

I use it to upload pre-generetad dump files, so it is only CREATE TABLE and INSERT statements, but the manual sais that SELECT works fine two.

And i'm shure that PDO extention has the same method.

answered Jun 03 '14 at 19:18

Maxim Pavlov

195
1
1
10

..with the caveat that multi_query represents an extreme security vulnerability if the queries are being dynamically built using data from the outside. http://xkcd.com/327/ – Michael - sqlbot Jun 04 '14 at 00:28

How to execute multiple select queries in one statement?

1 Answers1