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.

Thanks

Answer 1

Like this:

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);

// do something with the 2 result sets...

if ($result1)
    mysql_free_result($result1);

if ($result2)
    mysql_free_result($result2);

Answer 2

It says on the PHP site that multiple queries are NOT permitted (EDIT: This is only true for the mysql extension. mysqli and PDO allow multiple queries) . So you can't do it in PHP, BUT, why can't you just execute that query in another mysql_query call, (like Jon's example)? It should still give you the correct result if you use the same connection. Also, mysql_num_rows may help also.

Answer 3

Using SQL_CALC_FOUND_ROWS you can't.

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement.

As someone noted in your earlier question, using SQL_CALC_FOUND_ROWS is frequently slower than just getting a count.

Perhaps you'd be best off doing this as as subquery:

SELECT 
 (select count(*) from my_table WHERE Name LIKE '%prashant%') 
as total_rows,
Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

Answer 4

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 5

You'll have to use the MySQLi extension if you don't want to execute a query twice:

if (mysqli_multi_query($link, $query))
{
    $result1 = mysqli_store_result($link);
    $result2 = null;

    if (mysqli_more_results($link))
    {
        mysqli_next_result($link);
        $result2 = mysqli_store_result($link);
    }

    // do something with both result sets.

    if ($result1)
        mysqli_free_result($result1);

    if ($result2)
        mysqli_free_result($result2);
}