Reading Records / Comparing to Data

steur

Hi everyone,

What I'm trying to do is this:

Read all records from a table in my database and compare it to an array of data that I have.

So I want to basically see if I havent already inserted this data OR if the data in the database in incomplete compared to the new data in the array.

Based on this information I'll ofc want to NOT insert data or merge data.

Now, I'm pretty new at php but I guess i can't get around using nested foreach loops? One for my database records and one for my array of records? This is a huge problem because im working with tens of thousands of records...

Any help would be great

Cheers

NogDog

If one or more of the data columns is/can be defined as a unique index or primary key, then you could simply do a REPLACE or a INSERT . . . ON DUPLICATE KEY UPDATE of the data, instead of having to retrieve every record and then doing all the work in your PHP script.

steur

I know, it's just that this script will be running for alot of databases (who dont belong to me) so there wont always be unique keys, in fact hardly ever.

This is why I want to go through all this data myself using php, but i guess there's just no fast way of doing that..

blackhorse

Hi, I did the same thing, I would create temp table to compare with an original old table.

My approach works fine for up to 3,000 - 5,000 records, but when I have 30,000 records, then it is slow. Detail is in this thread,

http://phpbuilder.com/board/showthread.php?t=10372038

Nogdog, any advice for me too?

sohguanh

steur;10946639 wrote:
Hi everyone,

What I'm trying to do is this:

Read all records from a table in my database and compare it to an array of data that I have.

So I want to basically see if I havent already inserted this data OR if the data in the database in incomplete compared to the new data in the array.

Based on this information I'll ofc want to NOT insert data or merge data.

Now, I'm pretty new at php but I guess i can't get around using nested foreach loops? One for my database records and one for my array of records? This is a huge problem because im working with tens of thousands of records...

Any help would be great

Cheers

Hi my personal working experience is whenever a task require to manipulate with thousands and thousands or even involve millions of records, using a programming language be it C,C++,Java or for your case PHP to fetch them out and compare will not give the optimal performance. Very often we have to resort to the database proprietary stored procedures or SQL syntax.

Since I my job deal mainly with rcle, they offer something MERGE INTO .. syntax. For your case you can create a temp table and use the sqlldr to load your array of data. Then you can create index on that temp table. Then you use the MERGE INTO ... syntax to cross check the your table against that temp table and then depend on business logic to update or insert using a SINGLE SQL.

Please note above is proprietary solution but if it can give me the performance I want, I gotta bow to reality isn't it ? :quiet:

halojoy

sohguanh;10946932 wrote:
Since I my job deal mainly with rcle, they offer something MERGE INTO .. syntax. For your case you can create a temp table and use the sqlldr to load your array of data. Then you can create index on that temp table. Then you use the MERGE INTO ... syntax to cross check the your table against that temp table and then depend on business logic to update or insert using a SINGLE SQL.

MySQL does not support MERGE INTO like Oracle does.
2006 in March, 4 years ago,
a guy over at MySQL forum asked about MERGE INTO.

The advice he got was this:

🙂
Check out REPLACE INTO, or INSERT ... ON DUPLICATE KEY UPDATE ...

http://dev.mysql.com/doc/refman/5.0/en/replace.html
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
HTH,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.

When the guy got the above answer, his reaction was:

Hey, that's totally awesome...

thanks so much Jay!!!

Theron

Web source: http://forums.mysql.com/read.php?61,73734,73734