根据提供的数量将多个相同的行插入mysql DB

Question

I want to N identical rows to MySQL DB based on quantity from web-form At the moment the insert script only adds one row at a time ("1" is a default value for this quantity input)

Here is this script:

<?php 
require 'connect.php';

// define variables and set to empty values
$cosmInOut_name = $cosmInOut_dateIn = $cosmInOut_expire = $cosmInOut_priceIn = $cosmInOut_4sale = $cosmInOut_dateOut = $cosmInOut_qtyOut = $cosmInOut_priceOut = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $cosmInOut_name = test_input($_POST["cosmInOut_name"]);
  $cosmInOut_dateIn = test_input($_POST["cosmInOut_dateIn"]);
  $cosmInOut_qtyIn = test_input($_POST["cosmInOut_qtyIn"]);
  $cosmInOut_expire = test_input($_POST["cosmInOut_expire"]);
  $cosmInOut_priceIn = test_input($_POST["cosmInOut_priceIn"]);
  $cosmInOut_4sale = test_input($_POST["cosmInOut_4sale"]);
  $cosmInOut_dateOut = test_input($_POST["cosmInOut_dateOut"]);
  $cosmInOut_qtyOut = test_input($_POST["cosmInOut_qtyOut"]);
  $cosmInOut_priceOut = test_input($_POST["cosmInOut_priceOut"]);
 }

function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

$inserted = 0;
while ($cosmInOut_qtyIn > $inserted) {
$sql = "INSERT INTO cosmInOut (cosmInOut_name, cosmInOut_dateIn, cosmInOut_expire, cosmInOut_priceIn, cosmInOut_4sale, cosmInOut_dateOut, cosmInOut_qtyOut, cosmInOut_priceOut)" .
        "VALUES('{$cosmInOut_name}', '{$cosmInOut_dateIn}', '{$cosmInOut_expire}', '{$cosmInOut_priceIn}', '{$cosmInOut_4sale}', '{$cosmInOut_dateOut}', '{$cosmInOut_qtyOut}', '{$cosmInOut_priceOut}')";
    $inserted++;
    }
if ($conn->query($sql) === TRUE) {
    include('../head.php');
    echo '
        <script> some script</script> 
    ';
    include('../footer.php');

} else {
    echo 'Error' . $conn->error;
}

$conn->close();
?>

Obviously, my while function is not working. How can I fix it? Thanks

Answer 1

At the moment you repeat creating your insert string $cosmInOut_qtyIn number of times & then just insert it once.

You could create the query string outside the loop just the once & then repeat the query inside the loop.

Another solution would be to append the values you want to add $cosmInOut_qtyIn number of times (comma separated) to your query string & then run the query just the once. something like :

$sql = "INSERT INTO cosmInOut (cosmInOut_name, cosmInOut_dateIn, cosmInOut_expire, cosmInOut_priceIn, cosmInOut_4sale, cosmInOut_dateOut, cosmInOut_qtyOut, cosmInOut_priceOut) VALUES ";
$sqlv = "('{$cosmInOut_name}', '{$cosmInOut_dateIn}', '{$cosmInOut_expire}', '{$cosmInOut_priceIn}', '{$cosmInOut_4sale}', '{$cosmInOut_dateOut}', '{$cosmInOut_qtyOut}', '{$cosmInOut_priceOut}')";

$inserted = 0;
while ($cosmInOut_qtyIn > $inserted)
{
    if ($inserted > 0)
        $sql = $sql . ",";
    $sql = $sql . $sqlv;
    $inserted++;
}