How do I know if the record is new?

Anon

I'm sure there has to be a very easy way to do this, but I can't figure it out...

If I have a value, how can I figure out if that value exists in the database without cycling through each and every value and testing each one?

For instance, when a user comes in and wants to add their username and password to the database, how to I check to make sure that the username they want to use is unique?

I'm assuming that there is a very easy mysql query that would handle this....

gigowiz

When you create the table you define that field as unique. Then when you try to insert a username you check the error code to see if the insert was successful or failed because of the "no duplicates" violation.

If you don't want to go that (recommended) route, you could get the new username and then do a select count(*) statement where the username is equal to the new one. If the result is greater than 0, you have a duplicate.

The first way uses the capabilities of the database to enforce your data integrity. The second way does it programatically which means just one mistake in a program can ruin your day.

sporte01

You are right on putting on the primary key. Its the quicker way to do things.

This is kinda evil. If you cause it to count all results, it doesnt\\' return back with the results right away. So that if you have 3 million rows, it has to go through all 3 million, even if its indexed, count the matching rows and return.

Instead, do a \\"SELECT 1 FROM BLAH WHERE EXISTS ... \\" it find the first match, and just returns.

-s

If you don\\'t want to go that (recommended) route, you could get the new username and then do a select count(*) statement where the username is equal to the new one. If the result is greater than 0, you have a duplicate.

gigowiz

Good point!

Anon

Check out any one of many SQL tutorials to find your answer, such as:

http://w3.one.net/~jhoffman/sqltut.htm