How Can I Execute Multiple MySQL Queries in PHP?

Combining Multiple MySQL Queries in PHP

In PHP/MySQL, it is not possible to execute multiple queries as a single line of code using the regular mysql API. However, there are alternative approaches to achieve the same result.

Using MySQLi's mysqli_multi_query Function

MySQLi provides a function called mysqli_multi_query that allows you to execute multiple queries in one go. Syntax:

mysqli_multi_query(mysqli $link, string $query);

• $link: A valid MySQLi connection resource.
• $query: A string containing all the queries you want to execute. Separate each query with a semicolon (;).

For example:

$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

$query = "SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;" . "SELECT FOUND_ROWS();";

$mysqli->multi_query($query);

// Handle each result set separately
if ($mysqli->more_results()) {
    do {
        $result = $mysqli->store_result();
        // Process the current result set
        while($row = $result->fetch_array()) {
            // Do something with the row
        }
        $result->free();
    } while ($mysqli->more_results() && $mysqli->next_result());
}

Note: The use of mysql_query and other functions from the mysql API is deprecated and should be avoided in favor of MySQLi or PDO (PHP Data Objects).

Other Approaches

• Execute Two Separate Queries: Though not as efficient, you can simply execute the two queries one after another. The second query will be executed very quickly, so the performance difference is usually negligible.
• Use a Database Abstraction Layer (DAL): A DAL is a software library that provides a common interface for interacting with different databases. Some DALs, like Doctrine, allow you to execute multiple queries as a single transaction.

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