How Can I Execute Multiple INSERT Statements Simultaneously in MySQL Using mysqli?

Multiple mysqli Queries: Running INSERT Statements Simultaneously

Original Problem:

Attempts to execute two INSERT statements using mysqli_query() consecutively result in the failure of the second query. This issue stems from MySQL's security measures, which prevent multiple queries from being executed in a single call.

Solution: mysqli_multi_query()

To execute multiple queries in a single call, it is necessary to utilize the mysqli_multi_query() function:

$query  = "INSERT INTO images (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName');";
$query .= "INSERT INTO images_history (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName', '$day', '$month', '$year');";

$result = mysqli_multi_query($mysqli, $query);

Processing Query Results

Executing mysqli_multi_query() returns a boolean value. However, it is crucial to manually retrieve the results of each query using mysqli_store_result() and loop through them using mysqli_more_results() and mysqli_next_result():

while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)) {
    if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
        echo "Query failed: " . mysqli_error($mysqli);
    }
}

Advantages of mysqli_multi_query()

• Executes multiple queries efficiently in a single call.
• Maintains database integrity by preventing query interference.

Additional Considerations

• mysqli_store_result() may return FALSE for queries without results (e.g., INSERT statements). Use mysqli_error() to confirm success.
• Refer to the documentation for mysqli_multi_query(), mysqli_more_results(), mysqli_next_result(), and mysqli_store_result() for further details.

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