DB insertion best practices

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
kc11
Forum Commoner
Posts: 73
Joined: Mon Sep 27, 2010 3:26 pm

DB insertion best practices

Post by kc11 »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB insertion best practices

Post by califdon »

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.
kc11
Forum Commoner
Posts: 73
Joined: Mon Sep 27, 2010 3:26 pm

Re: DB insertion best practices

Post by kc11 »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB insertion best practices

Post by califdon »

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! :)
kc11
Forum Commoner
Posts: 73
Joined: Mon Sep 27, 2010 3:26 pm

Re: DB insertion best practices

Post by kc11 »

Thanks Califdon,

I'll read up on unique keys.

Best regards,

KC
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DB insertion best practices

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
uday8486
Forum Newbie
Posts: 22
Joined: Fri Oct 28, 2011 11:42 pm
Location: Pune, India

Re: DB insertion best practices

Post by uday8486 »

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.
Post Reply