updating multiple rows in sql in a loop

Question

i have multiple input-fields with the same name as id's and the other fields with other names like

<input name="id[]" value="1" />  <input name="st[]" value="4" />

<input name="id[]" value="5" />  <input name="st[]" value="57" />

<input name="id[]" value="79" /> <input name="st[]" value="43" />
                             .
                             .    
                             .

in my sql table i want to change ...maybe id->3 and id->87 or whatever 10 others to update the column "st" with the value from the right side input field. The key values from id and st are the same. I know the update-sql-syntax of one row. but, if i want more rows i dont know what to do

my reasoning was do it with Jquery Ajax or php Implode like

$id = implode(',', $_POST['id']);
$st = implode(',', $_POST['st']);
$sql = "UPDATE table SET st=('$st') WHERE id=('$id')";

then i found this from user peterm

   UPDATE table
   SET st = CASE id 
                      WHEN 'id_x' THEN 'st_x' 
                      WHEN 'id_y' THEN 'st_y'
                        ... 
                      ELSE st
                      END
 WHERE id IN('id_x', 'id_y', ...);

How do i get it with spontaneously entrys from my website-user in a loop (foreach?)

Better do the foreach in php, not in mysql. And do not forget to check your data before executing the update statement for injections. — Daniel
– Daniel, Commented Aug 28, 2015 at 8:32

Satender K · Accepted Answer · 2015-08-28 09:22:11Z

2

I hope it can resolve your problem. You can use the code as below :

if(!isset($_POST['id'] || count($_POST['id']) == 0){
    return;
}

foreach($_POST['id'] as $key => $id)){
    $sql = "UPDATE table SET st='{$_POST['st'][$key]}' WHERE id='{$id}'";
}

edited Aug 28, 2015 at 9:22

answered Aug 28, 2015 at 9:07

Satender K

5813 silver badges14 bronze badges

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

Comments

Nana Partykar · Accepted Answer · 2015-08-28 09:05:11Z

0

$SizeOfID=sizeof($id);

for($i=0;$i<$SizeOfID;$i++)
{
   $IDvalue=$id[$i];
   $STvalue=$st[$i];

   mysql_query("UPDATE table SET st='$STvalue' WHERE id='$IDvalue'");
}

answered Aug 28, 2015 at 9:05

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

1 Comment

Reeno Over a year ago

Can you explain your solution?

gadlol · Accepted Answer · 2019-06-04 20:01:26Z

0

There is also another quicker way to accomplish it without executing multiple sql because of the loop.

But you must make sure that the query will be constructed in a way that it will "hit" on existing unique key ( for example the primary ) and so it will use the sql's ON DUPLICATE KEY to update its VALUES...

For example:

<?php

$values = [];

foreach( $data as $id => $value ){

    $values[] = "( '".$id."', '".$value."' )";

}

if( (bool)$values ){

   $sql = "INSERT INTO table ( id, st ) VALUES".implode(',',$values)." ON DUPLICATE KEY UPDATE st = VALUES(st)";

   //execute $sql here

}

Of course you need to validate - sanitize data... the usual stuff...

answered Jun 4, 2019 at 20:01

gadlol

1,3532 gold badges16 silver badges24 bronze badges

Collectives™ on Stack Overflow

updating multiple rows in sql in a loop

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related