I own a Cafepress Premium shop called HexxWear and for the past month or so I have been working on optimizing my shop for mobile web and smartphones. My mobile site is not yet ready for purchasing product from it but I do have featured products displayed with links to them in the main desktop version of my shop.
As with most web development one of the keys to owning a website is ensuring that there is as little manual maintenance as possible. This is done by developing or integrating dynamic elements on your page by means of scripts or other software.
Previously, on my HexxWear mobile site, I had to manually post five featured products. This is approach is not very convenient. So I decided to add PHP into the mix by displaying random featured products. In order to do that I needed a database of my products that the PHP can pull from. Cafepress, as far as I know, will most likely not provide me with a database dump of my shop products (no one has answered my question in the forum yet). So how can I obtain the necessary data from my shop that I can import into my own SQL database?
The answer to this question is not quick and simple and takes a little time to cleanup prior to importing. However, I have proven that it is possible using a combination of a third party tool called podToolbox, Microsoft Excel (or equivilent spreadsheet application), basic text editor (i.e. notepad, wordpad). There may be quicker procedures for obtaining your cpshop data, but I could not find one aside from the third party tool cpshop. I may look into that later, but from what I saw from the sample sites they are not mobile compliant.
Part 1
Using podToolbox
I only recently learned about podToolbox but it apparently has been around for a while and was originally called cafeToolbox. In a nutshell, it is designed to extract your premium shop data and create a .txt file for feeding which is compatible with Google Base (now called Google Merchant Center). You would upload this .txt file to Google Base and it would display items from your shop feed (.txt file) upon relevant search results in the Google Market. This tool is free to use but you have to register.
- Register at http://www.podtoolbox.com
- When registering you will need your Cafepress shop ID.
- This works for basic or premium shops.
- You will receive a confirmation email with a link to activate
- Log into your account at http://www.podtoolbox.com
- On the left menu under Cafepress Tools, click on Add Shop.
- You are instructed to enter your Cafepress Shop ID ( http://cafepress.com/<shopID> )
- Under Cafepress Tools, click on Request Feed.
- You will reach the cpFeeder Request form
- Fill out the cpFeeder Request form and click the Submit Request to Queue button.
At this point your request will be submitted and the process of extracting your shop data will begin.
***NOTE: This process can take quite a while so you have to be patient and once the process is complete you will receive an email with the .zip file of your feed.
Part2
Extract your cpFeeder .zip and Clean up Using Microsoft Excel
In this part you will extract your .zip file then copy and paste its contents into an Excel spreadsheet for cleanup. The cpFeeder file will contain all of your shop data such as product name, description, price, size and more. I used Microsoft Excel but you should be able to perform this on other equivalents such as OpenOffice Calc.
The reason you need to ‘clean up’ the file is because it will contain characters (i.e.- $, // etc) and other data that may be interpreted differently by the SQL database. I chose to use a spreadsheet to perform this cleanup because it displays data from the file in table format. Not to mention that editing from a text editor on such a huge file, for this particular task is not too pretty.. I also chose to edit with a spreadsheet because you can perform find and replace to speed up the cleanup process. The last reason I chose to use a spreadsheet is because I can then export it as a .csv comma separated value). This file type also make importing into your database table less hassle.
- Extract your cpFeeder .zip file
- It should have a .txt extension
- Open your blank Excel workbook (or equivalent)
- Ensure you only have one worksheet.
- By default if you open a new Excel workbook it will consist of three worksheets.
- Delete the other two worksheets by right-clicking on the worksheet tab and select delete.
- Copy and paste the .txt file contents into your Spreadsheet.
- The contents should display nicely in table format.
Clean up Using Excel:
These are the things you are looking for in your spreadsheet that will need to be cleaned up. You need to remove and/or replace certain characters that can be interpreted as a function or ignored by the SQL database. You use the Find and Replace feature in your spreadsheet to perform this task quicker. I will make suggestions based off of my experience, however you may have a different criteria for your data.
- In Microsoft Excel: Edit > Find
- Click the Options button to display a full dialog box of options.
- Under the Find tab, enter your search text
- Select the check box ‘Match entire cell contents’
- Under the Replace tab, enter your Replace with text. ( The Find what: field should auto populate using the text you entered from the Find tab.)
- If you have numerous results then you will want to select Replace ALL rather than Replace.
Suggested data elements you need to search for in your spreadsheet to replace or remove.
- http:// -You need to search for hyper link prefixes and replace with www
- ” -You need to search for quotes after numbers indicating inches in size and replace them with the abbreviation in. , inch or the word inches.
- Replace quotes in your descriptions with the HTML ASCII code "
- Search for commas and replace with a space.
- For example- Medium, Large, X-Large, 2X-Large
- Replace with- Medium Large X-Large 2X-Large
- Remove or replace commas with space in your product descriptions
- This needs to be done because you will export your spreadsheet as a .csv and the commas will be interpreted as the end a line.
- Locate any blank cells under columns and type the word NULL.
- Unfortunately there is no short way that I can find to perform this.
- All of my NULL values were located under the Size and Material columns
- Your data may differ based on what products your shop contains
- A blank cell will be interpreted as no data and will end up shifting your other row data over resulting in cells being under the wrong column. (i.e.- prices for some rows may end up under the size column instead of the price column.)
This was the most time consuming part depending on the size of your shop and how much product you have. Hopefully this does not have to be done often 
Export your spreadsheet
Ensure that you only have one spreadsheet in your workbook consisting of the spreadsheet for your shop.
- In Microsoft Excel, File > Save As
- Save as Type: CSV (Comma delimited)(*.csv)
Part 3
Create Your SQL Database and Table
I am not going to cover the steps that involve creating a SQL database because there are numerous docs on the web about that task. Here is the link to MySQL Documentation for your reference: http://dev.mysql.com/doc/. I am going to cover the steps needed to ensure a successful import of your feed generated from podToolbox.
- Create your SQL database
- Create your table
- Refer to your cpFeeder .txt file
- The very first line of the file consists of your column headers (i.e.- id, product, description, price..etc)
- Create the TABLE fields in your SQL database using the same names of the headers in the .txt file.
Import your .csv File
I used phpMyAdmin for administering MySQL but you can perform the same tasks via command line.
- In phpMyAdmin, click on your Database
- Click on the table for your shop data
- Click on Import tab
- Browse for your .csv file
- Under Format of imported file, select CSV using LOAD DATA
- In the CSV using LOAD DATA options
- Select Replace table data with file
- Fields terminated by Enter a comma ( , )
- Fields enclosed by Enter a quota ( ” )
- Fields escaped by Enter backslash ( \ )
- Lines terminated by Enter auto
- Column names Enter the columns names from your cpFeeder .txt file. Separate each with a comma. (i.e.- id,product,description)
- Select check box Use LOCAL keyword
- Click GO button to perform the import.
- If you receive any SQL errors you need to refer to the error code and message then read the SQL documentation. You can first try and verify that you do not have any characters in your file that could be interpreted as code functions. Then import it again. This worked for me.
Part 4
PHP Fun!
Now that drama is all done…whew!.. on to the fun stuff. My ultimate goal is to randomly display 5 featured products from my Cafepress shop on my mobile website. I am using PHP to perform my SQL query from the database and output the results on the page. Every new visit to the page will display different products.
I referenced a combination of web tutorials…google is your friend
<?php
include(‘dbconnect.php’);
for($i=$print;$i<=4;$i=$i+1)
{
$query=”SELECT * from ShopData ORDER BY RAND() LIMIT 5″;
$result=mysql_query($query);
$title=mysql_result($result,$i,”title”);
$image_link=mysql_result($result,$i,”image_link”);
$link=mysql_result($result,$i,”link”);
$price=mysql_result($result,$i,”price”);
$prod=”<p style=\”text-align:center\”><b>$title</b><br><a href=\”http://$link\” class=\”accesskey\”><img src=\”$image_link\” width=\”150\” height=\”150\” border=\”0\” class=\”top5\” /></a><br><b>$$price</b></p><br>”;
echo $prod;
}
?>
Explanation:
include(‘dbconnect.php’);
This line of my PHP connects to my database by referencing an external file I created containing SQL code for database connection. (MySQL Documentation)
My external dbconnect.php contained the following syntax:
<?php
$username=”<username>”;
$password=”<password>”;
$database=”<databasename>”;
mysql_connect(“<databasehost>”,$username,$password);
@mysql_select_db($database) or die( “Unable to select database”);
?>
The next line of my php code contains the for LOOP enabling me to enter my php code one type and it will repeat the output on the page for the specified amount of times. In my example below its going to print it 5 times.
for($i=$print;$i<=4;$i=$i+1)
The next line of my code will select data from my table randomly using the SQL RAND() element. I am limiting the random results to 5.
{
$query=”SELECT * from ShopData ORDER BY RAND() LIMIT 5″;
$result=mysql_query($query);
My next line of code identifies my function names and values. The names are identical to my table column names in my SQL database. This is the table data that I want to display on my webpage. You can replace the values according to your table column names.
$title=mysql_result($result,$i,”title”);
$image_link=mysql_result($result,$i,”image_link”);
$link=mysql_result($result,$i,”link”);
$price=mysql_result($result,$i,”price”);
This final string of code is identifying a function called $prod that contains a value of embedded HTML within the PHP, allowing to display the content on the page according to my desired HTML formatting. IMPORTANT: You have to escape ( \ ) any characters that may be interpreted by PHP as a function or possibly ignored.
- Notice before all the quotes for my html attributes have a backslash ( \ ) before them. This escapes the quotes so PHP will not generate an error.
- Notice on the last line I use an ASCII code ($) to display a dollar sign rather than enter the actual dollar sign. PHP will interpret it as a function.
$prod=”<p style=\”text-align:center\”><b>$title</b><br><a href=\”http://$link\” class=\”accesskey\”><img src=\”$image_link\” width=\”150\” height=\”150\” border=\”0\” class=\”top5\” /></a><br><b>$$price</b></p><br>”;
The final line of my PHP code performs the actual “print” of the content on the webpage.
echo $prod;
}
The Result
If you visit my mobile webpage for featured products and refresh it, the products will change with each refresh or new page visit. http://mobi.hexxwear.com/featured.php
Leave a Comment