Importing Data, checking for existense in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WaspData
Forum Newbie
Posts: 1
Joined: Fri Aug 06, 2010 5:06 am

Importing Data, checking for existense in database

Post by WaspData »

Hi,

I am developing some software (being fairly new to PHP/mySQL this is proving to be a big learning curve :D ).

I need to import data from an external source, via their api, insert it into the database, then aprox every hour, import the data again, check it against the database, and do the following actions:
  • If entry exists in database, do nothing
    If entry does not exist in database, add it to database
    If entry exists in database, but not on the new external source array, then delete record in database
The external data can be retreived via xml, csv, or PHP/JSON

The PHP/JSON output will be like this:

Code: Select all

array(
      array(
            'phish_id' => 123456,
            'url' => 'http://www.example.com/',
            'phish_detail_url' => 'http://www.phishtank.com/phish_detail.php?phish_id=123456',
            'submission_time' => '2009-06-19T15:15:47+00:00',
            'verified' => 'yes',
            'verification_time' => '2009-06-19T15:37:31+00:00',
            'online' => 'yes',
            'target' => '1st National Example Bank',
            'details' => array(
                               array(
                                     'ip_address' => '1.2.3.4',
                                     'cidr_block' => '1.2.3.0/24',
                                     'announcing_network' => '1234',
                                     'rir' => 'arin',
                                     'detail_time' => '2006-10-01T02:30:54+00:00'                         
                                    )
                               )
           )
)
My question:

How do I query the database, to see if entry exists, then either 1) add entry 2) delete entry or 3) Do nothing if entry already exists?

Sorry but this has really got me stumped. I apreciate any help you can give

__________
Regards
Gareth
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Importing Data, checking for existense in database

Post by requinix »

WaspData wrote:How do I query the database, to see if entry exists, then either 1) add entry 2) delete entry or 3) Do nothing if entry already exists?
First, find yourself a PHP+MySQL tutorial. Just about anything half-decent will be fine.

There are two strategies I'd consider:
1. Whenever you update, delete everything in the table first. Start over from scratch. Then there's no worrying about whether something is new, updated, or old.
2. Track the "modified" time for each entry - you might need an extra field for this, I don't know. When updating, do a regular INSERT query with an ON DUPLICATE KEY clause that updates all information or do an INSERT IGNORE and, if there were no inserts made, do an UPDATE instead. With either, set the "modified" time to be the current time. That takes care of adding and updating. For old records, delete everything that has an old "modified" time.

Which one I'd use depends on a number of things, such as how many records you expect to receive, whether you need to maintain data integrity during the update, and how much information is being tracked. Think about it yourself; common sense is often a good guide.
Post Reply