Tutorials/en/google xml-sitemap

Copy code and create a file "google_sitemap_xml.php" - put in your details for URL and database - transfer to rootfolder of your shop.

Thanks to DIATOM for the basic script and to nullzehn, Piengie and uppaffrath for their modifications.

Thread in OXID Community forum: http://www.oxid-esales.com/forum/showthread.php?t=1933

Should work at least for shopversions 4.4 and 4.5

also to be found at github -&gt; https://github.com/OXIDprojects/google_sitemap please check for a possible newer version there &lt;?php /**
 * Google Sitemap Xml v.0.1
 * oxid ESales - SEO
 * by DIATOM Internet &amp; Medien GmbH
 * c: 27.07.2009 | u: 27.07.2009
 * modified by: 010|nullzehn - digitale medien @2010-01-23:
 * - creating multiple sitemaps for large shops
 * - compress sitemaps (gzip)
 * - recalls self to avoid memory breaks
 * - run as a cronjob or manually in cli
 * you can call the script by crontab: "googlesitemap.sh -c 1"
 * (or without params if you like)
 * therefore add "#! /usr/bin/php -q " before the &lt;?php in first line and
 * save as googlesitemap.sh
 * install
 * (1) insert your paths and data below //configuration
 * (2) upload file to your webspace
 * (3) adjust chmod if needed
 * (4) open with your browser
 * (5) open sitemap.xml and check content
 * transfer to google
 * (1) open www.google.com/webmasters/tools
 * (2) log in with your account
 * (3) choose website
 * (4) "XML-Sitemaps" -&gt; "add Sitemap"
 * (5) specify URL of your sitemap.xml
 * (4) open with your browser
 * (5) open sitemap.xml and check content
 * transfer to google
 * (1) open www.google.com/webmasters/tools
 * (2) log in with your account
 * (3) choose website
 * (4) "XML-Sitemaps" -&gt; "add Sitemap"
 * (5) specify URL of your sitemap.xml
 * (4) "XML-Sitemaps" -&gt; "add Sitemap"
 * (5) specify URL of your sitemap.xml

// init $mod_cnf                       = array; $error                         = array; $xmlInsert                     = array; $xmlList                       = array; $xmlList_cat                   = array; $xmlList_cms                   = array; $xmlList_prod                  = array;

// configuration $mod_cnf['filepath']           = './';                          // if google_sitemap_xml.php and sitemaps.xml.gz are in same directory no path just '' -&gt; else path from php to xml $mod_cnf['filename']           = 'sitemap-de';                  // basename for sitemaps $mod_cnf['offset']             = 20000;                         // how many product-urls in each sitemap? (max. allowed: 50.000 urls (total, with cats and cms) &amp;&amp; max. filesize: 10Mb (uncompressed!)) $mod_cnf['siteurl']            = 'http://www.my-domain.tld/';   // shop url (with ending slash!) $mod_cnf['dbhost']             = "localhost";                   // dbhost normally localhost $mod_cnf['dbname']             = "insert db_name";              // dbname $mod_cnf['dbuser']             = "insert db_user";              // dbuser $mod_cnf['dbpass']             = "insert db_password";          // dbpass

// which run?: script calls with '-c [n]' // first run (without params) -&gt; call #1 if ("-c" != $_SERVER['argv'][1]) {   $pcall = 1; } else { $pcall = $_SERVER['argv'][2]; if (!preg_match("/[\d]/",$pcall)) {       die("Illegal call.\n"); } }

// db connection $sqlConnect = mysql_connect(   $mod_cnf['dbhost'],    $mod_cnf['dbuser'],    $mod_cnf['dbpass']) OR die('error connecting to database.'); mysql_select_db(   $mod_cnf['dbname'],    $sqlConnect) OR die('error selecting table.');

//** get number of needed script-calls, based on active items with valid seo-url. cms and categories will be added to first sitemap automatically. $cntCalls = ceil(getCountScriptCalls / $mod_cnf['offset']);

