I have a PHP script that posts many <select> elements in order to update a schedule. Each user (6 digit ID) can select one of any locations (4 digit ID) per day. Here is what that looks like:
$_REQUEST
[
'2018-01-01'=>1234,
'2018-01-02'=>3456,
'2018-01-03'=>2345,
...
]
//The user's ID (`sid`) is missing from the request because a user
//can only update their own schedule; Their ID is stored in $_SESSION
I'd like it so that my SQL query can do all of the table manipulation in one query. The one caveat is that there can't be any duplicates in the table. To be clear, by duplicate I mean that there should only be one record for a given day, per user. So I've used the following query:
PHP + MySQL
$params = array();
$days = 0; //Count the number of days to update
foreach ($_REQUEST as $date => $id) {
array_push($params,
$_SESSION['sid'],
$date,
$id
);
$days ++;
}
$value_string = str_repeat("(?,?,?),", $days - 1). "(?,?,?)";
$sql = "INSERT INTO schedule_s (sid, date, plan) VALUES $value_string
ON DUPLICATE KEY UPDATE date = VALUES(date), sid = VALUES(sid)";
The issue I'm having is that I'm still getting duplicates in my table. I'm finding it a little tricky to come up with a solution, since there are certainly going to be duplicate sids and logs in each column.
Here's the table layout.
sid | date | log
---------------------------
123456 | 2018-01-01 | 1234
123456 | 2018-01-02 | 3456
123456 | 2018-01-03 | 2345
...
567890 | 2018-01-01 | 5678
567890 | 2018-01-02 | 5678
567890 | 2018-01-03 | 2345
...
Any help on getting this update to work would be greatly appreciated!
The immediate fix to your problem is to add a unique index on the combination of the sid and date columns:
ALTER TABLE schedule_s ADD CONSTRAINT cnstr UNIQUE (sid, date);
Then, if a user tries to submit a date which already has an entry, the query would fail at the database level. Then, you could handle the failure in your PHP code.
But, from a user experience point of view, it would be good to load the page and presenting the user with only dates which are currently available. A calendar table might come in handy here, which you could join to schedule_s to generate the available dates.