Special Case Addendum to Databases

termn8r

Basically I have a MySQL database with 30,000+ records. This database has had manual changes made over the month, but each month a new database is sent with maybe 100-300 new records along with the old records.

The records are never in order but have unique id's ie the new set might have id 234,21347,16 that the old database didnt have.

In short I need to be able to tell the difference between the two databases and only insert the new records. I have tried using array_diff it returns 0 in php 4.7 no matter what array I throw at it.

I tried writing code to manually do an array_search on database 1 from database 2, this takes a year for each record check.

Is there some simple way to compare 2 databases in MySQL and find the item_id's that are not in the active database and insert them? Or do I need to write some huge merger executable and let it run overnight or something?

Any help is appreciated.
Termn8r

Roger_Ramjet

If the table has unique keys then new entries will 'bounce' off so you don't need to check anything, simply let the DBMS do it's thing.
If you want to know which are the new entries then a left join with 'old_db.primary_key = NULL' will return the list of new entries, http://dev.mysql.com/doc/mysql/en/JOIN.html. (do this before you add the new entries obviously)
If you want to overwrite the old entries with the new ones then use REPLACE instead of INSERT. http://dev.mysql.com/doc/mysql/en/REPLACE.html

termn8r

The problem is the data doesnt have those ids yet. IE the current table might have 1,2,3,5,6,8,9,10 and I want to insert 4 and 7. A very archaic way I found to achieve what I need is to make a temp table of the new data and run a script that checks to see if the id exists in the old table and after about 40 seconds I have a table with ONLY the new ids.

I was looking into using ON DUPLICATE, wish I could just Inert into olddbase ON DUPLICATE IGNORE

would be nice. Thanks for the response though the join might give me some ideas.

Termn8r

Roger_Ramjet

I'm sorry, I don't follow you. Either the new data has an id, or it does not. If it has an id then any existing ones will NOT be overwritten by an insert - only the new ones will be added.

You are just overcomplicating things. All this talk of arrays and such shows that you are not experienced with relational databases. SQL will do it ALL for you very easily.

Primary keys exists specifically for this purpose: to ensure that duplicate records are not inserted. If you try to insert 1000 records and 900 of them have primary keys that are already in the table, then only the 100 records with primary keys not in the table will be inserted, you do not have to do anything else. Please go and read the MySQL manual or a book on the subject if you still do not understand this very basic fact.