How To Create A Google Shopping Feed In PHP

19th Jan 2017

We’re going to go into some code today, which will only apply to some of you. If you’re running a shop built on some custom PHP, you might have wondered how to simply create a Google Shopping Feed for Merchant Center. Today I’ll walk you through how to do that. This tutorial is specific to UK stores, so if you’re from the USA you may need to add a section on tax. Feel free to get in touch if you’d like some assistance there.

First you’ll want to create a new PHP file on your server somewhere. Something like DOMAIN.com/feeds/google-shopping-feed.php will be fine. This will be where we’ll add our code to create the feed, as well as become the URL we’ll share with Google to give them our product data.

The first few lines of our code are very simple, we’re just going to set up some variables we’ll need later. From now on, all anything in bold in the code excerpts are pieces you should edit:

$shop_name = "Example Shop";
$shop_link = "
https://exampleshop.com";

Next we need to connect to our database. I’m assuming a MySQL database for this tutorial, so you’ll need to edit slightly if you’re using another type of database. Also, you almost definitely shouldn’t be using the example line to connect to your database – use whichever secure function you’ve already created for your shop:

$db = new mysqli('server', 'username', 'password', 'database');

Now we use SQL to get our products from the database, as well as set up an empty array for the products which we’ll later loop through to create our feed:

$sql = "SELECT * FROM products";
$result = $db->query($sql);

$feed_products = [];

Now we loop through all those products, and set a Google Feed friendly attribute name to each of the pieces of your product database. The pieces in red here will be the column names of your product table. If you find that you don’t have a column made for one of the sections, you will probably need to amend your product table.

