Kaelan Gardner - Protest the Hero - No Stars Over Bethlehem (cover)

My son Kaelan Scott Gardner sharing an impromptu jam to Protest the Hero's song, 'No Stars Over Bethlehem' on 27th November, 2016.

It is hard for me to comprehend just how far he has advanced since then.

Exactly one week ago today, an era ended for me when my oldest boy boarded a plane to begin a new chapter in his life at the Berklee School of Music, Boston.

I am so proud of you my son,

Dad

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.

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.

How to Setup the Ultimate Server for Affiliate Marketing

PART 1 – How to Setup the Ultimate Server for Affiliate Marketing

Originally published on 1st July, 2005

Programming using a laptop computer

In this post I covered some nuts and bolts methods that will help make your datafeed site unique and I offered to provide working code for these examples if anyone was interested so here we go. In order for any of my examples to work you must have the following setup on your server.

You will need to have PHP and MySQL as well as access to cronjob and .htaccess. If you haven’t got cronjob don’t worry I will show you how to auto update your sites from any scheduler you run on your computer.

I like to make all my pages look static with no variables so I add this line to httpd.conf using a shell editor. (If you don’t have access to httpd.conf just add the line to .htaccess)

AddType application/x-httpd-php .htm .html

This tells the server to handle all files with a .htm or .html extension as PHP files allowing you to run PHP code on pages that appear static to search engines and visitors alike.

two more lines to add are:

Options +FollowSymlinks
RewriteEngine On

These will be covered later when we get into mod-rewrite so just add them for now.

You will also need to add a MySQL database on your server, create a user with full privileges and add the user to the database.

Next you will need to import your datafeed into a standardized database. I will continue in a new post as this next subject is going to take some space.

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.

The Benefits of Affiliate Marketing

An Introduction to Affiliate Marketing

Originally Published on 29th June 2005

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. 

Offline Marketing

Most manufacturers lack the knowledge, ability or capacity to sell their products to a level of market saturation. i.e they only sell to people in their neighbourhood, city or county. By using retail outlets, franchising or distributing through third parties, they are able to achieve far greater sales volumes as new people market their products further afield creating new markets that did not previously exist.

Online Marketing

In online marketing we have exactly the same scenario and manufacturers and suppliers need for increased sales is filled in a few different ways. The problem with selling stuff online is actually getting people to come to your site (traffic) which suppliers soon discover is the hardest part of the game.

Affiliate Marketing

Enter stage right, affiliate marketing. I am not going to harp on too much but to me affiliate marketing is the best win win distribution model between manufacturer/supplier and publisher in existence anywhere.

Manufacturer Benefits

• The manufacturer increases his sales force at an exponential level and has people selling for him that he has never met. 
• The main benefit is that sales delivered via affiliate marketing have practically no costs attached as the first contact the supplier has with a customer is when an order or product query arrives.
• Most sales have a Cost Per Sale (Action) arrangement and thousands of affiliates marketing their products whom only require payment if a sale has been made.
• The affiliates cover all hosting, design and marketing costs and although affiliates are paid a tiny percentage compared to offline retail outlets, this is balanced by other benefits.

Affiliate Benefits

• The win win side of the coin for the affiliate is being able to create huge online shopping sites with practically unlimited products lists
• never having to pay for stock
• never having to deal with shipping, returns or customers

The wonderful nature of the web being that it is borderless and products are either delivered to customer's doors or downloaded online making the location where the customer first clicked through to make their purchase irrelevant.

So based on this description it is clear that each and every affiliate site that sells products performs a clear and tangible service both to the supplier and the customer.

Customer Benefits

The customer benefits as the affiliate has sourced and delivered information about the product(s) the customer was looking for and transferred the customer directly to the place of purchase.

An Affiliates Requirements for Success

The single most important talent of any affiliate is their ability to stay ahead of the pack and get their product pages listed first whether in generic internet search engines or via PPC. If anyone learns this skill and maintains it through research and experience then the online world is their oyster. Of course there are many other aspects to selling but with experimentation these will fall into place once an acceptable traffic level has been achieved.

Because there are a very large number of spammy sites selling similar products out there and they appear to be proliferating at an alarming rate, search engines have no choice but to filter out a lot of them to maintain some kind of integrity.

Most search engines do it on a macro level as the sheer volume of Internet pages makes human intervention on a large scale daunting. These macro filters catch out many ethical, quality websites that are confused with spammy sites or have duplicate content due to copying and/or 301 hijacks, intentional or otherwise.

Correct Datafeed Manipulation

I would like to share a few tips on handling datafeeds in such a way as to get the most benefit out of them while doing well in the search results and remaining in them.

Put random text such as related quotes that update on every page load to protect against duplicate content filters and pagejackers. If someone does copy your site or the dupe filter comes calling your page is never the same as the last visit.

Use mod_rewrite to pretty up your URLs. Used in conjunction with preg_replace and urldecode($QUERY_STRING) you can hide your database queries inside your URLs.

Learn to use and love preg_replace and Regex. It is an extremely fast and versatile multiple term search and replace tool. You can create a huge word list and it will swap out all these words dynamically on the fly letting you keep your feed unique. Use it on all your datafeed text, product names etc. Although you will have to learn Regex (Regular Expressions) which can be daunting, the benefits are endless.

Write a redirection script to handle all your outgoing links and mask any URLs that may be on search engines watch lists. Use mod_rewrite, preg_replace and urldecode($QUERY_STRING) to rewrite product URLs to the exact product name for best SERP results, pass them on to your redirection page which will look them up in your database, add the link code and change the header location to match that of the target URL.

e.g. http://yoursite.com/details_royale-beach-towels-in-purple.htm has been rewritten with preg_replace and strtolower from the product name Royale Beach Towels (Purple).

mod_rewrite has been told to watch out for the string 'details_' and pass all requests to a page called details.htm without changing the URL as it appears in the browser and converting the URL into a query. This is achieved in .htaccess like this:

RewriteRule ^details_([^/]+).htm$ /details.htm?$1 [L]

Which gives us

details.htm?royale-beach-towels-in-purple

The details.htm page then uses urldecode($QUERY_STRING) to extract the query and then preg_replace and strtoupper to convert it back to Royale Beach Towels (Purple) which is queried from your database and the correct product link is extracted.

This product link is then updated into the current page header like so:

$URL="Location: ".($row["YourLinkColumn"]."&afsrc=1&sid=".$row["ProductName"]);
header($URL);

In this way your affiliate URLs are never exposed. Use robots.txt to block a directory from all search engines and place your redirection script in there.

To build navigation, use MySQL "Select DISTINCT" to auto build menus from a brands or styles column in your database. Don’t forget to pass your menus through preg_replace and really work on those word lists to keep your sites unique.

If you have not used redirection, consider adding rel="no follow" to all your products HREFs.

Have a good think about your link exchange structure. Is it effective? Consider buying a good sounding URL that you can sub-domain to house all your other sites link exchange programs. In this way your site only has one outgoing link to your separate links domain. This should prevent your site from receiving penalties for linking to bad neighbourhoods which is an easy trap to fall into as they can be hard to spot. The links you gain this way are better for all concerned as they are one way.

If you build more than one site for the same product range mod out your word list a lot. Use a completely different naming structure and whatever you do, make sure to change all your page names, titles and H1s so you are not competing with your own site but expanding your market by targeting different word combinations for a brand or product.

If anyone finds it useful in some way, I would be honoured. Also if anyone wants help with any aspect I have explained above or a simplified working example, just drop me a line and I will write working code and add it in a future article.


All the best and happy marketing,


Dirk Gardner