Insert multiple rows into db table based on SELECT with qualifying rows from another table

Question

I am trying to insert multiple values into the table row which are coming from the array. I almost got the problem solved with this answer. Best way to INSERT many values in mysqli?

please check the accepted answer. in this answer, he is adding the value in a single column but I want it in multiple columns. how can I do this?

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

but this code giving me an error.

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

Any answers appreciated.

full script code

    <?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 is value from checkbox

Answer 1

You don't need a loop. You can use a SELECT query as the source of data for an 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(); }
}