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 *