Two mysqli queries

Question

Is it possible to have two mysqli_queries, for example:

mysqli_query($dblink, "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')") or die(mysql_error()); mysqli_query($dblink, "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')") or die(mysql_error()); 

Basically I want to update two tables in my database. Is there a better way to do this?

Answer 1

This is possible with mysqli_multi_query () .

Example:

 <?php $mysqli = new mysqli($host, $user, $password, $database); // create string of queries separated by ; $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');"; // execute query - $result is false if the first query failed $result = mysqli_multi_query($mysqli, $query); if ($result) { do { // grab the result of the next query if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') { echo "Query failed: " . mysqli_error($mysqli); } } while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results } else { echo "First query failed..." . mysqli_error($mysqli); } 

The key is that you must use mysqli_multi_query if you want to execute multiple queries in one call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injection.

Also consider the behavior of mysqli_store_result . It returns FALSE if the query does not have a result set (which INSERT queries are not), so you should also check mysqli_error to see that it returns an empty string meaning that the INSERT was successful.

Cm:
mysqli_multi_query
mysqli_more_results
mysqli_next_result
mysqli_store_result

Answer 2

Once and forever! Use this function to get the results of an unlimited number of queries anywhere in your script.

Function:

You simply pass the result of the multiprocess to the function and return all the results and errors found in each request.

  function loop_multi($result){ //use the global variable $conn in this function global $conn; //an array to store results and return at the end $returned = array("result"=>array(),"error"=>array()); //if first query doesn't return errors if ($result){ //store results of first query in the $returned array $returned["result"][0] = mysqli_store_result($conn); //set a variable to loop and assign following results to the $returned array properly $count = 0; // start doing and keep trying until the while condition below is not met do { //increase the loop count by one $count++; //go to the next result mysqli_next_result($conn); //get mysqli stored result for this query $result = mysqli_store_result($conn); //if this query in the loop doesn't return errors if($result){ //store results of this query in the $returned array $returned["result"][$count] = $result; //if this query in the loop returns errors }else{ //store errors of this query in the $returned array $returned["error"][$count] = mysqli_error($conn); } } // stop if this is false while (mysqli_more_results($conn)); }else{ //if first query returns errors $returned["error"][0] = mysqli_error($conn); } //return the $returned array return $returned; } 

Application:

 $query = "INSERT INTO table1 (attribute1) VALUES ('value1');"; $query .= "INSERT INTO table2 (attribute2) VALUES ('value2');"; $query .= "SELECT * FROM table3;"; //execute query $result = mysqli_multi_query($conn, $query); //pass $result to the loop_multi function $output = loop_multi($result); 

Exit

$ output includes 2 arrays of "result" and "error", ordered by request. For example, if you need to check if any errors occurred while executing the third request and get its result, you can do:

 if(isset($output['error'][2]) && $output['error'][2] !== ""){ echo $output['error'][2]; }else{ while($row = $output['result'][2]->fetch_assoc()) { print_r($row); } }