使用PDO通过单个查询插入多行 [英] Inserting multiple rows with a single query using PDO

问题描述

我已切换到PDO，并且在构建和执行SQL查询时遇到麻烦，该SQL查询将只执行一次即可插入多行.

I have switched to PDO and am having trouble building and executing a SQL query that will insert multiple rows with just one execute.

json_decode后$data的内容:

Contents of $data after json_decode:

Array (
    [action] => load
    [app] => CA
    [street_type] => AVE
    [place_type] => --
    [state] => AL
)

代码:

$data = json_decode(file_get_contents("php://input"));
$query = "REPLACE INTO tblsettings(setApp, setIP, setKey, setValue)VALUES";
$qPart = array_fill(0, count($data), "(?, ?, ?, ?)");
$query .= implode(",", $qPart);
$stmt = $db->prepare($query);

    foreach($data as $key => $val){
        $query = "REPLACE INTO tblsettings(setApp, setIP, setKey, setValue)VALUES";
        $qPart = array_fill(0, count($data), "(?, ?, ?, ?)");
        $query .= implode(",", $qPart);
        $stmt = $db->prepare($query);

        $i = 1;
        if(!is_array($val)){
            $stmt->bindParam($i++, $data->app);
            $stmt->bindParam($i++, gethostbyname(trim(gethostname())));
            $stmt->bindParam($i++, $key);
            $stmt->bindParam($i++, $val);
        }

        if ($stmt->execute()){
            echo "Success";
        }else{
            echo $stmt->errorCode();
        }
    }

推荐答案

我猜$i = 1;应该在for循环之内而在if循环之外，因为对于每个for循环，它都将增加4，我们不希望我们从1开始到4并在每个for循环中退出

I guess $i = 1; should be inside the for loop and outside the if loop, because for every for loop it will be incremented by 4, which we dont want we, want to start from 1 and reach 4 and exit for every for loop

 $data = json_decode(file_get_contents("php://input"), true);
 $query = "REPLACE INTO tblsettings(setApp, setIP, setKey, setValue)VALUES";
 $qPart = array_fill(0, count($data), "(?, ?, ?, ?)");
 $query .= implode(",", $qPart);
 $stmt = $db->prepare($query);

 foreach($data as $key => $val){
   $i = 1; //for every for loop reset it to 1
   if(!is_array($val)) {
      $stmt->bindParam($i++, $data->app); //here it will be 1
      $stmt->bindParam($i++, gethostbyname(trim(gethostname())));  //here it will be 2
      $stmt->bindParam($i++, $key);  //here it will be 3
      $stmt->bindParam($i++, $val);  //here it will be 4
   }
  }

  if ($stmt->execute()){
        echo "Success";
   }else{
        echo $stmt->errorCode();
   }

这篇关于使用PDO通过单个查询插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！