check if identical row exists before insert into database table

Question

My users have the ability to connect with each other, or to connect with the other profile & all connections of that profile at the same time.

But it might be, that the user is already connected with one of the connections from the other user. I need to make sure there won't be two identical rows in the database:

my query looks like this:

if (isset($_POST['crewconnect'])) {

    include '../php/dbconnect.php';

    $connections = $DBcon->query("SELECT connection_id FROM tbl_current_userconnections WHERE user_id=$profileID");
    while($row = $connections->fetch_array()){
        $crewmemberID = $row['connection_id'];

        $DBcon->query("DELETE FROM tbl_former_userconnections WHERE user_id=$activeID AND connection_id=$crewmemberID");
        $DBcon->query("DELETE FROM tbl_former_userconnections WHERE user_id=$crewmemberID AND connection_id=$activeID");

//the following query needs to be changed:
        $DBcon->query("INSERT INTO tbl_current_userconnections(user_id, connection_id) VALUES('$activeID','$crewmemberID'),('$crewmemberID','$activeID')");
    }

    $DBcon->query("INSERT INTO tbl_current_userconnections(user_id, connection_id) VALUES('$activeID','$profileID'),('$profileID','$activeID')");

    $DBcon->close();
}

I thought about something like this for the part to be changed:

        $DBcon->query("IF (NOT EXISTS(SELECT user_id, connection_id FROM tbl_current_userconnections WHERE user_id=$activeID AND connection_id=$crewmemberID)) INSERT INTO tbl_current_userconnections(user_id, connection_id) VALUES('$activeID','$crewmemberID'),('$crewmemberID','$activeID')");

but this is not working, so far...

Gordon Linoff · Accepted Answer · 2016-12-17 13:14:45Z

4

The correct way to do this is to have the database do the check. That is, let the database maintain the integrity of the data.

So, first create a unique index:

create unique index unq_t_userid_connectionid on tbl_current_userconnections(user_id, connection_id);

Then, any attempt to insert an existing value would result in an error. If you don't want an error, then use:

INSERT INTO tbl_current_userconnections(user_id, connection_id) 
    VALUES ('$activeID', '$crewmemberID'),
           ('$crewmemberID', '$activeID')
    ON DUPLICATE KEY UPDATE user_id = VALUES(user_id);

The ON DUPLICATE KEY does nothing to the data, but it prevents an error from being reported.

Why is this better? The problem with doing the check in the application is that two users might attempt to create the same link(s) at the same time. This can result in a race condition where the IF passes for both users, so both insert the data. That is one important reason why doing the logic in the database is important.

Also, you should learn to use parameterized queries and not pass values directly into query statements. Your approach is dangerous and can lead to unexpected syntax errors.

answered Dec 17, 2016 at 13:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

Why are you updating user_id if it already exists with the same value? That option is supposed to be used to update other columns, not the key columns. If he just wants to skip the duplicate, he should use INSERT IGNORE.

Gordon Linoff Over a year ago

@Barmar . . . The user_id = VALUES(user_id) is a no-op. It does nothing (because MySQL checks if a record is changed before doing an update). Well, it does one thing: it ignores the specific error of a duplicate. INSERT IGNORE ignores all errors on the insert, not just those caused by a duplication error. Hence, my preference for ON DUPLICATE KEY UPDATE.

Barmar · Accepted Answer · 2016-12-17 14:04:50Z

0

Put a unique index on (user_id, connection_id). Then you can use INSERT IGNORE. If it tries to create a duplicate row, it will simply skip those values without getting an error.

answered Dec 17, 2016 at 14:04

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

check if identical row exists before insert into database table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related