0

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

2
Contributors
12
Replies
28
Views
4 Years
Discussion Span
Last Post by RigaConnectCom
0

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

0

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.

Edited by pritaeas

0

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

0

I do not understand syntax.

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

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

0

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

0

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

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

Edited by pritaeas

0

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

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.