Geek asks and answers

How to update multiple columns by duplicate key

I have a series of radio objects that are inserted into a database in an array. This works great for the first time.

$query="INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) VALUES ";
        foreach ($params AS $key => $value) {       
            if (( $key != "form_key" ) && ( $key != "stylecolor" ) && ( $key != "key" )){           
                $values[] = "('$sku', '$key', '$value', NOW()) ";       
                }
            }
        $query .= implode(', ', $values) . ';';

I am trying to update these columns when the switch state changes by changing this:

$values[] = "('$sku', '$key', '$value', NOW()) ON DUPLICATE KEY UPDATE value = '$value'";

It will work and be updated if there is only one radio button in the array selected with this request:

 INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
 VALUES ('1893240720', '1', 'no', NOW()) 
 ON DUPLICATE KEY UPDATE value = 'yes';

however, when selecting several buttons, it aborts the request:

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
VALUES ('1893300667', '1', 'yes', NOW())
ON DUPLICATE KEY UPDATE value = 'no',
('1893300667', '2', 'yes', NOW()) 
ON DUPLICATE KEY UPDATE value = 'maybe';

Do I need to have a separate one INSERTor how to check for duplicates, and then update each one of valueits own note_id? Is this just a case of bad syntax?

change

, , . , ON DUPLICATE , .

+3
3

Zac, , , ( ):

$query = "INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) VALUES ";

foreach ($params AS $key => $value) {       
    if (( $key != "form_key" ) && ( $key != "stylecolor" ) && ( $key != "key" )){           
        $values[] = "('$sku', '$key', '$value', NOW()) ";       
    }
}
$query .= implode(', ', $values) . 'ON DUPLICATE KEY UPDATE value = VALUES(value);';

:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

+3

- note_id,

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` )
VALUES ('1893300667', '1', 'yes', NOW())
ON DUPLICATE KEY UPDATE
`value` = 'no',
style_color = VALUES(style_color),
created_at = VALUES(created_at);
+1

, ON DUPLICATE KEY UPDATE . ..

INSERT INTO `notes_value` ( `style_color`, `note_id`, `value`, `created_at` ) 
VALUES ('1893300667', '1', 'yes', NOW()), 
       ('1893300667', '2', 'yes', NOW()) 
ON DUPLICATE KEY UPDATE value = values(`value`);

EDIT: (), . EDIT: ​​ , .

, , , .

0

More articles:


All Articles