MySQL: How should check to see if the data is already in the database?

[igowerf]

I'm working on a form to enter data into a database and I was wondering if there's an easy method to check if the information is already in the table. Right now, I'm just doing another SELECT query and comparing the result to the information entered in the HTML form. Thanks in advance.

 

[Haircut]

It depends, you could do a

SELECT 1
FROM tablename
WHERE inputdata1 = tabledata1
AND inputdata2 = tabledata2
etc.

and find where the query returns a result.

Or you could set up a unique key in your table and capture the error whenever you try and insert a duplicate value.

 

[Armitage]

Originally posted by: Haircut
It depends, you could do a

SELECT 1
FROM tablename
WHERE inputdata1 = tabledata1
AND inputdata2 = tabledata2
etc.

and find where the query returns a result.

Or you could set up a unique key in your table and capture the error whenever you try and insert a duplicate value.

I'd probably go for the latter, if the key doesn't have to be to wierd/big.
If you do a select to check for the data, then you are always hitting the database twice for each piece of data.
Just try to push it, and catch the error if its a duplicate.
Of course keys slow down inserts also...

 

[igowerf]

Originally posted by: Haircut
It depends, you could do a

SELECT 1
FROM tablename
WHERE inputdata1 = tabledata1
AND inputdata2 = tabledata2
etc.

and find where the query returns a result.

Or you could set up a unique key in your table and capture the error whenever you try and insert a duplicate value.

I tried the SELECT method and query seems to always return the same result, even if the data isn't in the table. I'm doing this using MySQL and PHP.

EDIT: Nevermind.... I was using count() instead of mysql_num_rows(). It works now. Thanks! 😱

 

[m0ti]

Depending on the number of inserts you're doing it can be much faster to put the data in (even if it is duplicate) with a batch statement, and then using a delete statement to clear out all the duplicate data.