// store cms- and category-data only at first call, further calls are products only if (1 == $pcall) {   // get cms data from shop - only at first script-run! (-c 1) $xmlList_cms = getCmsSite; // get all categories $xmlList_cat = getCategories; }

// get products (with offset) $xmlList_prod = getProducts($pcall);

// build xml-data and output $xmlList = array_merge($xmlList_prod, $xmlList_cat, $xmlList_cms);

// create sitemap $sitemapdata = createSitemap($xmlList); $smfile = createXmlFile($sitemapdata);

// compress sitemap compressSitemapFile($smfile);

// create global sitemaps-index-file (watch sitemaps.org for more infos..) createSitemapIndex;

//** RECALL SCRIPT if ($pcall &lt; $cntCalls) {   // memory seems to hold list-array-values, maybe depends on local environment unset($xmlList,$xmlList_cat,$xmlList_cms,$xmlList_prod); // call itself $exec = './googlesitemap.sh -c '.($pcall+1); //echo "\n".$exec."\n"; //debug system($exec); exit(0); }

//** exit all //echo "\nready.\n";   // debug exit(0);

// ** FUNCTIONS

/** get all active and visible categories from database * @return array */ function getCategories {   global $mod_cnf; $list = array; $sql = "SELECT                seo.oxseourl            FROM                oxcategories as oxcats            LEFT JOIN                oxseo as seo ON (oxcats.oxid=seo.oxobjectid)            WHERE                oxcats.oxactive = 1 AND                oxcats.oxhidden = 0 AND                seo.oxtype='oxcategory' AND                seo.oxstdurl NOT LIKE ('%pgNr=%') AND                seo.oxlang = 0            GROUP BY                oxcats.oxid;"; $sql_query = mysql_query($sql); while ($sql_row = mysql_fetch_array($sql_query)) {       $list[] = array(            'loc'           =&gt; $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),            'priority'      =&gt; '1.0',            'lastmod'       =&gt; date("Y-m-d") . 'T' . date("h:i:s") . '+00:00',            'changefreq'    =&gt; 'weekly',        ); }   mysql_free_result($sql_query); return $list; }

/** get active cms content from database * @return array */ function getCmsSite {   global $mod_cnf; $list = array; $sql = "SELECT               seo.oxseourl            FROM                oxcontents as content            LEFT JOIN                oxseo as seo ON (content.oxid=seo.oxobjectid)            WHERE                content.oxactive = 1 AND                content.oxfolder =                 AND seo.oxseourl &lt;&gt;                 AND seo.oxseourl NOT LIKE ('%META%')                AND seo.oxlang = 0            GROUP BY                content.oxid;"; $sql_query = mysql_query($sql); while ($sql_row = mysql_fetch_array($sql_query)) {       $list[] = array(            'loc'           =&gt; $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),            'priority'      =&gt; '1.0',            'lastmod'       =&gt; date("Y-m-d") . 'T' . date("h:i:s") . '+00:00',            'changefreq'    =&gt; 'weekly',        ); }   mysql_free_result($sql_query); return $list; }

