PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Feb 23, 2020 12:17 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
PostPosted: Tue Sep 27, 2011 2:04 pm 
Offline
Forum Commoner

Joined: Mon Sep 27, 2010 3:26 pm
Posts: 73
Hi everyone,

I am working on a script that parses a feed periodically for new records and then inserts them into a mysql DB via PDO. I would like to use some sort of efficient caching mechanism so that no duplicate records exist in the DB. Would it be best to insert everything and then delete the duplicates in the mysql table , or somehow cache my insert statement so that it inserts only new records. Perhaps mysql or PDO have some mechanism which can help with this. I would appreciate any directions on best practices for this problem.

Thanks in advance,

KC


Top
 Profile  
 
PostPosted: Tue Sep 27, 2011 8:07 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
How are "new records" defined? If every record has a unique key field, you might just rely on the database, setting that field to "indexed unique" and trapping the errors. I would certainly prefer never entering the data, over detecting duplicates later.


Top
 Profile  
 
PostPosted: Tue Sep 27, 2011 9:21 pm 
Offline
Forum Commoner

Joined: Mon Sep 27, 2010 3:26 pm
Posts: 73
Hi Califdon,

By 'new' record, I mean a record with a unique key field, which has not already been inserted into the DB. I want to avoid duplicate records in the DB. I agree however that for efficiency's sake the best way is to only insert 'new' records.This is the way I've been going up until now, by using pdo so select a list of all records in the db and load them into an array. I then use array_diff to compare database contents to a list of recently parsed records, and select the new ones for insertion. Is there a better way?

Thanks,

KC


Top
 Profile  
 
PostPosted: Tue Sep 27, 2011 9:59 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
Sure. That's what unique keys are for. Just create a unique index on the key field, then the database won't accept a record if one already exists with the same key value. You don't have to do anything with arrays. The only other thing you will want to do is to trap the error that is created, since it would be ugly to see a bunch of error messages and maybe hear a bunch of beeps! :)


Top
 Profile  
 
PostPosted: Wed Sep 28, 2011 1:09 pm 
Offline
Forum Commoner

Joined: Mon Sep 27, 2010 3:26 pm
Posts: 73
Thanks Califdon,

I'll read up on unique keys.

Best regards,

KC


Top
 Profile  
 
PostPosted: Mon Oct 03, 2011 3:22 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4313
Location: Sofia, Bulgaria
Read this article also
http://decipherinfosys.wordpress.com/20 ... imary-key/

_________________
There are 10 types of people in this world, those who understand binary and those who don't


Top
 Profile  
 
PostPosted: Sat Oct 29, 2011 2:35 am 
Offline
Forum Newbie

Joined: Fri Oct 28, 2011 11:42 pm
Posts: 22
Location: Pune, India
Yes unique keyword is that purpose only, You can have a unique key and an error handler that what should happen if duplicate for unique key is found so that you do not end up with script termination.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group