CODEWITHSUNDEEP
phpmysqldatabase
Oseer
Oseer

Reputation: 740

How to update multiple mysql database rows with same field names?

I'm trying to update multiple rows that all share the same field names.

The structure is like:

+---+-------+--------+
|id | name  | value  |
+---+-------+--------+
| 1 | color | red    |
| 2 | fruit | apple  |
| 3 | day   | friday |
+---+-------+--------+

So, something like :

$config_data = "UPDATE system_config SET color = '$_POST[color]', fruit = '$_POST[fruit]', day = '$_POST[day]'";

$success = mysql_query($config_data);

... will not work. How would I perform this query?

Upvotes: 0

Views: 683

Answers (3)

Nicola Cossu
Nicola Cossu

Reputation: 56357

update system_config
set value =
case 
when name = 'color' then 'something1'
when name = 'fruit' then 'something2'
when name = 'day' then 'something3'
else value
end

Upvotes: 0

Daniel
Daniel

Reputation: 1341

You may not do it in only one query.

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

http://cl.php.net/manual/en/function.mysql-query.php

If you want to make multiple queries "all at once" you could use mysqli_multi_query

more documentation here: https://www.php.net/manual/en/mysqli.multi-query.php

Upvotes: 0

genesis
genesis

Reputation: 50966

Try

$config_data = "UPDATE system_config SET value = '$_POST[color]' WHERE name = 'color';";
$success = mysql_query($config_data);
$config_data = "UPDATE system_config SET value = '$_POST[fruit]' WHERE name = 'fruit';";
$success = mysql_query($config_data);
$config_data = "UPDATE system_config SET value = '$_POST[day]' WHERE name = 'day'";
$success = mysql_query($config_data);

instead.It's necessary to put in more queries in this case

Upvotes: 2

Related Questions