Insert Row Count

[jaymc]

Hi, i have a question

I have a table which has an email field that is unique

I have another script that will insert 100 emails into the database

Basically my question is, how can I get some output of exactly how many of those 100 inserts where successfull

Say for instance 20 of them already existed in the database.. how would I get it to tell me 80 out of 100 where inserted

Cheers

[btherl]

Trying to insert data when it's already there (and violating a private key or unique constraint) is an error.. so your insert statement will return an error status.  How you test for that depends on which interface you are using (mysql_query() ? ).  If you're using that, you can check the return value of mysql_query().

You might also try mysql_affected_rows().  I'm pretty sure that won't function if the query failed though, which will be the case if the insert failed.

[jaymc]

But then I would have to run a query for each of the 100 emails

Thus, 100 mysql queries to check if already exists before it then inserts, as apposed to 1 main query

Surely their must be a better way to just use one query, ignore errors of it already existing and just insert the ones that dont exist

Then of course, let me know how many inserted and how many already existed...

[artacus]

It'll do those 100 queries in no time. But if you don't like that approach, insert them into a tmp table, left join the tmp table to your original table and only insert the ones that don't match.
[code]INSERT INTO email_table
SELECT t.*
FROM tmp_email AS t
LEFT JOIN email_table AS eml ON t.email = eml.email
WHERE eml.email IS NULL[/code]

[jaymc]

I basically did it the basic way

For each email run query to check if the email exists, if it doesnt, then insert it

Sometimes their can be 1000 emails to be checked and inserted, so as you can imagine that could possibly be 2000 queries...

It is rather laggy, I certainly need to do this another way

I am not familiar at all with the above method using join. Ive seen that system once with Invision but I really am lost with it..

Is their another way to just run 1 query to check for matches currently in the DB and to then have it return in a array perhaps the emails that are not in that table

I can then use that array to insert the rest, so all in all, 2 queries

Any way how to do that?

[artacus]

Yeah you CAN do it in PHP, but this is something SQL is much, much more efficient at. I gave you most of the pieces you'll need to do this with SQL.
1) Start by creating a table called tmp_email with the same structure as your regular email table.
2) Every time you start the email import, "TRUNCATE tmp_email"
3) Insert every email into the tmp_email table
4) Join / Insert using the code I wrote above. (It will only select the NEW emails, filtering out the old ones)

You can play around with #4 by just using the select until you're sure its working to your satisfaction and then add the INSERT INTO part when you're satisfied.

[jaymc]

So I actually need to create another table in the database to achieve this simple query and insert?!

Thats really bad.. all this because MYSQL stops querying when trying to insert a value that already exists in a unique field

Surely, surely... their is a way to do this without creating another table for this 1 script, or using PHP to manage to the query via an array etc..

[artacus]

What's the big deal about creating a temporary table? How's that "really bad?" I didn't say you had to do it that way, I just gave you the most efficient way to do it. You complain that your way is  laggy and you complain when I give you a way to do it that will be fast.

[fenway]

Is there any reason why you can't just check the table row count before & after the insert operation?

[jaymc]

[quote author=fenway link=topic=119047.msg488530#msg488530 date=1166568794]
Is there any reason why you can't just check the table row count before & after the insert operation?
[/quote]
That would be ideal, but doesnt the query stop when it trys to insert a value that already exists?

[jaymc]

[quote author=artacus link=topic=119047.msg488514#msg488514 date=1166568299]
What's the big deal about creating a temporary table? How's that "really bad?" I didn't say you had to do it that way, I just gave you the most efficient way to do it. You complain that your way is  laggy and you complain when I give you a way to do it that will be fast.
[/quote]
Oh a tempory table... I thought you meant create a static table to run adjasent to the primary one used soley to compare duplicate entries

[fenway]

[quote author=jaymc link=topic=119047.msg488550#msg488550 date=1166570647]
[quote author=fenway link=topic=119047.msg488530#msg488530 date=1166568794]
Is there any reason why you can't just check the table row count before & after the insert operation?
[/quote]
That would be ideal, but doesnt the query stop when it trys to insert a value that already exists?
[/quote]

Not if you use INSERT IGNORE.