Duplicate entry - how to continue inserting?

spstieng

I am inserting 500.000 + entries to a database from different files (csv). some files may contain exact same data.

When a Duplicate entry is reported, is there anyway for the script to continue and just skipping that insertion?

As it is now, I only get the error message: Duplicate entry and the whole script stops.....

Um....

My execution function looks like this.

	function _mysql_query($strQuery)
	{
	  $result = mysql_query($strQuery) or die (mysql_error());
	  return $result;
	}

I should probably remove the or die (mysql_error()) ?
I would like to logg the error, but not stopping the script.

nemonoman

The script will probably stop executing even if you don't die().

If you don't care about duplicate entries, the change the table definitions to that are currently set to prevent duplicates.

If you care about duplicates...and you should ... then take a little longer: Run a SELECT query of the potentially duplicate data

SELECT *
FROM myTable
WHERE uniqueColumn=$myTestData

If you return a row, skip the entry and go on to the next. Otherwise proceed with the update.

spstieng

You're right. I do not wish to have duplicates in my DB.
But I would like to continue inserting the other rows.

Scenario:

The database contains 500.000 rows
I have a csv file with 15.000 rows

To run the query

SELECT *
FROM myTable
WHERE uniqueColumn=$myTestData

for each of the 15.000 rows to be inserted, would be pretty ineffective in my eyes. It would double the execution time.

Is there no other EFFECTIVE solution for my problem?

nemonoman

on my server a simple select like that executes in between 0.00002 and 0.00006 seconds.

You still have to determine the number of rows returned, as well: typically 0.00002 seconds.

15,000 * average 0.00006 seconds = 0.9 seconds.

If you server were a 100 times slower, the extra overhead would still only be a minute and a half.

nemonoman

wait a minute... are you trying to use phpMyAdmin for this upload? or something similar?

spstieng

I'm using ordinary PHP code.

You can can find the code in this thread.

I did however discover something strange.
That's another problem I have.

Executing the script from an ISP works perfectly. Fast too.
Executing the same script on my server at home has a few problems.

First it just took a while (29 seconds).
And that was only a csv file containing 1000 lines.

A new problem is that the script, for no reason at all, simply just STOPS! No erro msg, no nothing.
Sometimes it stops after 300 lines, and sometimes after 500 lines.
The rows (lines) do get inserted into the DB though.

And I also found out that cuting the files down to 500, 300 and 100 lines, it STILL (on my own server) takes bloody 29 seconds!!!

Beats me why I get this problem.
Maybe it's something in my php.ini config file? Or some settings in MySQL??

Oh yeah... almost forgot :-p
I will try your solution of doing a SELECT to check if it exists or not.

If you could reply in my other thread (the same as above) I would much appreciate it.