MySQL insert multiple records with while loop

Question

I have a simple HTML form and some php variables as follows to submit book details to a MySQL table named Book using submit method. clcode is a auto increment field.

$isbn =$_POST["isbn"]; 
$bno =$_POST["b_no"];
$bname =$_POST["b_name"];
$qty =$_POST["qty"]; 
$price =$_POST["price"];

And need to insert records depending on the qty. eg:- If a qty=2, It needs to execute the insert query twice (Should be inserted 2 same records with auto increment clcodes). If a qty=5, It needs to execute the insert query 5 times (Should be inserted 5 same records with auto increment clcodes) and so on... I used a stored procedure and the following SQL query to do that.

$query ="CREATE PROCEDURE myproc()
    BEGIN
    DECLARE i int DEFAULT 0;
    DO
    INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES ('$isbn', '$bno', '$bname', '$qty', '$price');
        SET i = i + 1;
WHILE i < $qty;
    END WHILE;
END";

$result = mysql_query($query) or die ( mysql_error());

But it is return a syntax error. I can not uderstand what I am going wrong. Pls. anyone can help me...? Tnx.

You are prone to sql enjections and please add some error logs — jameshwart lopez, Nov 26 '15 at 04:49
so you say `it is return a syntax error`, but you don't think it would have been a good idea to post it here with your question? — Sean, Nov 26 '15 at 04:54
Should it insert two rows with qty = 1 if post qty is 2?. I assume your records should have N no of table rows with minimum scale of 1. — Pradeep Sanjaya, Nov 26 '15 at 04:54

score 6 · Answer 1 · edited May 23 '17 at 12:23

Create table

create table book (
  id int not null auto_increment,
  isbn VARCHAR(20) not null,
  b_no VARCHAR(20) not null,
  b_name VARCHAR(20) not null,
  qty INT not null,
  price decimal(8, 2) not null,
  primary key(id)
);

Create SP

DELIMITER //

CREATE PROCEDURE proc_book (
  IN isbn VARCHAR(20),
  IN no VARCHAR(20),
  IN name VARCHAR(20),
  IN qty INT,
  IN price DECIMAL(8,2)
)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < qty DO
  INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES (isbn, no, name, qty, price);
  SET i = i + 1;
  END WHILE;
END;

DELIMITER ;

Test SP

call proc_book('978-3-16-148410-0', '1', 'name1', 2, 10.00);

Data in table

+----+-------------------+------+--------+-----+-------+
| id | isbn              | b_no | b_name | qty | price |
+----+-------------------+------+--------+-----+-------+
|  1 | 978-3-16-148410-0 | 1    | name1  |   2 | 10.00 |
|  2 | 978-3-16-148410-0 | 1    | name1  |   2 | 10.00 |
+----+-------------------+------+--------+-----+-------+

Call SP from PHP

Use mysqli instead of mysql

<?php 
// input data should be filterd to prevent SQL injection etc.
$isbn  = $_POST["isbn"];
$bno   = $_POST["b_no"];
$bname = $_POST["b_name"];
$qty   = $_POST["qty"];
$price = $_POST["price"];

//connect to database
$connection = mysqli_connect("hostname", "user", "password", "db", "port");

//run the store proc
$sql = "CALL proc_book('" . $isbn . "', '" . $bno . "', '" . $bname . "', " . $qty . ", " . $price . ")";
$result = mysqli_query($connection, $sql) or die("Query fail: " . mysqli_error());
?>

Reference

