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 -> https://github.com/OXIDprojects/google_sitemap
please check for a possible newer version there

 "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 '' -> 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) && 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) -> 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 < $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'           => $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),
'priority'      => '1.0',
'lastmod'       => date("Y-m-d") . 'T' . date("h:i:s") . '+00:00',
'changefreq'    => '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 <> ''
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'           => $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),
'priority'      => '1.0',
'lastmod'       => date("Y-m-d") . 'T' . date("h:i:s") . '+00:00',
'changefreq'    => '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'           => $mod_cnf['siteurl'] . strtolower($sql_row['oxseourl']),
'priority'      => '0.5',
'lastmod'       => $lastmod,
'changefreq'    => '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[] = '
';
foreach($data as $key => $val)
{
$mapdata[] = ''. $val['loc'] .''. $val['priority'] .''. $val['lastmod'] .''. $val['changefreq'] .'';
}
$mapdata[] = '';
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 = ''."\n".'';
for ($i=1;$i<=$pcall;$i++)
{
$loc = ''.$mod_cnf['siteurl'].$mod_cnf['filename'].$i.'.xml.gz';
$last = ''.date("Y-m-d").'T'.date("H:i:s").'+00:00';
$sitemaps[] = ''.$loc.$last.'';
}
$maps = $smindex . "\n" . implode("\n",$sitemaps);
$sitemapindex = $maps . "\n";
// write to file
@file_put_contents($mod_cnf['filename'].'.xml',$sitemapindex);
return;
}
?>


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *