What are best practices for handling multiple queries and result sets in PHP scripts?

When handling multiple queries and result sets in PHP scripts, it is important to use different variables to store each result set to avoid overwriting data. It is recommended to use mysqli_multi_query() function for executing multiple queries at once and mysqli_next_result() function to move to the next result set. Additionally, using prepared statements can help prevent SQL injection attacks. 

// Example of handling multiple queries and result sets in PHP scripts

// Connect to database
$mysqli = new mysqli("localhost", "username", "password", "dbname");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Execute multiple queries
$query = "SELECT * FROM table1; SELECT * FROM table2";
if ($mysqli->multi_query($query)) {
    do {
        // Store result set
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                // Process data from result set
                echo $row['column_name'] . "<br>";
            }
            $result->free();
        }
    } while ($mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}

// Close connection
$mysqli->close();

Keywords

MySQLi PDO fetchAll multi_query prepared statements

Related Questions