1

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.

Improve this question
5
  • You are prone to sql enjections and please add some error logs Commented Nov 26, 2015 at 4: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? Commented Nov 26, 2015 at 4: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. Commented Nov 26, 2015 at 4:54
  • Why are you using a stored procedure for this? Commented Nov 26, 2015 at 5:05
  • @Xtern find the answer. I have tested this. Commented Nov 26, 2015 at 5:42

7 Answers 7

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Good explanation...Please check this as well
@Zumry Mohamed I will add this reference as well.
You should consider implementing transactions if you add large qty. Please find the benchmark for 10000 qty.
5

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());
Improve this answer

Comments

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.

Improve this answer

2 Comments

What's bad about creating a procedure for this task? does it slow? unsafe? ...?
As written, the OP's code creates a new procedure every time it is run, which is horribly inefficient.
0 
$count = 0
while($qty >= $count){
    //insertion code goes here...
    $count++
}
Improve this answer

Comments

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.

Improve this answer

1 Comment

@ raveenanigam. But is returns syntax error like -> 'DELIMITER $$ CREATE PROCEDURE myproc() BEGIN DECLARE i integer DEFAULT' at line 1
0

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

Improve this answer

Comments

0

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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.