How Can I Execute Multiple MySQL Queries Independently in PHP?

Executing Multiple MySQL Queries as One Using PHP

Problem:

You need to execute two separate MySQL queries and handle their results independently in PHP. The queries are:

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

Answer:

Using the standard mysql_ extension in PHP, it's not possible to execute multiple queries as a single transaction. However, you can achieve the desired result by following these steps:

1. Execute the first query:

$query1 = "SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;";
$result1 = mysql_query($query1);

2. Execute the second query:

$query2 = "SELECT FOUND_ROWS();";
$result2 = mysql_query($query2);

3. Fetch the results of the first query:

$rows1 = mysql_fetch_all($result1, MYSQL_ASSOC);

4. Fetch the results of the second query:

$totalRows = mysql_fetch_row($result2)[0];

By following these steps, you can execute both queries independently and handle their results separately. Note that this approach is not as efficient as executing them as a single transaction, but it's the only way to achieve the desired result using the mysql_ extension.

Alternative Solution (Deprecated):

Update: It was previously thought possible to execute multiple queries in one statement using the mysql_connect() function with a flag. However, this method is deprecated and should not be used.

Modern Solution:

For modern PHP applications, it's recommended to use the PDO (PHP Data Objects) extension for database interactions. PDO provides a more object-oriented and consistent way to execute SQL queries and retrieve their results. Using PDO, you could execute the two queries as follows:

$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
$stmt1 = $pdo->prepare($query1);
$stmt2 = $pdo->prepare($query2);

$stmt1->execute();
$rows1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);

$stmt2->execute();
$totalRows = $stmt2->fetchColumn();

This solution is more efficient and easier to use than the older mysql_ extension.

The above is the detailed content of How Can I Execute Multiple MySQL Queries Independently in PHP?. For more information, please follow other related articles on the PHP Chinese website!