Avoid database duplicates when inserting new row

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.

Without deleting the old value, no I don’t think so.

But to avoid inserting duplicate rows, just add a unique key to the table definition, and you won’t need to check for it in your query anymore.

Hi thanks for your help. Yes it is necessary to delete the row before inserting a new one. The only problem is when i’ve got only one row left it does delete it and without adding a new one instead. I think I might need to work on some form of if statement before executing the query
Also how can I add unique to the table definition?

I might be missing something, but wouldn’t it be easier to do something like an UPDATE WHERE NOT NULL query?

After adding a unique key, you’re right, the logic can become:

  • Update the value
  • in case of a duplicate key error, delete the row

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE tablename
ADD UNIQUE KEY (uesr_join_id, group_join_id)

After adding the unique key you’ll be able to use a single REPLACE INTO query

Hi i’ve sorted it, it is a bit elaborate and i don’t know if it might slow things down but it works for now

	$select_user_flat = mysqli_prepare($conn, "SELECT user_flat_id FROM user_flats WHERE user_flat_user_id=?");
	mysqli_stmt_bind_param($select_user_flat, 'i', $client_id);
	mysqli_stmt_execute($select_user_flat);
    mysqli_stmt_store_result($select_user_flat);
    mysqli_stmt_bind_result($select_user_flat, $tot_row_flats);
    $tot_row_flats = mysqli_stmt_num_rows($select_user_flat);

	$select_join_group = mysqli_prepare($conn, "SELECT group_join_id FROM user_group_join WHERE user_join_id=?");
	mysqli_stmt_bind_param($select_join_group, 'i', $client_id);
	mysqli_stmt_execute($select_join_group);
    mysqli_stmt_store_result($select_join_group);
    mysqli_stmt_bind_result($select_join_group, $tot_row_group_id);
    $tot_row_group_id = mysqli_stmt_num_rows($select_join_group);

    if($tot_row_group_id > 1){
    
    // Elimino prima il gruppo dalla tabella group join
	$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_user_group, $client_id, $new_user_group);
    mysqli_stmt_execute($new_join_group);
    mysqli_stmt_close($new_join_group);

    }else if ($tot_row_group_id == 1 && $old_group_id != $new_user_group){

    	if($tot_row_flats == 1){

    		// Elimino prima il gruppo dalla tabella group join
			$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_user_group, $client_id, $new_user_group);
		    mysqli_stmt_execute($new_join_group);
		    mysqli_stmt_close($new_join_group);


    	}else{

    		$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_user_group, $client_id, $new_user_group);
		    mysqli_stmt_execute($new_join_group);
		    mysqli_stmt_close($new_join_group);


    	}

    

    }

This wall of code makes me cringe.
But yeah, in some sense it’s a true PHP way - an elaborate effort in building sort of Lego figure out of most basic bricks available, without a hint of automation or using whatever smart know-how.
So be it.

1 Like

Hi yes i know i’m still trying the solution you suggested but with no success at the moment :frowning: i’ll Keep trying because i’m Not satisfied of that big piece of code i’ve posted and I will write again here one i’ve Done more tests. Thanks for your help anyway

you need only two things for that:

an unique index. it is not a rocket science, just one command issued in mysql console.
a REPLACE INTO query instead of INSERT INTO which will do exactly what you need - delete a previous entry if it exists

i’m still trying the solution you suggested but with no success at the moment

If you were, you’d come here with a certain question regarding your attempts

Hi i’ve tried your solution:

  1. ALTER TABLE user_group_join ADD UNIQUE KEY (user_join_id, group_join_id)

Then i’ve written this code:

$new_join_group = mysqli_prepare($conn, "REPLACE INTO user_group_join (user_join_id, group_join_id) VALUES (?,?)");
mysqli_stmt_bind_param($new_join_group, 'ii', $client_id, $new_user_group);
mysqli_stmt_execute($new_join_group);
mysqli_stmt_close($new_join_group);

When i execute the script nothing happen in the database :frowning:

I the code in post #8 is what you’re going to go with, you might want to consider wrapping it up in a transaction, incase one of the queries fails and you need to bail out of the process

Hi thanks for your reply. The code i’ve posted in post #8 is not perfect in fact it doesn’t work if i’ve got the following situation:

If i’ve got the user with id 75 having for example two flat in the group 13 and one flat in the group 14, if I change one of the two similar flats with the group with value 12 the database values will be:

user_join_id | group_join_id
          75 | 12
          75 | 14

when i actually would like to have it back:

user_join_id | group_join_id
          75 | 12
          75 | 13
          75 | 14

If nothing happens, then your query failed,
If a query failed, then there was an error.
If there was an error, you should make yourself aware of it
How to report errors in mysqli.

Add the following line before mysqli_connect

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

and make sure that you can see PHP errors in general.

1 Like

Ok, I spent a bit more time reading your question.
Here is how I would do it.

  1. Get all users data.
  2. Make the final array of groups the user belongs to.
  3. Start a transaction.
  4. Delete all user’s records from the join table
  5. Insert all groups the user belongs to from the array formed in #2
  6. Commit a transaction.

It will make your code much more clear and it will handle any case, be in inserting, deleting or updating the group to user linking.

Basically you will need to prepare only two simple queries

mysqli_begin_transaction($conn);

$stmt = mysqli_prepare($conn, "DELETE FROM user_group_join WHERE user_join_id=?");
mysqli_stmt_bind_param($delete_join_group, 'i', $client_id);
mysqli_stmt_execute($stmt);

$sql = "INSERT INTO user_group_join (user_join_id, group_join_id) VALUES(?,?)"
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($delete_join_group, 'ii', $client_id, $group_id);
foreach ($all_user_groups as $group_id) {
    mysqli_stmt_execute($stmt);
}

mysqli_commit($conn);

Like I said. this code is much cleaner. All you need is to prepare an array with data. I suppose, though, that you already have it, from the HTML form.

2 Likes

Hi perfect I came to the same solution in the end :wink: many thanks for your help :wink:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.