php 基于SELECT将多行插入到数据库表中,并使用另一个表中的合格行

iih3973s  于 2022-11-21  发布在  PHP
关注(0)|答案(1)|浏览(122)

我尝试将多个值插入到来自数组的表行中。我几乎用这个答案解决了问题。Best way to INSERT many values in mysqli?
请检查接受的答案。在这个答案中,他是在一个列中添加值,但我希望它在多个列中。我如何做到这一点?

$array = array($u_id, $pid, $tradexx_price, $pvp, $product_title, $product_image);
  $query = "INSERT INTO selection (c_id,p_id,tradexx_price,pvp,product_name,img) VALUES (?,?,?,?,?,?)";
  $stmt = $con->prepare($query);
  $stmt->bind_param("ssssss", $one);

  $con->query("START TRANSACTION");
  foreach ($array as $one) {
      $stmt->execute();
  }
  $stmt->close();
  $con->query("COMMIT");

但这段代码给了我一个错误。

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables in

如有任何问题,欢迎回答。
完整脚本代码

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
include '../includes/conn.php';

if (isset($_POST['insert_selection'])) {
  $pid = implode(',', $_POST['id']);
  $u_id = $_POST['u_id'];

  foreach ((array)$pid as $p) {
    $get_pro = "SELECT * FROM products WHERE id IN ($p)";
    $run_pro = mysqli_query($con, $get_pro);

    while ($row_pro = mysqli_fetch_array($run_pro)) {
      $id = $row_pro['id'];
      $product_title = $row_pro['product_title'];
      $tradexx_price = $row_pro['product_price'];
      $pvp = $row_pro['pvp'];
      $product_image = $row_pro['product_image'];
      $array = array($u_id, $id, $tradexx_price, $pvp, $product_title, $product_image);
      $query = "INSERT INTO selection (c_id,p_id,tradexx_price,pvp,product_name,img) VALUES (?,?,?,?,?,?)";
      $stmt = $con->prepare($query);
      $stmt->bind_param("ssssss", $cur_c_id, $cur_id, $cur_tradexx_price, $cur_pvp, $cur_product_name, $cur_img);

      $con->query("START TRANSACTION");
      foreach ($u_id as $i => $cur_c_id) {
        $cur_id = $id[$i];
        $cur_tradexx_price = $tradexx_price[$i];
        $cur_pvp = $pvp[$i];
        $cur_product_name = $product_title[$i];
        $cur_img = $product_image[$i];

        $stmt->execute();
      }
      $stmt->close();
      $con->query("COMMIT");

      

    }
  }

}
$pid是复选框中的值

wwtsj6pe

wwtsj6pe1#

您不需要循环。您可以使用SELECT查询作为INSERT的数据源。

error_reporting(E_ALL);
ini_set('display_errors', 1);
include '../includes/conn.php';

if (isset($_POST['insert_selection'])) {
  $pid = implode(',', $_POST['id']);
  $u_id = $_POST['u_id'];

  $sql = "INSERT INTO selection (c_id,p_id,tradexx_price,pvp,product_name,img)
          SELECT ?, id, product_price, pvp, product_title, product_image
          FROM products
          WHERE FIND_IN_SET(id, ?)";
  $stmt = $con->prepare($sql);
  $stmt->bind_param("i", $u_id, $pid);
  $stmt->execute(); }
}

相关问题