Birrel Birrel - 7 months ago 40
PHP Question

PHP, MySQL - Append to cell from input array, when ON DUPLICATE KEY

I've got a MySQL table, something like

sometable

id | val1 | val2 | val3
1 | ... | ... | ...
2 | ... | ... | ...
3 | ... | ... | ...


Where
val1
is set as a
UNIQUE
key.

I am inserting multiple rows simultaneously. The number is variable, and so the query is constructed in the following way:

for(...building array earlier in code...){
$arr[] = array('val1' => $somVal[$i][0],
'val2' => $somVal[$i][1],
'val3' => $somVal[$i][2]);
}

$datafields = array('val1', 'val2', 'val3');

$insert_values = array();
$qms = array();

foreach($arr as $d){
$qms[] = '(' . dbplaceholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}

$db = new PDO(...);
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO sometable (' . implode(',', $datafields ) . ')
VALUES ' . implode(',', $qms) .
' ON DUPLICATE KEY UPDATE val3 = "New Val3 Value"');

if($stmt->execute($insert_values)){
$db->commit();
}


Where:

function dbplaceholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}

return implode($separator, $result);
}


And this works fine. When
val1
is a duplicate, the value in
val3
is changed to "New Val3 Value".

But this isn't quite what I want.

What I would like is for the new
val3
to be appended to the end of the current

Something like:

ON DUPLICATE KEY UPDATE val3 = val3 + ";" + $newVal3Val


But the problem is that
$newVal3Val
is buried inside of
$insert_values
.

How can I have the new
val3
appended to the current value, with semi-colon delimiter, when a duplicate is found?

Answer

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in the ON DUPLICATE KEY UPDATE clause of INSERT statements and returns NULL otherwise.

You should be able to reference the values you've used in $datafields with the values() function

SQL: ON DUPLICATE KEY UPDATE val3 = CONCAT_WS(';',val3,VALUES(val3))

Comments