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
– Your Common Sense, Commented Jun 3, 2014 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
– dev, Commented Jun 4, 2014 at 7:49

Maxim Pavlov · Accepted Answer · 2014-06-03 19:18:40Z

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 3, 2014 at 19:18

Maxim Pavlov

1951 gold badge1 silver badge10 bronze badges

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

1 Comment

Michael - sqlbot Over a year ago

..with the caveat that multi_query represents an extreme security vulnerability if the queries are being dynamically built using data from the outside. xkcd.com/327

Collectives™ on Stack Overflow

How to execute multiple select queries in one statement?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related