How To Write Awesome PHP Datafeed Import Code

Part 3 – Fullblown PHP Datafeed Import Code

Using PHP to import product data into a MySQL database from a flat, tab delimited file.

Originally published on 6th July, 2005

Fullblown PHP Datafeed Import Code

If you got the last import script posted here to work, believe it or not you are ready to write a full-blown datafeed import script. We are going to use the last script and expand on it. For this example I will use a standard Commission Junction feed that CJ delivers in GZIP format and is tab delimited.

Remember, all we need to do to allow the script to handle gzipped files instead of uncompressed text files is swap fopen and fclose to gzopen and gzclose.

Let us begin by looking at the first line in a Commission Junction feed which consists of the column descriptions as named by CJ. We don’t actually want to include this line when we pull the feed so in our loop statement we used "if($RowNum > 0){}" which means don’t include the first line.

Here is the first row. I have added numbers beside each one for your reference so you don’t forget column one in MySQL (like most programming languages) is column 0:

PROGRAMNAME(0) PROGRAMURL(1) LASTUPDATED(2) NAME(3) KEYWORDS(4) DESCRIPTION(5) SKU(6) MANUFACTURER(7) MANUFACTURERID(8) UPC(9) ISBN(10) CURRENCY(11) SALEPRICE(12) PRICE(13) RETAILPRICE(14) FROMPRICE(15) BUYURL(16) IMPRESSIONURL(17) IMAGEURL(18) ADVERTISERCATEGORY(19) THIRDPARTYID(20) THIRDPARTYCATEGORY(21) AUTHOR(22) ARTIST(23) TITLE (24) PUBLISHER(25) LABEL(26) FORMAT(27) SPECIAL(28) GIFT(29) PROMOTIONALTEXT (30) STARTDATE(31) ENDDATE(32) OFFLINE(33) ONLINE(34)

After handling many feeds you will become aware that many of these columns are unpopulated and that this differs from merchant to merchant. So you need to decide which ones you need for a particular merchant and then ignore the other columns.

FYI I will be adding an auto incrementing primary key but if a merchant uses item SKUs you should do away with the auto incrementing primary key and set the SKU as the primary key. The benefit of this is you have a unique identifier per item that does not change from import to import.

• LASTUPDATED is great as you can use it to display for example a ”New Item” image
• CATEGORY or MANUFACTURER are great for building automatic menus
• SALEPRICE, RETAILPRICE, PRICE columns are great as you can use them to show discounts, savings and items on sale etc.

In this example for simplicity’s sake i will use a few basic columns. You can call them whatever you want and rearrange them however you want. These are the column names I will use:

• Name = Column 3
• Merchant = 0
• Description = 5
• Sku = 6
• Brand = 7
• SalePrice = 12
• Price = 13
• RetailPrice = 14
• LinkURL = 16
• ImpressionURL = 17
• ImageURL = 18
• Currency = 11
• Category = 19

As I am using the last script I will not add comments. For comments check the earlier post. So we need to set up columns, name them and tell MySQL what they will contain. Then we setup temporary variables and tell the script where to fill them from on each iteration of the import loop. Finally we take the contents of the temporary holding variables, turn them into an array and fill the array contents into the correct columns we created earlier. So here we go:

PHP Code:


And there you have it!

Once you have this done we can move into the good stuff. The next part will be about something I have often seen on here referred to as “the holy grail”. That is on the fly, super fast multiple search and replace of data, making your feed site unique. The method I will show you will work on all aspects of the site including content, page names, auto navigation etc.

All the best,

Dirk Gardner

I used to write articles for a website called A Best Web which is closing down and I have decided to pull my articles from there and give them a new home here for posterity.