//LOOP THROUGH PRODUCTS
while ( $product = mysqli_fetch_assoc($result)){

//CREATE EMPTY ARRAY FOR GOOGLE-FRIENDLY INFO
$gf_product = [];

//FLAGS FOR LATER
$gf_product['is_clothing'] = $product['
clothing']; //set True or False, depending on whether product is clothing
$gf_product['is_on_sale'] = $product['
sale']; //set True or False depending on whether product is on sale

//feed attributes
$gf_product['g:id'] = $product['
id'];
$gf_product['g:sku'] = $product['
sku'];
$gf_product['g:title'] = $product['
product_name'];
$gf_product['g:description'] = $product['
meta_description'];
$gf_product['g:link'] = $product['
url'];
$gf_product['g:image_link'] = $product['
image'];
$gf_product['g:availability'] = $product['
is_stock'];
$gf_product['g:price'] = $product['
price'];
$gf_product['g:google_product_category'] = $product['
google_product_category'];
$gf_product['g:brand'] = $product['
brand'];
$gf_product['g:gtin'] = $product['
gtin'];
$gf_product['g:mpn'] = $product['
mpn'];
if (($gf_product['g:gtin'] == "") && ($gf_product['g:mpn'] == "")) { $gf_product['g:identifier_exists'] = "no"; };
$gf_product['g:condition'] = $product['
condition']; //must be NEW or USED
//remove this IF block if you don't sell any clothing
if ($gf_product['
is_clothing']) {
$gf_product['g:age_group'] = $product['
age_group']; //newborn/infant/toddle/kids/adult
$gf_product['g:color'] = $product['
color'];
$gf_product['g:gender'] = $product['
gender'];
$gf_product['g:size'] = $product['
size'];
}
if ($gf_product['is_on_sale']) {
$gf_product['g:sale_price'] = $product['
offer_price'];
$gf_product['g:sale_price_effective_date'] = $product['
sale_startdate']." ".$product['sale_enddate'];
}

$feed_products[] = $gf_product;
}

The final code block takes all that information and formats it into an XML file which Google can read from to get your product information. You don’t need to change anything here:

//close the database connection
$db->close();

$doc = new DOMDocument('1.0', 'UTF-8');

$xmlRoot = $doc->createElement("rss");
$xmlRoot = $doc->appendChild($xmlRoot);
$xmlRoot->setAttribute('version', '2.0');
$xmlRoot->setAttributeNS('http://www.w3.org/2000/xmlns/', 'xmlns:g', "http://base.google.com/ns/1.0");

$channelNode = $xmlRoot->appendChild($doc->createElement('channel'));
$channelNode->appendChild($doc->createElement('title', $shop_name));
$channelNode->appendChild($doc->createElement('link', $shop_link));

foreach ($feed_products as $product) {
$itemNode = $channelNode->appendChild($doc->createElement('item'));
foreach($product as $key=>$value) {
if ($value != "") {
if (is_array($product[$key])) {
$subItemNode = $itemNode->appendChild($doc->createElement($key));
foreach($product[$key] as $key2=>$value2){
$subItemNode->appendChild($doc->createElement($key2))->appendChild($doc->createTextNode($value2));
}
} else {
$itemNode->appendChild($doc->createElement($key))->appendChild($doc->createTextNode($value));
}

} else {

$itemNode->appendChild($doc->createElement($key));
}

}
}


$doc->formatOutput = true;
echo $doc->saveXML();

And that’s it. Save the file, go to the URL you saved this file at and you’ll see a very messy dump of all of your product data. More importantly, head to the Merchant Center and set this URL up as your feed and you should find that it passes. Please do get in touch if you have any trouble.

Here’s the full code if you want to copy and paste ready to make your changes:

//SET SHOP VARIABLES
$shop_name = "
Example Shop";
$shop_link = "
https://exampleshop.com";

//CONNECT TO DATABASE
$db = new mysqli('
server', 'username', 'password', 'database');

//GET PRODUCTS FROM DATABASE
$sql = "
SELECT * FROM products";
$result = $db->query($sql);

$feed_products = [];

//LOOP THROUGH PRODUCTS
while ( $product = mysqli_fetch_assoc($result)){

//CREATE EMPTY ARRAY FOR GOOGLE-FRIENDLY INFO
$gf_product = [];

//FLAGS FOR LATER
$gf_product['is_clothing'] = $product['
clothing']; //set True or False, depending on whether product is clothing
$gf_product['is_on_sale'] = $product['
sale']; //set True or False depending on whether product is on sale

//feed attributes
$gf_product['g:id'] = $product['
id'];
$gf_product['g:sku'] = $product['
sku'];
$gf_product['g:title'] = $product['
product_name'];
$gf_product['g:description'] = $product['
meta_description'];
$gf_product['g:link'] = $product['
url'];
$gf_product['g:image_link'] = $product['
image'];
$gf_product['g:availability'] = $product['
is_stock'];
$gf_product['g:price'] = $product['
price'];
$gf_product['g:google_product_category'] = $product['
google_product_category'];
$gf_product['g:brand'] = $product['
brand'];
$gf_product['g:gtin'] = $product['
gtin'];
$gf_product['g:mpn'] = $product['
mpn'];
if (($gf_product['g:gtin'] == "") && ($gf_product['g:mpn'] == "")) { $gf_product['g:identifier_exists'] = "no"; };
$gf_product['g:condition'] = $product['
condition']; //must be NEW or USED
//remove this IF block if you don't sell any clothing
if ($gf_product['
is_clothing']) {
$gf_product['g:age_group'] = $product['
age_group']; //newborn/infant/toddle/kids/adult
$gf_product['g:color'] = $product['
color'];
$gf_product['g:gender'] = $product['
gender'];
$gf_product['g:size'] = $product['
size'];
}
if ($gf_product['is_on_sale']) {
$gf_product['g:sale_price'] = $product['
offer_price'];
$gf_product['g:sale_price_effective_date'] = $product['
sale_startdate']." ".$product['sale_enddate'];
}

$feed_products[] = $gf_product;
}

//CREATE XML
//close the database connection
$db->close();

$doc = new DOMDocument('1.0', 'UTF-8');

$xmlRoot = $doc->createElement("rss");
$xmlRoot = $doc->appendChild($xmlRoot);
$xmlRoot->setAttribute('version', '2.0');
$xmlRoot->setAttributeNS('http://www.w3.org/2000/xmlns/', 'xmlns:g', "http://base.google.com/ns/1.0");

$channelNode = $xmlRoot->appendChild($doc->createElement('channel'));
$channelNode->appendChild($doc->createElement('title', $shop_name));
$channelNode->appendChild($doc->createElement('link', $shop_link));

foreach ($feed_products as $product) {
$itemNode = $channelNode->appendChild($doc->createElement('item'));
foreach($product as $key=>$value) {
if ($value != "") {
if (is_array($product[$key])) {
$subItemNode = $itemNode->appendChild($doc->createElement($key));
foreach($product[$key] as $key2=>$value2){
$subItemNode->appendChild($doc->createElement($key2))->appendChild($doc->createTextNode($value2));
}
} else {
$itemNode->appendChild($doc->createElement($key))->appendChild($doc->createTextNode($value));
}

} else {

$itemNode->appendChild($doc->createElement($key));
}

}
}


$doc->formatOutput = true;
echo $doc->saveXML();

Related Reading

© Jason Dilworth 2024
Contact