check specific cols then, update or create a new row, pdo

Question

Trying to check specific column values before inserting a new row. But a bit confused PDO's ON DUPLICATE KEY UPDATE so, here is insert function below.

public function insert_schedule($user_id, $status, $content, $date, $time, $remarks, $created_at){
    $sql = "INSERT INTO schedules (user_id, status, content, return_date, return_time, remarks, created_at) VALUES (?,?,?,?,?,?,?) 
    ON DUPLICATE KEY UPDATE user_id, created_at = VALUES(user_id, created_at)";
    $stmt = $this->connect()->prepare($sql);
    $stmt->execute([$user_id, $status, $content, $date, $time, $remarks, $created_at]); 
}

I want to check created_at and user_id before insert a new row. if created_at already exist on same user_id than update that row. else, insert a new row. How can I fix the above function.

@MarkusZeller The first half of the syntax looks fine. `(columns) VALUES (values)` is a more common syntax than `SET`. But they do need the values for the `UPDATE` portion. — aynber, Apr 09 '20 at 12:00
Do you have a unique column on `user_id` and `created_at`? `ON DUPLICATE KEY` only works for table keys. — aynber, Apr 09 '20 at 12:01
@aynber I updated `ON DUPLICATE KEY UPDATE` part in the question, could you please check it? — yvl, Apr 09 '20 at 12:07
No, you need to separate it out. `UPDATE user_id = VALUES(user_id), created_at = VALUES(created_at)` — aynber, Apr 09 '20 at 12:11
@MarkusZeller actually, I didn't ignore it. didn't understand what you mean there. could you please share full function as an example? — yvl, Apr 09 '20 at 12:11
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html has a bit of information on the proper syntax, but it seems to be lacking an example on multiple columns for the update portion — aynber, Apr 09 '20 at 12:12
https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query and https://stackoverflow.com/questions/8991593/on-duplicate-key-update-multiple-columns show the proper syntax better — aynber, Apr 09 '20 at 12:15
Looking at it... why would you want to update the created_at and user_id to the same thing if those keys exist? The update portion doesn't make sense. — aynber, Apr 09 '20 at 12:20
I don't want to update, `created_at` or `user_id` trying to check those fields before inserting new row. because if there is already same `created_at` and `user_id` then just need to update others rows. if not then insert a new row. @aynber — yvl, Apr 09 '20 at 12:27
Oh, then you want to 1) Make sure that user_id and created_at is a combined unique key, and 2) pass in the REST of the columns in the UPDATE portion, not the ones that stay the same. — aynber, Apr 09 '20 at 12:28
@aynber i can't give unique key, because it's one to many relation. there will be rows same `user_id` but different `created_at` OR same `created_at` and different `user_id` I am really stuck at this point.... — yvl, Apr 09 '20 at 13:35
You need a composite unique key. You can create a key by doing `CREATE UNIQUE INDEX user_created_idx ON schedules (\`user_id\`,\`created_at\`)` This means there can be only 1 row with the same user_id AND created_at. — aynber, Apr 09 '20 at 13:38
@aynber what about the final, insert and update queries? could you give me an example for it? — yvl, Apr 09 '20 at 13:49
Once the unique key is created, you should be able to use Markus' answer to insert and update. The INSERT ... ON DUPLICATE KEY will see that there's already a record with that user_id and created_at, and will update the columns you specify instead. — aynber, Apr 09 '20 at 13:52
oh god, thank you so much. `CREATE UNIQUE INDEX user_created_idx ON schedules (`user_id`,`created_at`)` this is literally saved me... — yvl, Apr 09 '20 at 13:56

Markus Zeller · Accepted Answer · 2020-04-09T12:33:33.397

1

Change the query to:

INSERT INTO schedules SET user_id=?, status=?, content=?, return_date=?, return_time=?, remarks=?, created_at=?
ON DUPLICATE KEY UPDATE status=?, content=?, return_date=?, return_time=?, remarks=?

$stmt->execute([
    $user_id, $status, $content, $date, $time, $remarks, $created_at,
    $status, $content, $date, $time, $remarks
]);

Duplicate keys must definded as index.
Execute needs the values as duplicate keys AGAIN

edited Apr 09 '20 at 12:33

answered Apr 09 '20 at 12:18

Markus Zeller

8,516
2
29
35

thank you, I tried it, but something is off... I can insert new row no problem here. But can't update the columns, if row has `created_at` and `user_id` – yvl Apr 09 '20 at 12:25
Are you sure you have an index over those fields? At least the user_id? If in doubt, dump your DDL. – Markus Zeller Apr 09 '20 at 12:29
sorry, you lost me there. "index over those fields", could you open it a bit? – yvl Apr 09 '20 at 12:30
Please read about [index](https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html). – Markus Zeller Apr 09 '20 at 12:31
Please see my updated answer. The logic was a bit wrong. Let's say the user_id is the unique field, you want to update all other fields. What you don't want to update is the user_id and the created_at. You need to create an UNIQUE index over user_id field. – Markus Zeller Apr 09 '20 at 12:35
yes, put a unique index over `user_id` but still, when I try to update the existed row. still creating new row with same `user_id` and same `created_at` – yvl Apr 09 '20 at 12:40
Impossible. MySQL would not write a new row when one is duplicate. – Markus Zeller Apr 09 '20 at 12:41
okay, I understand. and now I can update, but as you said before. this is wrong. because this is one to many relation table. so there is more then one same `user_id` – yvl Apr 09 '20 at 12:45
Hm, maybe the database design is not that optimal? – Markus Zeller Apr 09 '20 at 12:48
ah I thought, in PDO there is a way or method like in eloquent `updateOrCreate` but I guess, no help huh.... it is just no good.... :) – yvl Apr 09 '20 at 12:52
PDO is a good way to prevent SQL injections, but at the end it is just an interface. – Markus Zeller Apr 09 '20 at 13:24
1

in comments, -thanks to the aynber- I used `CREATE UNIQUE INDEX user_created_idx ON schedules (user_id,created_at)` then, used the query of yours.. and yes it worked out... – yvl Apr 09 '20 at 13:58

check specific cols then, update or create a new row, pdo

1 Answers1