Be Upen Be Upen - 2 years ago 470
PHP Question

PHP Mysql update multiple columns with multiple row values by single query

I am trying to update rows in mysql but I have to use for loop for multiple update for multiple values mysql query is

update table set column1='100',column2='140,column3='150' where id =1
update table set column1='120',column2='145,column3='154' where id =2
update table set column1='141',column2='148,column3='155' where id =3

I am using for loop for running query multiple times with different id, I want to run single query for update all rows, but not using the case, Is that possible?

Answer Source

you can use loop for generating dynamic query. please have a look. This might be helpful to you.

$data[] = array("column1"=>100, "column2"=>140, "column3"=>150, "id"=>1 );
$data[] = array("column1"=>120, "column2"=>145, "column3"=>154, "id"=>2 );
$data[] = array("column1"=>142, "column2"=>148, "column3"=>155, "id"=>3 );

foreach($data as $dat){
    $query = "UPDATE table SET column1=".$dat['column1'].", column2=".$dat['column2'].", column3=".$da['column2']." WHERE id=".$dat['id'];
    echo $query;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download