How to execute two mysql queries as one in PHP/MYSQL?

Question

I have two queries, as following:

SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10; SELECT FOUND_ROWS(); 

I want to execute both these queries in a single attempt.

$result = mysql_query($query); 

But then tell me how I will handle each tables set separately. Actually in ASP.NET we uses dataset which handles two queries as

ds.Tables[0]; ds.Tables[1]; .. etc 

How can I do the same using PHP/MYSQL?

Answer 1

Update: Apparently possible by passing a flag to mysql_connect(). See Executing multiple SQL queries in one statement with PHP Nevertheless, any current reader should avoid using the mysql_-class of functions and prefer PDO.

You can't do that using the regular mysql-api in PHP. Just execute two queries. The second one will be so fast that it won't matter. This is a typical example of micro optimization. Don't worry about it.

For the record, it can be done using mysqli and the mysqli_multi_query-function.

Answer 2

As others have answered, the mysqli API can execute multi-queries with the msyqli_multi_query() function.

For what it's worth, PDO supports multi-query by default, and you can iterate over the multiple result sets of your multiple queries:

$stmt = $dbh->prepare("     select sql_calc_found_rows * from foo limit 1 ;      select found_rows()"); $stmt->execute(); do {   while ($row = $stmt->fetch()) {     print_r($row);   } } while ($stmt->nextRowset()); 

However, multi-query is pretty widely considered a bad idea for security reasons. If you aren't careful about how you construct your query strings, you can actually get the exact type of SQL injection vulnerability shown in the classic "Little Bobby Tables" XKCD cartoon. When using an API that restrict you to single-query, that can't happen.