Skip to content

Instantly share code, notes, and snippets.

@AlexMcowkin

AlexMcowkin/googlefeed-1.php

Created August 6, 2015 11:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AlexMcowkin/ea1618c16e4958f9b682 to your computer and use it in GitHub Desktop.
Save AlexMcowkin/ea1618c16e4958f9b682 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
googlefeed-1.php
<?php
try
{
switch (__DIR__) {
case 'D:\open-server500\OpenServer\domains\sitename.loc':
$dbh = new PDO("mysql:host=127.0.0.1; dbname=sitename_db", 'root', '');
break;
default:
$dbh = new PDO("mysql:host=localhost; dbname=dbname", 'dbusr', 'dbpwd');
break;
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
$xmlString = '<?xml version="1.0" encoding="UTF-8"?>
<rss version ="2.0" xmlns:g="http://base.google.com/ns/1.0">
<channel>
<title>Air Purifiers &amp; HEPA Air Purifier Filters - Home Air Cleaners</title>
<description>Air Purifiers &amp; HEPA Air Purifier Filters, home and commercial Air cleaners. Buy Air Purifiers for Allergies, Allergy, Pet Hair, Pet Odor and more.</description>
<link>http://www.sitename.com/</link>'."\n";
$sql = "SELECT product_id, category_id FROM product_to_category WHERE category_id IN ('38', '40', '49', '63', '87')";
$query = $dbh->query($sql);
while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
$sql_1 = "SELECT image, price, model FROM product WHERE product_id = '".$row['product_id']."' AND status = 1 AND quantity > 0 LIMIT 1";
$query_1 = $dbh->query($sql_1);
$result_1 = $query_1->fetch(PDO::FETCH_ASSOC);
if($result_1)
{
$xmlString .= '<item>'."\n";
$_model = ucwords(strtolower(trim($result_1['model'])));
$_model = str_replace(" ", "", $_model);
$_model = str_replace("+", "", $_model);
$_model = str_replace("-", "", $_model);
$_model = str_replace("'", "", $_model);
$_model = str_replace('"', "", $_model);
$xmlString .= '<g:id>'.$_model.'</g:id>'."\n";
$xmlString .= '<g:mpn>'.$_model.'</g:mpn>'."\n";
$xmlString .= '<g:price>'.substr($result_1['price'], 0, -2).' USD</g:price>'."\n";
switch($_model)
{
case 'HealthmateHm400' :
$xmlString .= '<g:image_link>http://www.sitename.com/googlefeed/images/HealthmateHm400.jpg</g:image_link>'."\n";
break;
default :
$xmlString .= '<g:image_link>http://www.sitename.com/image/'.$result_1['image'].'</g:image_link>'."\n";
break;
}
$xmlString .= '<g:condition>new</g:condition>'."\n";
$xmlString .= '<g:availability>in stock</g:availability>'."\n";
// $xmlString .= '<g:identifier_exists>FALSE</g:identifier_exists>'."\n";
/***BRAND***/
if ($row['category_id'] == 38) { $xmlString .= '<g:brand>Airfree</g:brand>'."\n";}
elseif ($row['category_id'] == 87) { $xmlString .= '<g:brand>Zen Water Systems</g:brand>'."\n";}
elseif($row['category_id'] == 63) {$xmlString .= '<g:brand>Surround Air</g:brand>'."\n";}
else {$xmlString .= '<g:brand>Austin Air</g:brand>'."\n";}
/***CATEGORY***/
if(($row['category_id'] == 38) OR ($row['category_id'] == 40) OR ($row['category_id'] == 63))
{
$xmlString .= '<g:google_product_category>Home &amp; Garden &gt; Household Appliance Accessories &gt; Air Purifier Accessories</g:google_product_category>'."\n";
}
elseif ($row['category_id'] == 87)
{
$xmlString .= '<g:google_product_category>Hardware &gt; Plumbing &gt; Water Dispensing &amp; Filtration &gt; Water Filtration Accessories</g:google_product_category>'."\n";
}
else
{
$xmlString .= '<g:google_product_category>Home &amp; Garden &gt; Household Appliance Accessories &gt; Air Purifier Accessories &gt; Air Purifier Filters</g:google_product_category>'."\n";
}
/***TYPE***/
if ($row['category_id'] == 87)
{
$xmlString .= '<g:product_type>Home &amp; Garden &gt; Household Appliance Accessories &gt; Air Purifier Accessories &gt; Air Purifier Filters</g:product_type>'."\n";
}
else
{
$xmlString .= '<g:product_type>Home &amp; Garden &gt; Household Appliance Accessories &gt; Air Conditioner Accessories</g:product_type>'."\n";
}
$sql_2 = "SELECT name, description FROM product_description WHERE product_id = {$row['product_id']} LIMIT 1";
$query_2 = $dbh->query($sql_2);
$result_2 = $query_2->fetch(PDO::FETCH_ASSOC);
$xmlString .= '<g:title>'.ucwords(strtolower($result_2['name'])).'</g:title>'."\n";
$_descr ='';
$_descr = $result_2['description'];
$_descr = html_entity_decode($_descr);
$_descr = strip_tags($_descr);
$_descr = str_replace('&nbsp;', ' ', $_descr);
$_descr = str_replace('&trade;', '', $_descr);
$_descr = str_replace('&rdquo;', '"', $_descr);
$_descr = str_replace('&acirc;', ' ', $_descr);
$_descr = str_replace('&bdquo;', '"', $_descr);
$_descr = str_replace('&quot;', '"', $_descr);
$_descr = str_replace('&cent;', ' ', $_descr);
$_descr = str_replace('&deg;', '', $_descr);
$_descr = str_replace('&#39;', '', $_descr);
$_descr = str_replace('&frac12;', '', $_descr);
$_descr = str_replace('&ordm;', '', $_descr);
$_descr = str_replace('&rsquo;', '', $_descr);
$_descr = str_replace('&reg;', '', $_descr);
$_descr = str_replace('&ndash;', '', $_descr);
$_descr = str_replace('&ordm;', '', $_descr);
$_descr = str_replace(array("\r\n", "\r", "\n"), "", $_descr);
$_descr = preg_replace('/\t+/', '', $_descr);
$_descr = substr($_descr, 0, 1000);
$_descr = rtrim($_descr, "!,.-?");
$_descr = substr($_descr, 0, strrpos($_descr, ' '));
$xmlString .= '<g:description>'.trim($_descr).'</g:description>'."\n";
$sql_3 = "SELECT image FROM product_image WHERE product_id = {$row['product_id']}";
$query_3 = $dbh->query($sql_3);
while ($row_3 = $query_3->fetch(PDO::FETCH_ASSOC))
{
$xmlString .= '<g:additional_image_link>http://www.sitename.com/image/'.$row_3['image'].'</g:additional_image_link>'."\n";
}
$sql_4 = "SELECT keyword FROM url_alias WHERE query = 'product_id=".$row['product_id']."' LIMIT 1";
$query_4 = $dbh->query($sql_4);
$result_4 = $query_4->fetch(PDO::FETCH_ASSOC);
if ($row['category_id'] == 38) { $xmlString .= '<g:link>http://www.sitename.com/air-purifiers/airfree-air-purifiers/'.$result_4['keyword'].'</g:link>'."\n";}
elseif ($row['category_id'] == 40) { $xmlString .= '<g:link>http://www.sitename.com/air-purifiers/austin-air-purifiers/'.$result_4['keyword'].'</g:link>'."\n";}
elseif ($row['category_id'] == 49) { $xmlString .= '<g:link>http://www.sitename.com/air-filters/austin-air-filters/'.$result_4['keyword'].'</g:link>'."\n";}
elseif ($row['category_id'] == 87) { $xmlString .= '<g:link>http://www.sitename.com/Water-Ionizer-Purifier/'.$result_4['keyword'].'</g:link>'."\n";}
elseif ($row['category_id'] == 63) { $xmlString .= '<g:link>http://www.sitename.com/air-purifiers/surround-air-purifiers/'.$result_4['keyword'].'</g:link>'."\n";}
$xmlString .= '</item>'."\n";
}
}
$xmlString .= '</channel>'."\n";
$xmlString .= '</rss>';
$file_name = 'googlefeed_ca.xml';
$file = fopen($file_name,"w");
fwrite($file,$xmlString);
fclose($file);
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment