PHP/Mysql how to check for duplicates during insert?

[cgchris99]

I have an online database that allows users to enter data.

Is there a way I can verify that they do not enter a duplicate?
I would have to check it with at least three fields. Date and Name and state.

Can this be done during the insert or just before it?

Thanks for any information.

[YappyDog]

Before you do your insert, do a select with the same information to see if it is already in the database. If it is, you've got a duplicate.

[stolzyboy]

simply do a select with the information they are trying to insert, if it comes up with rows with that information, tell them they can't insert that information, otherwise run the insert

[Sxooter]

Well, that's two wrong answers in a row. :-) Unless it's all happening in innodb tables inside a transaction, your answers are vulnerable to a race condition. They seem reasonable, in fact, they are a recipe for inserting duplicates at some point in the future.

The way to prevent duplicates is with a primary key.

create table bubba (trucks int, cars int, huts int, shotguns int, primary key (trucks, cars, huts, shotguns));

Now it's simply unpossible :-) to insert duplicates, the database won't let you.

Even MySQL supports this, so there's no excuse for not using it except for maybe performance. But given that unique constraints are usually implemented as a unique index, the gains from the index during select should offset the cost during insert, and since you don't have to do your own select before hand each time, it may actually be faster than the other methods.

[cgchris99]

sxooter,

thanks for the info. I know how to create the key in the DB but how do I code the PHP to pick up that there was an error so I can redisplay the insert page but display an error at the top.

Thanks again

[YappyDog]

Sxooter is correct when you are dealing with column types that are integers. If your column types allow text, it is trickier.

Based on columns names of Name and State, I assumed it should allow text.

MySql will consider SXOOTER and Sxooter two different (unique) names, will it not?

[stolzyboy]

we've been thru this before sxooter, LOL, and they are not WRONG answers persay, yes, they involve the race condition, however, as long as he does

select * .....

then

insert or not

right after another, the possibility of a race condition is VERY, VERY small, since something like a select will likely take ms's to accomplish

your way is better, but not likely that he will want to change the table around, seems people always have trouble doing that

[Sxooter]

cgchris99:

What you do is "catch the error". It's not as hard as it seems. You execute your SQL query with an @ sign to hide the error from the user should you have auto-error messages turned on, grab the error if there is one, and report it if you want, or put up your own custom error message:

$res = @mysql_query($conn,$query);
if (mysql_errno()) {
  if (mysql_errno()==errornofordupkey){
    print "fancy duplicate entry error";
  }
} 

and Stolzy, the check before without a transaction is just as wrong as tightenting the header bolts on a 426 hemi in the wrong pattern. I.e. neither problem shows up until you're stressing the system, and when it does, kaboom. It's too late to fix. :-)

[stolzyboy]

think you may have these backwards, Sxooter

$res = @($conn, $query);

should be

$res = @($query, $conn);

although, it isn't absolutely necessary for the $conn anyway, as long as you know what conn you are using

[stolzyboy]

Originally posted by Sxooter
and Stolzy, the check before without a transaction is just as wrong as tightenting the header bolts on a 426 hemi in the wrong pattern. I.e. neither problem shows up until you're stressing the system, and when it does, kaboom. It's too late to fix. :-)

and likely you aren't always gonna be able to use your method, with mysql anyway, t'would be nice is mysql caught up to pg soon for decent locking of rows, etc...

not starting a pissing match, just being realistic since we are talking about mysql and its current limitations

[Sxooter]

There's nothing in my method that won't work on MySQL as is, even back quite a few versions.

And no, I'm not having a pissing match here either. :-)

The difference is I take a data centric view, and most developers take a user-centric view.

My view is that 'tis better for heaven and hell to open up and rain down upon the user than to put bad data into the data store. If we can give the users steel umbrellas then that's a good thing.

The user centric view point is that the user should feel as though they are walking through a field of daisies, playing with kittens, and enjoying a nice mint julip, and if we can keep duplicates out of the database, that would be kinda nice too.

The primary key is definitely a possible cause of problems for the users (i.e. speed, ugly and cryptical error messages) but perfect for the data.

The only real issue is if you have more columns than your index type can handle, but since almost every database in the universe supports at least 16 keys in a unique index, it can be considered to be universally available.

The closer the check happens to the data itself, the better, and the database is as close as it gets.

[Sxooter]

Originally posted by stolzyboy
think you may have these backwards, Sxooter

$res = @($conn, $query);

should be

$res = @($query, $conn);

although, it isn't absolutely necessary for the $conn anyway, as long as you know what conn you are using

Sorry, that's my postgresql-ness showing through. In bizarro / pgsql work, the conn is always required, and always comes first... :-)

[YappyDog]

Not that I don't enjoy a good pissing match, but I kind of want to know the answer to my question posted early on in the fray.....

How does the steel umbrella handle this type of situation.

MySql will consider SXOOTER and Sxooter two different (unique) names, will it not?

[stolzyboy]

if you use a LIKE, it WILL, if you use =, it WILL NOT, or if you use LIKE BINARY, it WILL NOT

[Sxooter]

Originally posted by YappyDog
Not that I don't enjoy a good pissing match, but I kind of want to know the answer to my question posted early on in the fray.....

How does the steel umbrella handle this type of situation.

MySql will consider SXOOTER and Sxooter two different (unique) names, will it not?

Depends. MySQL tends to treat upper / lower case the same, so Sxooter and SXOOTER would be the same. However, a custom made unique index will work for Postgresql, which would consider Sxooter and SXOOTER to be different:

create unique index table_ndx on table (lower(field1),lower(field2), lower(field3));

[csn]

Hey Scott,

Think they'll ever add IGNORE to inserts in PG? Gotta admit, I kinda like it in mysql when inserting tons of records where there may be dupes.

[Sxooter]

Originally posted by csn
Hey Scott,

Think they'll ever add IGNORE to inserts in PG? Gotta admit, I kinda like it in mysql when inserting tons of records where there may be dupes.

No, not likely. I'd expect them (the core developers) to say that this is in the realm of triggers. I.e. insert and let a trigger throw away the row AND be the constraint all at once.

I'd tend to agree with them, as triggers aren't really all that hard to write. It's likely something like this would wind up in the /contrib directory.

That's before triggers of course.

[csn]

Cool - hadn't thought of that 🙂. Have to see how it performs when inserting 100K's of records.