Multiple Rows update in foreach loop

Question

my SQL table

table->requirement

id        label_name      label_value      requirement_id   
103         budget         5000                 4           
104       specialist      dentist               4          
105       treatment       clinic                4          
106       expertise       criminal              5          
107       charges          5100                 5

i am trying to update label_value in this this table by mysql-php.

i have tried this code in php but it is working.

foreach ($data as $columnName => $colValue) {
    echo   $subQuery .= "UPDATE `$tableName` SET `label_value`='$colValue' where (`label_name`='$columnName' AND `requirement_id`='$id')";
    mysql_query($subQuery);
    }

GETTING OUTPUT-

UPDATE requirement SET label_value='Physician' where (label_name='specialist' AND requirement_id='2')

UPDATE requirement SET label_value='Physician' where (label_name='specialist' AND requirement_id='2')

UPDATE requirement SET label_value='On Your Home' where (label_name='Appointment' AND requirement_id='2')

I don't know this is correct or not but i need to update this table in form. please help me out of this.

And just remove . from .= "UPDATE because it concatenate your query every time — Saty
– Saty, Commented Mar 21, 2016 at 11:06

Saty · Accepted Answer · 2016-03-22 05:28:24Z

2

You need to remove . from .= "UPDATE because it will concatenate your query.

For first itration of loop it will generate

UPDATE `$tableName` SET `label_value`='$colValue' where (`label_name`='$columnName' AND `requirement_id`='$id'

This query works But for second itration it will generate

UPDATE `$tableName` SET `label_value`='$colValue' where (`label_name`='$columnName' AND `requirement_id`='$id' UPDATE `$tableName` SET `label_value`='$colValue' where (`label_name`='$columnName' AND `requirement_id`='$id'

This is wrong syntax.

Just Use

foreach ($data as $columnName => $colValue) {
    echo   $subQuery = "UPDATE `$tableName` SET `label_value`='$colValue' where (`label_name`='$columnName' AND `requirement_id`='$id')";
    mysql_query($subQuery);
    }

Try to learn mysqli or pdo because mysql is depricated

Also learn mysqli_multi_query

answered Mar 22, 2016 at 5:28

Saty

22.5k7 gold badges35 silver badges52 bronze badges

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

1 Comment

Gautam Jha Over a year ago

i didn't down your vote i just to say a big thanks because you got my problem in no time

Collectives™ on Stack Overflow

Multiple Rows update in foreach loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related