Hi i’ve got a database table (user_group_join) with the following tables:
user_group_join_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
user_join_id int(11) NOT NULL,
group_join_id int(11) NOT NULL,
FOREIGN KEY (user_join_id) REFERENCES users (user_id),
FOREIGN KEY (group_join_id) REFERENCES user_group (group_id)
They are populated as follows:
user_join_id = 75
group_join_id = 12
user_join_id = 75
group_join_id = 13
user_join_id = 75
group_join_id = 14
What i need to do is when performing a insert query if for example I change the value 12 from group_join_id to 13 i should only have:
user_join_id = 75
group_join_id = 13
user_join_id = 75
group_join_id = 14
This is my php code, i’m deleting the value first and then add the new one if it is not already stored in the database:
$delete_join_group = mysqli_prepare($conn, "DELETE FROM user_group_join WHERE user_join_id=? AND group_join_id=?");
mysqli_stmt_bind_param($delete_join_group, 'ii', $client_id, $old_group_id);
mysqli_stmt_execute($delete_join_group);
mysqli_stmt_close($delete_join_group);
$new_join_group = mysqli_prepare($conn, "
INSERT INTO user_group_join (user_join_id, group_join_id)
SELECT ?, ? FROM user_group_join
WHERE NOT EXISTS (SELECT user_join_id, group_join_id FROM user_group_join
WHERE user_join_id=? AND group_join_id=?)
LIMIT 1 ");
mysqli_stmt_bind_param($new_join_group, 'iiii', $client_id, $new_group_id, $client_id, $old_group_id);
mysqli_stmt_execute($new_join_group);
mysqli_stmt_close($new_join_group);
$old_group_id is the value stored into the database before i delete it
$new_group_id is the new value
Maybe there is another way of sorting out the problem without first deleting the value. Many thanks for your help.