Checking for duplicate entries

Anon

Hello,

I am uploading files and storing info on the files in the database. When a file upload is made, I need a script to check the database to make sure that a file with that name is not already inserted, and abort upload if that is the case. Whats the best strategy to achieve this?

Thanks!

kparker

The best way is to make sure there is a unique index on the column containing the file name. That way, the database itself will prevent any erroneous duplicates. Then, just go ahead and INSERT the row, but turn off automatic error reporting, and inspect the error message (if any) for an indication of failure. (This is, alas, specific to the particular dbms.)

For example, using PostgreSQL, I have many instances like this:

if (!($result = @pg_exec ($conn, $qry )))
  {
  if ( strpos( $php_errormsg, 'Cannot insert a duplicate key' ) > 0)
    {
    @pg_exec( $conn, 'rollback' );
    ...
    echo "Sorry, that ID is already in use.";
    exit;
    }

kparker

I should have pointed out: the reason you don't issue a SELECT query first to see if the value is already present is that this creates a race condition. Two (or more) clients can perform the same SELECT at the same time, and all will discover that the value does not exist, so they all go ahead and try to add it. I realize that MySQL (and perhaps others) have proprietary extensions to do a unique insert, these are all just that--proprietary. Whereas this method should work with any SQL compliant dbms.

Anon

I left out 2 very important pieces of info:

I am using MySQL
I am database idiot.

Is there a solution specific to MySQL that is simpler than others? If not can you tell me a bit more about the indexes?

Thanks!

kparker

Is there a solution specific to MySQL that is simpler than others?

Not that I am aware of, but as I hope you can see from the example, it takes longer to describe it than to actually do it.

If not can you tell me a bit more about the indexes?

Funny you should ask--I just looked up a source of sql tutorials for someone else here:

http://www.sqlcourse.com/
http://w3.one.net/~jhoffman/sqltut.htm

The second site actually has a section specifically about indexes. And don't forget the MySQL manual itself!

Anon

I have a database of song lyrics that has been created via loading text files. And there are lots of duplicate entries... I am new at this, and didn't realize that I needed to prevent duplicate entries from even loading. Now i have a 1,000 song database...with lots o' duplicates. Is there any way to delete the duplicates without re-creating the table?

thanks

-Caroline =)

shanx24

If your database is MySQL, you cannot do it straight in one-shot SQL cuz it doesnt support sub-queries. You might have to write a script to run through the database and delete the redundant ones. To enlist all those are repeated, try this:

if your table (TEST, for example) is as follows....

+-------+-------------+------
| Field | Type | Null
+-------+-------------+------
| col1 | varchar(10) | YES
+-------+-------------+------

....then, try the following SQL:

select , count() from test group by col1 having count(*) > 1;

+------------+----------+
| col1 | count(*) |
+------------+----------+
| Here's the | 2 |
+------------+----------+

Hope that gets you started...its not rocket science really. 🙂 Feel free to holler if you need more help...

-Shanx

Anon

Ok, did that. great! I can select just those entries that have duplicate names. But how do I delete all but one of those entries??? without doing it manually, that is...

thanks! =)Caroline

tsmith

I had this same thing and I did the loop that you want.
http://www.phpbuilder.com/snippet/detail.php?type=snippet&id=348
should give you the script I used. You will have to tweak it for your db, but there you go.
There is a line in it that says:
echo "id: $row[0], $row[4], count of entries: $num_email<br>\n";

replace that with a new query like:
$query = "delete from tablename where id = $row[x] ";
mysql_query($query, $connect);

so that it's actually deleting instead of echoing. Be aware that it's like watching paint dry waiting for it.