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.

Answer 1

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());

Answer 2

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)

Answer 3

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

• CREATE PROCEDURE
• Stored Procedures
• MySQL WHILE
• How to call a MySQL stored procedure from within PHP code?

Answer 4

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

Answer 5

[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.

Answer 6

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

Answer 7

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.