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...?

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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.

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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[]

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

I do not understand syntax.

INSERT INTO table (column1, column2) VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2
Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

What error?

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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) (?, ?, ?), (?, ?, ?)"

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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

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

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80

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

Re: Syntax for ON DUPLICATE KEY UPDATE with positional placeholders 80 80
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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.