Two mysqli queries

问题:

Is it possible to have two mysqli_queries like so?:

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 DB. Is there a better way to do this?

回答1:

It 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 more than one query in a single call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections.

Also keep in mind the behavior of mysqli_store_result. It returns FALSE if the query has no result set (which INSERT queries do not) so you must also check mysqli_error to see that it returns an empty string meaning the INSERT was successful.

See:
mysqli_multi_query
mysqli_more_results
mysqli_next_result
mysqli_store_result

回答2:

Once and for all! Use this function to get results of unlimited number of queries anywhere in your script.

Function:

You simply pass the output of the multi query to the function and it returns all results and errors found in each query.

  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;
  }

Usage:

$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);

Output

$output includes 2 arrays \"result\" and \"error\" ordered by query. For example, if you need to check if any errors have happened when executing the third query and fetch 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);
  }
}