SilkySmooth’s PHP Blog

Well, I have now been a blogger for two weeks, thus far I have accumulated 16 posts and still, sadly, 0 comments. I don’t mind that no one has posted any comments yet, these will eventually come I am sure of that.

However this blogging game is starting to take it’s toll, specifically on my time. Due to the fact that most of my posts include programming it usually takes 2-3 hours to write up the post, check through the programming, then format it all correctly, etc. So blogging has shall we say, taken up more of my time that I first thought it would.

Up until now I have been trying to post at least once per day and in some cases managing to get two posts finished. Always taking a break on a Sunday.

I have now decided that I will switch this to posting one entry every two days regardless of the day. This will allow me to manage the blog and fit it in around my other daily jobs

If I do have the free time available then I will of course try and get more done, but I think this will be a good balance to allow me to maintain this blog for a long time to come.

Shopping.com is one of the largest product comparison web sites around and if you are an Internet merchant then getting your products listed in this CPC environment is a great way to attract targeted buyers to your web site. For a low deposit of just $25.00* you can get started and you have a choice of four options to get your products listed:

• Shopping.com Crawler – For an additional fee of $300.00* setup plus $20.00* per month you can have the Shopping.com Crawler visit your site and index your products for you.
• CSV File Upload – Upload a CSV file in a specific format detailing each of your products.
• Manual Entry – Manually enter each of your products via a series of forms.
• XML – Provide a HTTP URL to an XML file which contains all of the product data.

* Prices are correct at the time of publishing.

In this post I will show you how to create an XML feed which Shopping.com can use on a daily basis to grab the details of your products. I personally prefer this way because if you make any adjustments to a product then you don’t need to worry about manually updating Shopping.com.

I will assume that you are grabbing your data from a MySQL database as this is the most common, however the feed will work with other databases if updated correctly. With that in mind the first thing we will do in our script is set the database variables:

// Define Database Variables
$DB_User="";
$DB_Pass="";
$DB_Host="";
$DB_Name="";

We then move on to define some constants. Each case will be different depending on your store set up and the type of products you are selling. In my case the products being sold are always in stock, so I have defined the following contstants…

// Define Constants
$Stock="Y";
$StockDescription="In Stock";

The next step in the script is to set the XML header and begin displaying the XML itself with a couple of echo statements.

// Set the XML Header
header("Content-type: text/xml");
// Begin the XML Display
echo "<?xml version="1.0" encoding="UTF-8"?>n";
echo "<products>n";

Now we need to open up a MySQL connection and connect to our database before we run the query to select our product data. You will need to edit the query to match your database, an important thing to note here is to ensure that your query contains an ordering statement. If you fail to order your products then the process at Shopping.com which downloads your XML will have problems distinguishing between new and deleted products.

// Open a MySQL Connection & Select Database
$Connection=mysql_connect($DB_Host,$DB_User,$DB_Pass);
$DB=mysql_select_db($DB_Name);
// Select the Products
$SelectP="SELECT * FROM products WHERE this=that ORDER BY the_primary_key";
$SelectP_Result=mysql_query($SelectP);

We now enter into a loop, first assigning the data to an array and then outputting it via a series of echo statements. Again, depending on the type of store you are working with and the type of products, you will need to make various adjustments within this loop.

An example of this can be seen in the loop below where I am passing the product weight and the shipping zip for each product. This allows Shopping.com to show the shipping costs for the item directly on the Shopping.com web site. However not all stores use UPS for shipping so this would not always be needed.

To that end, I have enclosed the Shopping.com feed specifications PDF manual which includes a full break down of all the fields which are required in the various circumstances.

// Loop through the result set
for ($a=0; $a < mysql_num_rows($SelectP_Result); $a++)
    {
    // Assign the result to an array
    $PArray=mysql_fetch_array($SelectP_Result);
    // Render the product XML
    echo "<product>n";
    echo "<mpn>".$PArray['SKU']."</mpn>n";
    echo "<manufacturer>".$PArray['Manufacturer']."</manufacturer>n";
    echo "<product_name>".$PArray['Item']."</product_name>n";
    echo "<product_description>".$PArray['Copy']."</product_description>n";
    echo "<price>".$PArray['MCF']."</price>n";
    echo "<stock>".$Stock."</stock>n";
    echo "<stock_description>".$StockDescription."</stock_description>n";
    echo "<product_url>".$PArray['URL']."</product_url>n";
    echo "<image_url>".$PArray['ImageURL']."</image_url>n";
    echo "<category>".$PArray['Category']."</category>n";
    echo "<shipping_weight>".$PArray['Wgt']."</shipping_weight>n";
    echo "<zip_code>".$PArray['ShippingZip']."</zip_code>n";
    echo "</product>n";
    }

