SQL\PHP: add multiple rows with same values but in a not fixed number-CodePudding

I would like to insert multiple rows in a table. All of these rows will have the same values. The number of rows is not fixed but come from an input label in the frontend ($Num variable). How can I do that? Here my backend code:

<?php

require_once('config.php');

$A= $conn->real_escape_string($_POST['A']);
$B= $conn->real_escape_string($_POST['B']);
$C= $conn->real_escape_string($_POST['C']);
$D= $conn->real_escape_string($_POST['D']);
$E= $conn->real_escape_string($_POST['E']);
$Num = $conn->real_escape_string($_POST['Num']);

$sql = "INSERT INTO table (field_A, field_b, field_C, field_D, field_E) VALUES ('$A', '$B', '$C', '$D', '$E');";

if ($conn->query($sql) === true) {
    echo '<script>
    alert("Everthing OK");
    </script>';
} else {
    echo '<script>
    alert("Error " . $conn->error");
    </script>';
}

CodePudding user response：

I think you can easily achieve this by looping the SQL query within a for() loop for the value that has been put in $num. This way your loop will execute the amount of times the variable in $num has saved.

As Nigel Ren said in the comment under my post, using MySQL Prepared Statements are pretty useful in this current situation.

For instance:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$Num = $conn->real_escape_string($_POST['Num']);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO table (field_A, field_b, field_C, field_D, field_E) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("sss", $A, $B, $C, $D, $E);


for($i=$num; $i <= $num; $i  ) {
    $A= $conn->real_escape_string($_POST['A']);
    $B= $conn->real_escape_string($_POST['B']);
    $C= $conn->real_escape_string($_POST['C']);
    $D= $conn->real_escape_string($_POST['D']);
    $E= $conn->real_escape_string($_POST['E']);
    $stmt->execute();
}

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

CodePudding user response：

I'm not a PHP dev but there is a feature in mysql that will help you out. You stillneed to convert it to PHP code and I'm pretty sure it's quite easy for you.

Starting from version 8 mysql allows you to use CTEs. Those expressions support recursion which is exactly what you need:

insert into test_table(a, b, c, d, e)
  with recursive insert_data(a, b, c, d, e, lvl) as (
    select 'a', 'b', 'c', 'd', 'e', 1 -- <-- recursion starts
    union all
    select 'a', 'b', 'c', 'd', 'e', lvl   1
      from insert_data
     where lvl <= 10 -- <-- recursion's stop condition. This is where you need to pass "$num"
)
select a, b, c, d, e

from insert_data;

See the dbfiddle

CodePudding user response：

If the number of objects on the front-end is variable, then you want to append [] to the field name, also, build your prepared statement based on the number of populated fields

front-end example (client.php):

<form action="server.php" method="post">
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="text" name="name[]" value="" />
    <input type="submit" value="Submit" />
</form>

Back-end example:

<?php

if($_REQUEST)
{
    $names = $_REQUEST['name'];
    $sql = "INSERT INTO names (name) VALUES ";
    #create a prepared statement with values(?, ?, ?... etc)
    $sql .= implode(',', array_fill(0, count($names), '(?)')); 
    $stmt = $db->prepare($sql);
    // bind the parameters
    foreach($names as $key => $name)
    {
        $stmt->bindValue($key 1, $name);
    }
    $stmt->execute($names);    
}

?>