PHP - importing affiliate feed into existing directory

I'm php rooky - have done a few small proects, mostly redoing already written scripts for my needs.

Now, I have a really good directory and I already have it populated with US States and Counties. I would like to write a script that would insert my affiliate feeds into existing directory.
Since I ain't PHP wiz, I'm not sure exactly how to do that. Affiliate feed is pretty big - 7Mb in txt format it has one matching field to my database - county. So I should be able to import this feed every days into my directory by counties. Any suggestions where to start? I don't really know where to start since this is a lot of data to insert and I never done anything like that. I can do this manually, but doing it every day is not the best choice

I think I need to pull up one by one county names from my db and match them to the affiliate feed, and INSERT (shourld I insert or replace or truncate first the old data and then insert again since advertiser can update or delete records in new feed?) new data to this county. So this would be kinda loop ->taking first county from directory->selecting all records in affiliate where directory's county = affiliate county -> inserting the selection; next county etc.

Allright, I'll be looking around for ideas may be will figure this out, but any help will be greatly appreciate. Thanks.

 

7MB is big for a txt file, let along inseting all of it in to a database everyday.

Do you need everything in it?

With big feeds, I manually import them in to Excell then filter out or delete the fields that are not required ... reduces feed size. This is a manual process for me as well.

I'm curious to see the responces on this one as well.

 

yeah.. it's about 49K records and they updated every day. If I don't update it every day then it doesn't make sense to have it on my site. I'm just thinking what if I add exactly the same fields into existind database and just do a cron job on trancating current data and entering the new one and run a little script that just updates county index for each record in comparison to what county is spelled out in the table...

 

You can use sed to generate update/insert SQL statements and update your DB automatically every day.

J.D.

 

what is "sed"? *nix shell? if it is, I'm using cpanel, so I don't have too much of freedom. If it is something else - details would help I guess

 

sed is a stream editor. It's a regex-based command line tool that allows you to transform large amounts of text in a variety of ways. In your case, your could transform your feed into a bunch of SQL statements and import converted data into the database.

J.D.

 

gotcha - will look into it. Thanks.

 

Import directly into MySql

Use parameters
--fields-terminated-by=... , --fields-enclosed-by=... , --fields-optionally-enclosed-by=... , --fields-escaped-by=... , --lines-terminated-by=...

See:
http://dev.mysql.com/doc/mysql/en/mysqlimport.html

put those lines into cron/php/perl whatever scripting language you use ...

7MB is trivial on the non-windows platforms because it buffers lines before it reads and does not upload it data in one time

 

how do I skip the first line which is the header (columns name)?

 

--ignore-lines=1 should do it.

 

Thanks for your help.