Google Merchant How To Setup A Live XML Data Feed With MySql

Setting up a live data feed for Google Merchant (Google Shopping) can be tricky if you don’t know how to set it up. I could not find one topic that covered from a to z how to set up a Google Merchant Feed. This article will cover how to c

Setting up a live data feed for Google Merchant (Google Shopping) can be tricky if you don’t know how to set it up. I could not find one topic that covered from a to z how to set up a Google Merchant Feed. This article will cover how to create a database, retrieve information using PHP and automatically generate a Google Merchant XML feed.

Requirements :

• basic knowledge of PHP
• basic knowledge of MySQL

First go and get yourself a Google Shopping Account and fill out all your details for registration (It’s a very simple process). You can also connect  your Google Adwords account to your Google Shopping account.

Now that you have already created an account lets delve on in-depth information on how to set-up a live data feed. If you already have a database with products please ignore the next part and go to required fields and explanation.

Step One : Setting up a MySQL database

Below I have created a simple MySQL code for you to use the bare minimum Google Shopping would need, now this means it’s not covering products such as clothing, if you need more details about it, please check the xml example here.

• Login to your MySQL CMS system, most likely phpMyAdmin
• Go to the tab SQL
• Copy and Paste the below code into the text area and click on Go

CREATE TABLE products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
productID VARCHAR(32),
brand VARCHAR(32),
price VARCHAR(12),
title VARCHAR(225),
description TEXT,
pageLink TEXT,
stock VARCHAR(32),
gmCategory TEXT,
productCondition VARCHAR(50),
image VARCHAR(225),
shippingCountry VARCHAR(3),
shippingService VARCHAR(1),
shippingPrice VARCHAR(10),
GTIN VARCHAR(15),
MPN VARCHAR(50),
productType TEXT
);

Required fields and explanation:
If you need an explanation to what all these fields are, simply look at the support files here. Within the xml file as an example you will see what the minimum requirements are for publishing products into Google Shop.

Insert some data as a test example

Step Two : Generating our XML file

Below is an example I have used for generating an example file. If you have imported the table from step one, than the php code below will generate the correct information. If not than most likely you want to change the fetched arrays within the script.

php
// mysql connection
$conn=mysqli_connect("localhost","database","password","database_name");
// Check connection
if (mysqli_connect_errno($conn))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Collect data in query
$q = mysqli_query($conn,"SELECT * FROM products ");

// Set the xml header
header("Content-type: text/xml");

// Echo out all the details
echo 'smileywar.com
		http://www.smileywar.comGoogle Shopping Feed';

// while loop this will cycle through the products and echo out all the variables
while($r = mysqli_fetch_array($q))
{
// collect all variables
$title =$r['title'];
$link =$r['pageLink'];
$description =$r['description'];
$productID =$r['productID'];
$condition =$r['condition'];
$price =$r['price'];
$stock =$r['stock'];
$image ='http://www.smileywar.com/_images/GoogleMerchant/'.$productID.'.jpg';
$category ='Vehicles & Parts > Vehicle Parts & Accessories > Motor Vehicle Exterior';
$gtin =$r['GTIN'];
$mpn =$r['MPN'];

// output all variables into the correct google tags
echo "$title
		$link
$description
<google_product_category>$category
$productID
$condition
$price GBP
$availability
<image_link>$image

UK
Standard
5.95 GBP

$gtin
Thule
$mpn
<product_type>$category
";
}
echo "";
?></product_type></image_link></google_product_category>

Save this file as index.php and upload it to a folder such as http://www.mysite.com/GoogleShoppingFeed remember to htaccess this folder so that your data can’t be downloaded by others. Browse to your folder, login and now you can see your xml data.

In Google Shopping Centre navigate to data feed, and enter in a name for example ShoppingFeed.xml and click on continue. In the second column under upload, click on Monthly/Weekly upload, here you enter in your URL, username and password for which you have added to your htaccess file, select the recurrence of your data download, for example every week on a Monday.

And that’s it. All done, now you have set up a live data feed as to which you only have to update in your server’s database and not multiple xml or text files.

Once you have finished your setup, you might be interested in how to optimize your feed in generating the highest ranks in Google Shopping and how to setup a custom Google Merchant product.