PHP MySQL Insert Query that won't insert if already exists

Question

I've been doing some research and haven't found anything that I've been able to make work, unfortunately, and I think that stems from not understanding the MySQL construct in the examples I've been looking at.

What I'm trying to do is run an insert query, and do a check on values in 3 specific columns to ensure they don't exist, then insert, else do nothing.

My Table is pretty basic: id(int11), user(varchar(45)), label(varchar(255)), agent(varchar(255)), empid(varchar(10)).

My id is my Primary, with Auto increment, and here is my code I currently have that works on inserting, but doesn't have the handling in place for duplicates:

$i = 0;
foreach ($agents as $ag){
    $sql = mysql_query("INSERT INTO `pms_users` 
    (`id`,`user`,`label`,`agent`,`empid`)
     VALUES 
    (NULL, '$user','$group','$labels[$i]','$ag')");
    $i ++;
}

The three columns I need to check against are the $user, $group, and $ag.

Can't you just run a select query specific to your insert target as a test for insertion? — Ghoti
– Ghoti, Commented Jul 2, 2018 at 19:56
You should stop using the mysql extension. It has been deprecated for many years, and is totally removed in PHP 7. Switch to mysqli or PDO, and also learn to use prepared statements to prevent SQL injection. — Barmar
– Barmar, Commented Jul 2, 2018 at 19:57
Thanks Barmar - I appreciate the example you provided below, in addition you to providing best practices. The Unique Index works just as I need it to. Additionally, I use PDO for other projects, and use prepared statements, but I'm constrained to the mysql version I currently have until we get new hardware, so I'll have to settle for mysql_real_escape to sanitize any free form inputs. — TecHalo
– TecHalo, Commented Jul 2, 2018 at 20:06

Barmar · Accepted Answer · 2018-07-02 19:56:52Z

5

Add a unique index for (user, label, empid). Then the database won't allow you to create duplicates.

ALTER TABLE pms_users
ADD UNIQUE INDEX (user, label, empid);

answered Jul 2, 2018 at 19:56

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Mureinik · Accepted Answer · 2018-07-02 19:59:06Z

4

If you can only have one row per combination of user, label and agent, you should define them as a unique constraint:

ALTER TABLE pms_users ADD CONSTRAINT pms_users_unq UNIQUE (`user`, `label`, `agent`);

And then let the database do the heavy lifting with an insert-ignore statement:

INSERT IGNORE INTO `pms_users`
(`user`, `label`, `agent`, `empid`)
VALUES ('some_user', 'some_label', 'some_agent', 123)

answered Jul 2, 2018 at 19:59

Mureinik

316k54 gold badges400 silver badges405 bronze badges

1 Comment

TecHalo Over a year ago

Thanks Mureinik, I really appreciate the example. I was able to get this to work by adding 'Unique Index' as specified above by Barmar, but can you tell me the difference between Unique Index and Unique Constraint? I'd very much like to learn best practices, in addition to having it work the way it should.

Sona Israyelyan · Accepted Answer · 2018-07-02 20:13:41Z

1

You can try insert on duplicate key update query.. It checks duplicate keys. If they exist MySQL do update query if not exist MySQL doing insert query. Sure in your database you should declare unique keys. Here is MySQL documentation for this case https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

edited Jul 2, 2018 at 20:13

answered Jul 2, 2018 at 20:06

Sona Israyelyan

394 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Insert Query that won't insert if already exists

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related