/** get active products from database with offset * @return array */ function getProducts($limit) {   global $mod_cnf; $list = array;

// calculate offset $start = $mod_cnf['offset']; if (1 == $limit) {       $end = 0; }   else { $end = (($limit-1) * $mod_cnf['offset']) - 1; }   $sql = "SELECT                oxart.oxtimestamp,                seo.oxseourl            FROM                oxarticles as oxart            LEFT JOIN oxobject2category as oxobj2cat                ON (oxobj2cat.oxobjectid = oxart.oxid)            LEFT JOIN oxcategories as oxcat                ON (oxcat.oxid = oxobj2cat.oxcatnid)            LEFT JOIN oxseo as seo                ON (oxart.oxid = seo.oxobjectid)            WHERE                oxart.oxactive = 1 AND                oxcat.oxactive = 1 AND                oxcat.oxhidden = 0 AND                seo.oxlang = 0 AND                seo.oxtype='oxarticle' AND                seo.oxstdurl LIKE ('%cnid=%')            GROUP BY                oxart.oxid            LIMIT ".$start." OFFSET ".$end.";"; $sql_query = mysql_query($sql); while ($sql_row = mysql_fetch_array($sql_query)) {       $lastmod = $sql_row['oxtimestamp']; if ("0000-00-00 00:00:00" == $lastmod) { $lastmod = date("Y-m-d"). 'T'. date("h:i:s"). '+00:00'; } $lastmod = date("Y-m-d"). 'T'. date("h:i:s"). '+00:00'; $list[] = array(           'loc'           =&gt; $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),            'priority'      =&gt; '0.5',            'lastmod'       =&gt; $lastmod,            'changefreq'    =&gt; 'daily',        ); }   mysql_free_result($sql_query); return $list; }

/** get total number of 'seo-active' products in shop * @return integer */ function getCountScriptCalls {   global $mod_cnf; $sql = "SELECT               oxart.oxid            FROM                oxarticles as oxart            LEFT JOIN oxobject2category as oxobj2cat                ON (oxobj2cat.oxobjectid = oxart.oxid)            LEFT JOIN oxcategories as oxcat                ON (oxcat.oxid = oxobj2cat.oxcatnid)            LEFT JOIN oxseo as seo                ON (oxart.oxid = seo.oxobjectid)            WHERE                oxart.oxactive = 1 AND                oxcat.oxactive = 1 AND                oxcat.oxhidden = 0 AND                seo.oxlang = 0 AND                seo.oxtype='oxarticle'            GROUP BY                oxart.oxid;"; $query = mysql_query($sql); return mysql_num_rows($query); }

/** creates xml data / sitemap-content * @return array */ function createSitemap($data) {   global $mod_cnf; $mapdata[] = '&lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"&gt;';

foreach($data as $key =&gt; $val) {       $mapdata[] = '&lt;url&gt;&lt;loc&gt;'. $val['loc'] .'&lt;/loc&gt;&lt;priority&gt;'. $val['priority'] .'&lt;/priority&gt;&lt;lastmod&gt;'. $val['lastmod'] .'&lt;/lastmod&gt;&lt;changefreq&gt;'. $val['changefreq'] .'&lt;/changefreq&gt;&lt;/url&gt;'; }   $mapdata[] = '&lt;/urlset&gt;'; return $mapdata; }

/** stores xml-file to filesystem * @return string */ function createXmlFile($smdata) {   global $mod_cnf,$pcall; $fname = $mod_cnf['filepath'].$mod_cnf['filename'].$pcall.".xml"; $fp = fopen($fname, "w+"); fwrite($fp, implode("\n", $smdata)); fclose($fp); return $fname; }

/** compress sitemap-file: new file is sitemap.gz * @return void */ function compressSitemapFile($fname) {   if (file_exists($fname)) {       system("gzip -q -9 ".$fname); }   return; }

/** append new sitemap to sitemap index * @return void */ function createSitemapIndex {   global $pcall,$mod_cnf; $sitemaps = array; $maps = array; // build xml-content $smindex = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;'."\n".'&lt;sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"&gt;'; for ($i=1;$i&lt;=$pcall;$i++) {       $loc = '&lt;loc&gt;'.$mod_cnf['siteurl'].$mod_cnf['filename'].$i.'.xml.gz&lt;/loc&gt;'; $last = '&lt;lastmod&gt;'.date("Y-m-d").'T'.date("H:i:s").'+00:00&lt;/lastmod&gt;'; $sitemaps[] = '&lt;sitemap&gt;'.$loc.$last.'&lt;/sitemap&gt;'; }   $maps = $smindex. "\n". implode("\n",$sitemaps); $sitemapindex = $maps. "\n&lt;/sitemapindex&gt;"; // write to file @file_put_contents($mod_cnf['filename'].'.xml',$sitemapindex); return; }

?&gt;