MySQL使用while循环插入多个记录 [英] MySQL insert multiple records with while loop

问题描述

我有一个简单的HTML表单和一些php变量，如下所述，用于使用Submit方法将书籍详细信息提交到名为Book的MySQL表中. clcode是一个自动递增字段.

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"]; 

并且需要根据数量插入记录.例如:-如果数量= 2， 它需要执行两次插入查询(应使用自动增量clcode插入2条相同的记录).如果数量= 5， 它需要执行5次插入查询(应该使用自动增量clcode插入5条相同的记录)，依此类推...我使用了存储过程和以下SQL查询来执行此操作.

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

但是它返回语法错误.我无法理解我要怎么做.请任何人都可以帮助我...? Tnx.

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

推荐答案

创建符合您要求的过程没有道理.

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.

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()); } 或方法2

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()); } Or 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)或死掉(mysql_error());

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

这篇关于MySQL使用while循环插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！