Finally we close off the XML ‘products’ tag and terminate the connection with the MySQL database.

// Close the Products
echo "</products>n";
// Close MySQL connection
mysql_close($Connection);
?>

Unfortunately there is going to be a little work on your part to get this script functioning perfectly with your products and store set up. But the bulk of the work has been explained above so you should be well on your way to integrating with Shopping.com using XML.

Downloads

Shopping.com XML Feed

Before we begin developing the code for our custom shopping cart we first need to construct the database that will hold all of the various information required for a cart to function. In total there are 4 database tables which need to be created, each of which has a specific purpose within the process of running a shopping cart.

The first table we will create is the ‘categories’ table, this will hold the data for all of our product categories allowing us to organise the products more effectively. The table consists of three columns, our unique identifier called ‘categories_id’, the ‘parent_id’ which allows us to create unlimited sub categories and the ‘category’ column which stores the name of the category itself.

CREATE TABLE categories (
categories_id int(11) NOT NULL PRIMARY KEY auto_increment,
parent_id int(11) NOT NULL DEFAULT 0,
category varchar(200) NOT NULL
);

We will now construct the table which stores the product information.

CREATE TABLE products (
products_id int(11) NOT NULL PRIMARY KEY auto_increment,
categories_id int(11) NOT NULL,
product varchar(200) NOT NULL,
description text,
price decimal(5,2) NOT NULL DEFAULT '0.00',
weight decimal(5,2) NOT NULL DEFAULT '0.00'
);

The table is pretty standard, we have ‘products_id’ as our unique identifier and then the ‘categories_id’ which determines the category which each product is assigned to. We then have a ‘product’ column which will store the name of the item, moving onto the description which we will store in a text datatype so we can use HTML in our item description. Finally we have the price which is self explanatory and the weight which we will use to determine shipping costs of the product.

Next we move onto the ‘orders’ table:

CREATE TABLE orders (
orders_id int(11) NOT NULL PRIMARY KEY auto_increment,
customers_name varchar(64) NOT NULL,
customers_street_address varchar(64) NOT NULL,
customers_suburb varchar(32),
customers_city varchar(32) NOT NULL,
customers_postcode varchar(10) NOT NULL,
customers_state varchar(32),
customers_country varchar(32) NOT NULL,
customers_telephone varchar(32) NOT NULL,
customers_email_address varchar(96) NOT NULL,
delivery_name varchar(64) NOT NULL,
delivery_street_address varchar(64) NOT NULL,
delivery_suburb varchar(32),
delivery_city varchar(32) NOT NULL,
delivery_postcode varchar(10) NOT NULL,
delivery_state varchar(32),
delivery_country varchar(32) NOT NULL,
payment_method varchar(32) NOT NULL,
shipping_method varchar(32) NOT NULL,
order_status varchar(32) NOT NULL,
shipping_price decimal(5,2) NOT NULL DEFAULT '0.00',
total_price decimal(5,2) NOT NULL DEFAULT '0.00',
date_purchased datetime
);

This is quite clearly the largest of the tables and contains all of the customers information for billing and shipping, plus some details of the order itself such as the order values, it’s status, the methods used etc.

And finally, we create the ‘orders_products’ table:

CREATE TABLE orders_products (
orders_products_id int(11) NOT NULL PRIMARY KEY auto_increment,
orders_id int(11) NOT NULL,
product varchar(200) NOT NULL,
price decimal(5,2) NOT NULL DEFAULT '0.00',
weight decimal(5,2) NOT NULL DEFAULT '0.00'
);

This table consists of a unique column, then the ‘order_id’ column which tells us what order the product is associated with. We then store the product details in the remaining three columns. The reason we store this information rather than simply using a reference column such as ‘product_id’ is so that we can keep a historical view of the orders.

If we were to use a reference column each time you changed the price or the weight you would no longer have a history of previous orders.

So, that is all we need to begin the construction of the shopping cart. We may add tables or insert additional columns into the above for other purposes such as tax, fixed price shipping verses live shipping quotes etc, however these will be added on as modules rather than being part of the core system.

In the next chapter we will get started on the administration system and begin putting the file structure together for the project.