insert multiple rows in database

Question

I am a beginner php code and I am trying to update multiple rows of data with this update loop. It will only update the database with the last entry.

$size = count($_POST['wkmove_id']);
$i=0;
while( $i < $size ) {
   // define each variable
   $rps = $_POST['sessrps'][$i];
   $wgt = $_POST['sesswgt'][$i];
   $mv = $_POST['wkmove_id'][$i];

   echo " cat=$cat subcat=$subcat mv= $mv wgt=$wgt rps=$rps<br>";

   $sql = "INSERT INTO wkout_sess 
                 (wkprog_id, wkname_id, wkmove_id, sesswgt, sessrps)
                  VALUES ('$cat','$subcat','$mv','$wgt','$rps')";
   ++$i; 
}
if (!mysql_query($sql,$con))
{

   die('Error: ' . mysql_error());
}

echo "1 record added";

This output will show

cat=1 subcat=1 mv= 1 wgt=45 rps=4
cat=1 subcat=1 mv= 2 wgt=15 rps=4
cat=1 subcat=1 mv= 3 wgt=10 rps=2
cat=1 subcat=1 mv= 4 wgt=12 rps=1
cat=1 subcat=1 mv= 5 wgt=5 rps=54
cat=1 subcat=1 mv= 6 wgt=1 rps=6
cat=1 subcat=1 mv= 7 wgt=2 rps=6
cat=1 subcat=1 mv= 8 wgt=1 rps=6
cat=1 subcat=1 mv= 9 wgt=1 rps=2
cat=1 subcat=1 mv= 10 wgt=6 rps=1
cat=1 subcat=1 mv= 11 wgt=4 rps=69
cat=1 subcat=1 mv= 12 wgt=8 rps=6
cat=1 subcat=1 mv= 13 wgt=4 rps=8
cat=1 subcat=1 mv= 14 wgt=9 rps=4
cat=1 subcat=1 mv= 15 wgt=1 rps=2
cat=1 subcat=1 mv= 16 wgt=3 rps=6
cat=1 subcat=1 mv= 17 wgt=5 rps=4
cat=1 subcat=1 mv= 18 wgt=8 rps=7
cat=1 subcat=1 mv= 19 wgt=4 rps=6
cat=1 subcat=1 mv= 20 wgt=9 rps=7
cat=1 subcat=1 mv= 21 wgt=8 rps=9
cat=1 subcat=1 mv= 22 wgt=4 rps=1
cat=1 subcat=1 mv= 23 wgt=4 rps=1
cat=1 subcat=1 mv= 24 wgt=9 rps=7

1 record added

How do I get it to add all previous 23 entries as well?

Just perform your queries in a loop. Or, ebtter yet, build one query in the loop and execute after the loop is completed. — John Conde
– John Conde, Commented Nov 25, 2014 at 19:39
WARNING: This is terrifyingly insecure because those parameters are not properly escaped. You should NEVER be putting $_POST data directly into the query: it creates a gigantic SQL injection bug. mysql_query is an obsolete interface and should not be used, it's being removed from PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way explains best practices. — tadman
– tadman, Commented Nov 25, 2014 at 19:39
^ Listen to tadman, and perfomance wise, remember: running INSERT INTO TABLE table (column1,column2) VALUES (1,2),(3,4),(5,6),(7,8); is a lot faster then running those queries separately. — Wrikken
– Wrikken, Commented Nov 25, 2014 at 19:42

thatidiotguy · Accepted Answer · 2014-11-25 19:39:28Z

2

You are running the query after the while loop. You want to run it IN the while loop. Also, please note that you are using deprecated mysql_* functions and should move to PDO or MySQLi.

The sql string $sql is only being run once, and that is for it's last value in the loop.

answered Nov 25, 2014 at 19:39

thatidiotguy

9,01114 gold badges66 silver badges109 bronze badges

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

2 Comments

dac086 Over a year ago

so how do I do it In the while loop rather than after? do I need a for loop?

thatidiotguy Over a year ago

@dac086 I am saying you need to run this code mysql_query($sql,$con) in your while loop. Right now it is only being executed ONCE, AFTER your while loop has run. If that still does not make sense I am afraid you are going to have to learn the basics of flow of control before anyone can further help you on this problem.

Collectives™ on Stack Overflow

insert multiple rows in database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related