Syntax for ON DUPLICATE KEY UPDATE with positional placeholders

[RigaConnectCom]

This is working code

$sql = "REPLACE INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}

Hovewer want to change it to ON DUPLICATE KEY UPDATE. Like

$sql = "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE (RecordDay, RecordMonth) ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?, ?, ?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}

Code is not working (not insert, not update). And no error messages.

Please, advice what is incorrect. May be some link with information...?

[pritaeas]

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

[RigaConnectCom]

yees, i have read it. unfortunatelly there is no information related to positional placeholders, so can not understand...

[pritaeas]

The placeholders are for your values, which may need to be specified twice, once for the insert, once for the update. So first decide what you want to do on update.

[RigaConnectCom]

ON DUPLICATE KEY UPDATE
if
$insertData[] = $row_id;
exists in column
Number
then I want update the row with values $insertData[] = $_POST['date_day'][$i]; $insertData[] = $_POST['date_month'][$i];
I do not understand synatax.

For INSERT INTO values are definied in $insertQuery[]
Where to define values for ON DUPLICATE KEY UPDATE?
Also in $insertQuery[]

[pritaeas]

I do not understand syntax.

INSERT INTO table (column1, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2

[RigaConnectCom]

OK, thanks. Code in example is not so straightforward (simple) for me. Tried ON DUPLICATE KEY UPDATE (RecordDay = ?, RecordMonth = ?). But also do not work

[pritaeas]

What error?

[RigaConnectCom]

No error. I made changes but do not work

`

$sql = "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE 
RecordDay = VALUES(RecordDay),
RecordMonth = VALUES(RecordMonth)
";

`

with var_dump($sql); get

string(173) "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE RecordDay = VALUES(RecordDay), RecordMonth = VALUES(RecordMonth) (?, ?, ?), (?, ?, ?)"

with
$sql = "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE (Number,RecordDay, RecordMonth) ";
get
string(139) "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE (Number,RecordDay, RecordMonth) (?, ?, ?), (?, ?, ?)"

[pritaeas]

You shouldn't put VALUES there. See my example.

Did you even execute the query? You should get an error.

[RigaConnectCom]

Your example is ok. But I need foreach

foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
}

If your example how to get foreach to work?

Yes, I get error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE (RecordDay, RecordMonth) ('21', '', '04', '', '04'), ('2' at line 1

[RigaConnectCom]

actually I am stuck... see no other solution as to use REPLACE INTO

[RigaConnectCom]

try {
$sql = "INSERT INTO 2_1_journal (Number, RecordDay) VALUES ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery) . 'ON DUPLICATE KEY UPDATE RecordDay = VALUES(RecordDay);';
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
}
catch (PDOException $e){
echo "DataBase Error: " .$e->getMessage() .'<br>';
}
catch (Exception $e) {
echo "General Error: ".$e->getMessage() .'<br>';
}

This is working example. Any comments, please?