php 为什么我不能运行两个 mysqli 查询？第二个失败

Why can't I run two mysqli queries? The second one fails

提问by BrokenCode

Is it possible to have two mysqli queries like so?

是否可以像这样有两个 mysqli 查询？

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')");
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')");

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

基本上我想更新我的数据库中的两个表。有一个更好的方法吗？

回答by drew010

It is possible with mysqli_multi_query().

使用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 mustuse mysqli_multi_queryif you want to execute more than one query in a single call. For security reasons, mysqli_querywill not execute multiple queries to prevent SQL injections.

关键是如果要在一次调用中执行多个查询，则必须使用mysqli_multi_query它。出于安全原因，mysqli_query不会执行多个查询以防止 SQL 注入。

Also keep in mind the behavior of mysqli_store_result. It returns FALSEif the query has no result set (which INSERTqueries do not) so you must also check mysqli_errorto see that it returns an empty string meaning the INSERTwas successful.

还要记住mysqli_store_result. 它返回FALSE如果查询没有结果集（这INSERT查询不），所以你还必须检查mysqli_error地看到，它返回一个空字符串意味着INSERT成功。

See:
mysqli_multi_query
mysqli_more_results
mysqli_next_result
mysqli_store_result

参见：
mysqli_multi_query
mysqli_more_results
mysqli_next_result
mysqli_store_result

回答by Your Common Sense

Some answers on Stack Overflow are so self-contradicting that it's just mind-blowing.

Stack Overflow 上的一些答案是如此自相矛盾，以至于令人兴奋。

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.

关键是如果您想在一次调用中执行多个查询，则必须使用 mysqli_multi_query。出于安全原因，mysqli_query 不会执行多个查询以防止 SQL 注入。

It basically says, "The key is that you must use a firearm without a safety catch, because a regular weapon won't let you to shoot yourself in a foot. So here is the way to break it down and now you can cripple yourself in a single shot!"

它基本上是说，“关键是你必须使用没有保险装置的枪支，因为普通武器不会让你用脚射击自己。所以这里是分解它的方法，现在你可以使自己瘫痪一枪！”

Despite the fact the OP didn't ask how to run two queries in a single call, despite citing the explicit warningthat the ability to run multiple queries in a single call is inherently dangerous, the answer nonchalantly provides the way to circumvent this limitation.

尽管 OP 没有询问如何在单个调用中运行两个查询，尽管引用了在单个调用中运行多个查询的能力本质上是危险的明确警告，但答案漫不经心地提供了规避此限制的方法。

The worst part, all this dangerous and toilsome mess is for naught. Simply because there is not a single reason to run several queries in a single call.Running queries one by one is how a database API is meant to be used.

最糟糕的是，所有这些危险而繁琐的混乱都是徒劳的。仅仅因为在一次调用中运行多个查询没有一个单一的理由。一项一项地运行查询是数据库 API 的使用方式。

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

基本上我想更新我的数据库中的两个表。有一个更好的方法吗？

Yes of course. Just use two prepared queries.

是的当然。只需使用两个准备好的查询。

$stmt = $dblink->prepare("INSERT INTO images 
(project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) 
VALUES (?,?,?,?,?,?,?)");
$stmt->bind_param("ssssss", $project_id, $user_id, $image_name, $date_created, $link_to_file, $thumbnail, $ImageName);
$stmt->execute();

$stmt = $dblink->prepare("INSERT INTO images_history 
(project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year)
VALUES (?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("ssssssssss", $project_id, $user_id, $image_name, $date_created, $link_to_file, $thumbnail, $ImageName, $day, $month, $year);
$stmt->execute();

it is not only much cleaner but also 100% safe from SQL injection.

它不仅更干净，而且100% 不受 SQL 注入的影响。

And if one of your queries fails, simply ask mysqli for the error message and then fix the error.

如果您的查询之一失败，只需向 mysqli 询问错误消息，然后修复错误。

回答by Kareem

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:

$output 包括按查询排序的 2 个数组“结果”和“错误”。例如，如果您需要检查在执行第三个查询并获取其结果时是否发生了任何错误，您可以执行以下操作：

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