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

Inserting multiple rows with a single query using PDO

本文关键字：插入查询单个 PDO 使用 | 更新日期: 2023-09-27

我已经切换到PDO，并且在构建和执行SQL查询时遇到问题，该查询将插入多行，只需执行一次。

json_decode后$data的内容：

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循环退出

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