Inserting multiple data to SQL server using While loop via PHP

Question

the idea is to insert the same data to SQL DB $quantity times it mean that if i have quantity = 10 the SQL query need to insert the same data 10 times the problem is that always i have one record more mean if i have the quantity=2 i will have 3 records if I have it to 1 i will have 2 record and so

$i=1; 
while ($i<="$quantity")   
{
  $sql="INSERT INTO arivage (ID_Ship, Date_ariv, Date_achat, prov_id, Sph, cyl, Prod_type, Pord_color)  
       VALUES ('', '$date', '$date1', '$prov_id', '$sph', '$cyl', '$Prod_type', '$Prod_color')";
  mysql_query($sql);      
  $i++;
}
if (!mysql_query($sql,$con))
{
  die('Error: ' . mysql_error());
}

i have the sql connection and closing in the code too.

First of all, why would you use "$quantity" instead of a normal variable $quantity? — Eugene
– Eugene, Commented Apr 23, 2012 at 11:41
More or less there are unique key constraints in your table. Also Move your error checking block if(!mysql_query... inside while(). — tuxuday
– tuxuday, Commented Apr 23, 2012 at 11:41

Terry Seidler · Accepted Answer · 2012-04-23 12:09:07Z

1

If you always have one record too many, that probably means your quantity value is not correct (?).

Also, this is not related to your problem, you should use while($i<=$quantity) instead of while($i<="$quantity"). You do not need the "s

This first block is ok:

$i=1; 
while ($i<="$quantity")   
{
    $sql="INSERT INTO arivage (ID_Ship, Date_ariv, Date_achat, prov_id, Sph, cyl, Prod_type, Pord_color)  
        VALUES ('', '$date', '$date1', '$prov_id', '$sph', '$cyl', '$Prod_type', '$Prod_color')";
    mysql_query($sql);      
    $i++;
}

The next if statement executes your query AGAIN, meaning you insert one too many rows. Removing the entire if statement would solve your "one too many rows is inserted" problem.

if (!mysql_query($sql,$con))
{
    die('Error: ' . mysql_error());
}

Alternatively, change your while loop to:.

$i=1; 
while ($i<="$quantity")   
{
    $sql="INSERT INTO arivage (ID_Ship, Date_ariv, Date_achat, prov_id, Sph, cyl, Prod_type, Pord_color)  
    VALUES ('', '$date', '$date1', '$prov_id', '$sph', '$cyl', '$Prod_type', '$Prod_color')";
    if(!mysql_query($sql, $con)) {
        die('Error: ' . mysql_error());
    }
    $i++;
}

edited Apr 23, 2012 at 12:09

answered Apr 23, 2012 at 11:41

Terry Seidler

2,04316 silver badges28 bronze badges

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

5 Comments

Apocaliptica61 Over a year ago

Hi, i am sure that the Quantity is correct. if i fill the form with the quantity = 4 i will have 5 records, if i fill it with 6 i will have 7By the way i try without the " and na thing change.

Apocaliptica61 Over a year ago

If i move the if(!mysql_query... inside while() inside the while loop i will get 2 more records,in mean if i have quantity=2 i wll get :5 records.LOL

Terry Seidler Over a year ago

If you move it inside the loop, then remove your other mysql_query($sql) call. Right now you are executing mysql_query($sql) a $quantity amount of times inside your loop, and then afterwards you execute mysql_query another time.. outside the loop. You should either REMOVE the if(!mysql_query) statement completely, or move it inside the loop and remove the other mysql_query statement :)

Apocaliptica61 Over a year ago

Yes removing the if (!mysql_query($sql,$con)) solve the Problem. But i will not have an error message if the query is not inserted. There is a way to add this error message?

Terry Seidler Over a year ago

Yep, see updated version - your main problem was that you had mysql_query() twice. Having the if-statement INSIDE the loop is fine, but you have to remove your other mysql_query statement. :)

Collectives™ on Stack Overflow

Inserting multiple data to SQL server using While loop via PHP

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related