CODEWITHSUNDEEP
phpmysqlsqluniquesql-insert
TecHalo
TecHalo

Reputation: 51

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

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.

Upvotes: 2

Views: 1402

Answers (3)

Sona Israyelyan
Sona Israyelyan

Reputation: 39

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 312219

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)

Upvotes: 4

Barmar
Barmar

Reputation: 782584

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);

Upvotes: 5

Related Questions