How to insert the same record into the database a number of times?

Question

I have a numeric PHP variable named $quantity and based on the number set in this variable, I want to insert the same record in the MySQL table.

Example:

$quantity = '4';

$sql1 = "INSERT INTO table_quantity (username, code, quantity, data) VALUES ('John', '34438', '1', now());";
$sql2 = "INSERT INTO table_quantity (username, code, quantity, data) VALUES ('John', '34438', '1', now());";
$sql3 = "INSERT INTO table_quantity (username, code, quantity, data) VALUES ('John', '34438', '1', now());";
$sql4 = "INSERT INTO table_quantity (username, code, quantity, data) VALUES ('John', '34438', '1', now());";

I typically handle this with a foreach loop on a range, like so: `foreach (range(1, $quantity) as $i) { // do something}` - It makes the code much more readable, with very little overhead compared to a `for` loop. — Nicholas Summers, Jun 16 '20 at 21:47
@NicholasSummers thanks for your answare. But can you explain with a full code example? — Frankie, Jun 16 '20 at 21:52
If you have to do insert username for each row when your insert quantity this seems not to be a good DB-structure. — bestprogrammerintheworld, Jun 16 '20 at 22:35
This question probably lacks an explanation what the actual purpose is. — Martin Zeitler, Jun 19 '20 at 22:50

score 0 · Answer 1 · answered Jun 19 '20 at 22:39

You can achieve this by using a loop and a prepared statement. You need to execute the same statement multiple times. This is also very useful if the values are dynamic and they could change, e.g. the values are coming from user input.

Prerequisite:
You need to open a connection to your database. If you use MySQL then the connection would look something like this:

$pdo = new PDO("mysql:host=localhost;dbname=db_name;charset=utf8mb4", 'username', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

Now you can prepare a statement which we will execute in a loop multiple times.

$stmt = $pdo->prepare("INSERT INTO table_quantity (username, code, quantity, data) VALUES ('John', '34438', '1', now());");

You can use any loop you like but for a simple scenario where we want the same code to be executed a number of times, a while loop is sufficient.

$quantity = 4;
while ($quantity--) {
    $stmt->execute();
}

If it is easier for you, you can use for loop, too.

for($quantity = 0; $quantity < 4; $quantity++) {
    $stmt->execute();
}

Nicholas Summers · Answer 2 · 2020-06-16T22:03:02.507

-1

Try something like this:

// NOTICE - Make sure you are escaping any end-user supplied values correctly - See PHP docs for examples of how
$sql_template = 'INSERT INTO `table_quantity` (`username`, `code`, `quantity`, `data`) VALUES (\'John\', \'34438\', \'1\', now());';
$quantity     = 5;
$sql          = '';

foreach (range(1, $quantity) as $i) {
  $sql .= $sql_template;
}

echo $sql;

See the following docs for explanations:

edited Jun 16 '20 at 22:03

answered Jun 16 '20 at 21:57

Nicholas Summers

4,444
4
19
35

Good solution. Thanks – Frankie Jun 16 '20 at 22:02
In this case, I have copy and past, change the query but not insert 5 records – Frankie Jun 18 '20 at 16:46

How to insert the same record into the database a number of times?

2 Answers2