Good explanation...Please check [this](https://dev.mysql.com/doc/refman/5.6/en/while.html) as well — Zumry Mohamed, Nov 26 '15 at 05:48
You should consider implementing transactions if you add large qty. Please find the benchmark for 10000 qty. — Pradeep Sanjaya, Nov 26 '15 at 11:31

score 5 · Accepted Answer · edited Mar 14 '23 at 23:20

It doesn't make sense to create a procedure to meet your requirement.

Suppose it worked for first run, but create procedure will show error in next run because procedure is already there.

You just need to run insert query a number of times, or build insert query with multiple values and run once. Here is what I think you should do.

Approach 1

for ($i=0;$i< $qty;$i++) {
    $sql = "INSERT INTO table_name (field1,field2,fieldn) VALUES ('field-val1','field-val2','field-valn')";
    $result = mysql_query($sql) or die ( mysql_error());
}

Approach 2

$sql = "INSERT INTO table_name (field1,field2,fieldn) VALUES";

for($i=0;$i< $qty;$i++){
  $sql .= "('val1','val2','valn')";
  if($i < ($qty -1 )){
     $sql .=",";
   }

 }
 $result = mysql_query($sql) or die ( mysql_error());

Abbas · Answer 3 · 2015-11-26T05:02:20.487

0

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

this is what the syntax of while loop

For e.g.:-

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE i INT DEFAULT 0;

  WHILE i < $qty  DO
    INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES ('$isbn', '$bno', '$bname', '$qty', '$price');
    SET i = i + 1;
  END WHILE;
END;

Try this one.

edited Nov 26 '15 at 05:02

answered Nov 26 '15 at 04:52

Abbas

412
2
9

@ raveenanigam. But is returns syntax error like -> 'DELIMITER $$ CREATE PROCEDURE myproc() BEGIN DECLARE i integer DEFAULT' at line 1 – Xtern Nov 26 '15 at 05:23

score 0 · Answer 4 · answered Nov 26 '15 at 04:52

0

You don't want to CREATE PROCEDURE in your query. You just want to INSERT. You should really be just looping in php to execute a prepared statement to perform the insert.

Read the PHP docs on using a mysqli prepared statement.

answered Nov 26 '15 at 04:52

Darwin von Corax

5,201
3
17
28

What's bad about creating a procedure for this task? does it slow? unsafe? ...? – Salah a Mar 14 '23 at 23:25
As written, the OP's code creates a new procedure every time it is run, which is horribly inefficient. – Darwin von Corax Apr 07 '23 at 20:59

score 0 · Answer 5 · answered Nov 26 '15 at 04:57

0

$count = 0
while($qty >= $count){
    //insertion code goes here...
    $count++
}

answered Nov 26 '15 at 04:57

Peter Georges Bou Saada

88
10

score 0 · Answer 6 · answered Nov 26 '15 at 05:04

Modify your sql statement :

$query = "DELIMITER $$

    CREATE PROCEDURE myproc() 
    BEGIN DECLARE i integer DEFAULT 0; 
    while (i< ".$qty.") do 
    begin 
    INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES (".$isbn.", ".$bno.", ".$bname.", ".$qty.", ".$price."); SET i = i + 1; end; end while; 
    END $$

    DELIMITER" ;

For more details visit: http://www.mysqltutorial.org/stored-procedures-loop.aspx

score 0 · Answer 7 · answered Nov 26 '15 at 11:29

Performance benchmark with and without transaction in SP.

1.) Without transactions.

DELIMITER //

CREATE PROCEDURE proc_book (
    IN isbn VARCHAR(20),
    IN no VARCHAR(20),
    IN name VARCHAR(20),
    IN qty INT,
    IN price DECIMAL(8,2)
)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < qty DO
    INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES (isbn, no, name, qty, price);
    SET i = i + 1;
    END WHILE;
END;

DELIMITER ;

Try to add 1000 qty (without transactions)

mysql> call proc_book('978-3-16-148410-0', '1', 'name1', 10000, 10.00);
Query OK, 1 row affected (8 min 43.01 sec)

2.) With transactions.

DELIMITER //

CREATE PROCEDURE proc_book (
    IN isbn VARCHAR(20),
    IN no VARCHAR(20),
    IN name VARCHAR(20),
    IN qty INT,
    IN price DECIMAL(8,2)
)
BEGIN

    DECLARE i INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    WHILE i < qty DO
      INSERT INTO book (isbn, b_no, b_name, qty, price) VALUES (isbn, no, name, qty, price);
      SET i = i + 1;
    END WHILE;

    COMMIT;

END;

DELIMITER ;

Try to add 1000 qty (with transactions)

call proc_book('978-3-16-148410-0', '1', 'name1', 10000, 10.00);
Query OK, 0 rows affected (0.21 sec)

MySQL insert multiple records with while loop

7 Answers7

Linked