How to Populate and Use a MySQL Database from a Datafeed File

PART 2 – How to Populate and Use a MySQL Database from a Flat Datafeed File

Originally Published on July 4th, 2005

Using PHP to manipulate datafeed import into a MySQL Database

I started writing part 2 the other day and realized I was introducing so much at one time that I was having to stop and define every new item and for the next post that would have included more than 50 definitions. So I am going to change course and start with something much simpler and expand from there.

I’ll introduce a random quote generator first as this uses a 3 column database and import script. Once that has been covered, writing a fully blown datafeed import script will be much easier to understand. So here we go with a very basic three column database import script:

At this point you should have a database setup on your server. If not go back to this post and follow it through. Make a note of the user name, password and database name you used to create it.

1. Create a blank text file in word pad or whatever and fill it with say 10 rows of data.
2. Leave the first line blank (This is so we can remove the first line in datafeed examples later that usually have Merchant column names in them). In this example lets use authors and quotes separated by a tab. Yes I know only two columns. This is because a database needs a primary key which is a column in which every row is unique from any other. We are going to auto generate this as you will see in a few steps.
3. Name the text file example.txt and upload it to your server (it doesn’t matter where).
4. Now here is the script. It is heavily commented for your benefit. Anything after a // (comment tag) can be removed. Write this into a file called example.php.

File Names for this example:

1. example.txt – flat file containing data
2. example.php – import script
3. main table – example
4. temp table – example_temp

PHP Code:
Here is an uncommented version for simplicity

PHP Code:
So upload example.php to your server and run it by loading the URL in your browser.


NOTES: fopen, fclose are interchangeable with fzopen, fzclose which would open GZIP files instead of text files. You can specify any separator in fgetcsv.

Here is a quick example of how to use the contents of this new table on your pages.

[This code goes on your actual pages.]

PHP Code:
And there you have it. I will wait one day to clear up any questions or comments before showing how to use what I have shown you here to handle a full blown datafeed.

Please jump in to improve my code. I have not tested it so there may be one or two bugs. If it does not work for you let me know and I will repair.

All the best,


Dirk

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.