Jump to content
Jack_mcs

Google XML Sitemap SEO

Recommended Posts

Hi Jack,

Just wondering installing sitemap on a 2.3.3.4 just getting this SQL error

 

SQL query:

INSERT INTO configuration( configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added, use_function )

VALUES (

NULL , 'Exclude these pages', 'GOOGLE_XML_SITEMAP_EXCLUDE_PAGES', '', 'Add these pages to the built-in file exclude list. This will prevent the links from being added to the pages site map.', '289', '20', NULL , now( ) , NULL

);

 

 

MySQL said:

#1048 - Column 'set_function' cannot be null

 

 

any ideas

 

Regards

Joli

 

PS: This seems to be the is the problem

 

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added, use_function)

VALUES (NULL, 'Exclude these pages', 'GOOGLE_XML_SITEMAP_EXCLUDE_PAGES', '', 'Add these pages to the built-in file exclude list. This will prevent the links from being added to the pages site map.', '289', '20', NULL, now(), NULL);

Edited by joli1811

To improve is to change; to be perfect is to change often.

 

Share this post


Link to post
Share on other sites

Try

INSERT INTO configuration( configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added, use_function )
VALUES (
'Exclude these pages', 'GOOGLE_XML_SITEMAP_EXCLUDE_PAGES', '', 'Add these pages to the built-in file exclude list. This will prevent the links from being added to the pages site map.', '289', '20', NULL , now( ) , NULL
);

this

Share this post


Link to post
Share on other sites

Hi,

 

Got it to generate succesfully on a demo (upgraded from older version osc so maybe my mistake ) by changing the database field to

 

set_function varchar(255) utf8_unicode_ci Yes NULL

 

so going to drop the new sitemaps tables and see what happens !!

With the new query

 

Just a sec

Edited by joli1811

To improve is to change; to be perfect is to change often.

 

Share this post


Link to post
Share on other sites

Seems is my problem for some reason the data base I was installing in shows

11 set_function text utf8_unicode_ci No None

 

 

should be on a standard 2.3.3.4 install

11 set_function varchar(255) utf8_unicode_ci Yes NULL

 

 

no idea how that slipped in maybe some addon will have to retrace my steps started with an original 2.3.3.4

 

ok thanks for the quick reply

 

guess I have some work to do checking now :thumbsup:

Regards

John


To improve is to change; to be perfect is to change often.

 

Share this post


Link to post
Share on other sites

I've been using this add on since November & until today it's been working fine with no probs whatsoever.

 

Over the last week or so I've been adding a lot of products (5000 or more at a time), and on a daily basis I've updated the sitemaps.

 

Today however it's not updated and I'm getting the result as below.

 

I've tried the alternate setting, checked the file permissions.

 

If anyone can help I will be eternally grateful

 

Opening /home/content/15/xxxxxxxxxxxxx/html/catalog/sitemapproducts.xml

FS_CAT /home/content/15/xxxxxxxxxx/html/catalog/

Server http://boutiquefeeldistribution.com

Save Path /home/content/15/xxxxxxxxxxxxx/html/catalog/

WS_CAT /catalog/

Write /home/content/15/xxxxxxxxxxxx/html/catalog/sitemapproducts.xml

Share this post


Link to post
Share on other sites

You don't show an error in what you posted. If that is all you see, then the most likely problem is that the server is timing out before the script can complete. Your error log should show that if that is the case. If that is it, you need to ask your host to increase the max execution time. If you are on a shared server they probably won't do it.

Share this post


Link to post
Share on other sites

I have installed XML sitemap v1.5.5 for my test OSC v2.3.4 shop. Also using Ultimate_Seo_Urls_5_PRO_r205 (by FM5). Getting this error. I have tried suggestion given to Ben above but failed.

 

Opening /sitemapproducts.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /

WS_CAT /bridal-lengha/catalog/

 

 

Warning: fopen(/sitemapproducts.xml) [function.fopen]: failed to open stream: Permission denied in /home/fashno5/y2rishta.com/bridal-lengha/catalog/googlesitemap/sitemap.class_Alternate.php on line 353

 

ERROR: Google Product Sitemap Generation FAILED!

 

Opening /sitemapcategories.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /

WS_CAT /bridal-lengha/catalog/

 

 

Warning: fopen(/sitemapcategories.xml) [function.fopen]: failed to open stream: Permission denied in /home/fashno5/y2rishta.com/bridal-lengha/catalog/googlesitemap/sitemap.class_Alternate.php on line 353

 

ERROR: Google Category Sitemap Generation FAILED!

 

Opening /sitemapindex.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /

WS_CAT /bridal-lengha/catalog/

 

 

Warning: fopen(/sitemapindex.xml) [function.fopen]: failed to open stream: Permission denied in /home/fashno5/y2rishta.com/bridal-lengha/catalog/googlesitemap/sitemap.class_Alternate.php on line 353

 

ERROR: Google Sitemap Index Generation FAILED!

 

Array

(

[QUERY] => Array

(

[PRODUCTS] => Array

(

[sTATUS] => success

[NUM_ROWS] => 166

)

 

[CATEOGRY] => Array

(

[sTATUS] => success

[NUM_ROWS] => 5

)

 

)

 

[sAVE_FILE_XML] => Array

(

[0] => Array

(

[file] => /sitemapproducts.xml

[status] => failure

[file_exists] => false

)

 

[1] => Array

(

[file] => /sitemapcategories.xml

[status] => failure

[file_exists] => false

)

 

[2] => Array

(

[file] => /sitemapindex.xml

[status] => failure

[file_exists] => false

)

 

)

 

)

Share this post


Link to post
Share on other sites

This addon isn't compatible with that rewriter. But it looks like the problem you are having is that you didn't set the permissions correctly on the sitemap files. See the instructions for that. Try turning SEO 5 off to see if the sitemaps will work. if they won't, then that is most likely the problem.

Share this post


Link to post
Share on other sites

@@Jack_mcs

I don't know if this has been mentioned before or I've used the wrong keywords to search. But when I run the script for the sitemaps they all are created successfully however the sitemapindex.xml renders links for sitemapproducts1.xml, sitemapproducts2.xml, and sitemapproducts3.xml. At the moment, I have only like 1200 products so the 3 mentioned product sitemaps aren't needed yet. This is causing Google to look for them when I submit the sitemapindex.xml in Webmaster Tools.

 

Thanks.

Share this post


Link to post
Share on other sites

Jack for some reason when I logged in to the admin and switched to Alternate and re-ran the script they all failed. Then I switched back to Standard and run the script again they were all created successfully. ALSO the script rendered the correct amount of sitemapproducts.xml links. So I guess you can disregard my previous posts unless that's a red flag of some sort.

 

Thanks.

Share this post


Link to post
Share on other sites

Hi, I have installed Jack's upgrade version v1.6 (http://addons.oscommerce.com/info/6583). It worked fine in standard mode and created all Sitemaps .... se below. However, Sitemaps are not displayed. See here .... Please suggest, am just a step away from success :-)

 

http://www.y2rishta.com/bridal-lengha/catalog/sitemapindex.xml'>http://www.y2rishta.com/bridal-lengha/catalog/sitemapindex.xml

This XML file does not appear to have any style information associated with it. The document tree is shown below.

 

Opening /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapproducts.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /home/fashno5/y2rishta.com/bridal-lengha/catalog/

WS_CAT /bridal-lengha/catalog/

Write /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapproducts.xml

Generated Google Product Sitemap Successfully

 

Opening /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapcategories.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /home/fashno5/y2rishta.com/bridal-lengha/catalog/

WS_CAT /bridal-lengha/catalog/

Write /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapcategories.xml

Generated Google Category Sitemap Successfully

 

Opening /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapindex.xml

FS_CAT /home/fashno5/y2rishta.com/bridal-lengha/catalog/

Server http://www.y2rishta.com

Save Path /home/fashno5/y2rishta.com/bridal-lengha/catalog/

WS_CAT /bridal-lengha/catalog/

Write /home/fashno5/y2rishta.com/bridal-lengha/catalog/sitemapindex.xml

Generated Google Sitemap Index Successfully

 

CONGRATULATIONS! All files generated successfully.

 

If you have not already submitted the sitemap index to Google click the link below.

Before you do I HIGHLY recommend that you view the XML files to make sure the data is correct.

 

http://www.google.com/webmasters/sitemaps/ping?sitemap=http%3A%2F%2Fwww.y2rishta.com%2Fbridal-lengha%2Fcatalog%2Fsitemapindex.xml

 

For your convenience here is the CRON command for your site:

php /home/fashno5/y2rishta.com/bridal-lengha/catalog/googlesitemap/index.php

 

Here is your sitemap index: http://www.y2rishta.com/bridal-lengha/catalog/sitemapindex.xml'>http://www.y2rishta.com/bridal-lengha/catalog/sitemapindex.xml

Here is your product sitemap: http://www.y2rishta.com/bridal-lengha/catalog/sitemapproducts.xml

Here is your category sitemap: http://www.y2rishta.com/bridal-lengha/catalog/sitemapcategories.xml

Share this post


Link to post
Share on other sites

Hi, Sitemaps are now displayed in both Standard and Alternate. Overwrite the gss file :-) URL's created are not SEO URL's but they are redirected to SEO URL's.

Share this post


Link to post
Share on other sites

Be sure to keep an eye on your webmaster tools regarding the maps. Google was complaining about sitemap files with urls not matching the actual urls. I haven't seen that lately but that may be because I haven't worked on site with that problem lately.

Share this post


Link to post
Share on other sites

Got a warning from google that 1,214 urls from my sitemap have:

An invalid date was found. Please fix the date or formatting before resubmitting.

This is what they are seeing:

 

Parent tag: url

Tag: lastmod

Value: 2014201420142014-JanJan-SatSatEST0606:0101EST-18000Sat

 

what is the correct format for these values? Where do I change them?


If the only tool you have is a hammer, all your problems look like nails

Share this post


Link to post
Share on other sites

I changed the lastmod to "Y-m-d" and got the following response:

An invalid date was found. Please fix the date or formatting before resubmitting.

 

 

Parent tag: url

Tag: lastmod

Value: -0001-11-30

Why is the year appearing like that?


If the only tool you have is a hammer, all your problems look like nails

Share this post


Link to post
Share on other sites

Here is the code

<?php
/**
* Google XML Sitemap Feed
*
* The Google sitemap service was announced on 2 June 2005 and represents
* a huge development in terms of crawler technology.  This contribution is
* designed to create the sitemap XML feed per the specification delineated
* by Google.
* @package Google-XML-Sitemap-Feed
* @[member='licensed2kill'] http://opensource.org/licenses/gpl-license.php GNU Public License
* @version 1.0
* @[member='Link'] http://www.oscommerce-freelancers.com/ osCommerce-Freelancers
* @[member='Link'] http://www.google.com/webmasters/sitemaps/docs/en/about.html About Google Sitemap
* @[member='copyright'] Copyright 2005, Bobby Easland
* @[member='author'] Bobby Easland
* @version 2.0
* @[member='Link'] http://www.eurobigstore.com
* @[member='Link'] http://www.google.com/webmasters/sitemaps/docs/en/about.html About Google Sitemap
* @[member='copyright'] Copyright 2006, Davide Duca
* @[member='author'] Davide Duca
* @filesource

*/
/**
* MySQL_Database Class
*
* The MySQL_Database class provides abstraction so the databaes can be accessed
* without having to use tep API functions. This class has minimal error handling
* so make sure your code is tight!
* @package Google-XML-Sitemap-Feed
* @[member='licensed2kill'] http://opensource.org/licenses/gpl-license.php GNU Public License
* @version 1.1
* @[member='Link'] http://www.oscommerce-freelancers.com/ osCommerce-Freelancers
* @[member='copyright'] Copyright 2005, Bobby Easland
* @[member='author'] Bobby Easland
*/
class MySQL_DataBase{
/**
 * Database host (localhost, IP based, etc)
* @var string
 */
var $host;
/**
 * Database user
* @var string
 */
var $user;
/**
 * Database name
* @var string
 */
var $db;
/**
 * Database password
* @var string
 */
var $pass;
/**
 * Database link
* @var resource
 */
var $link_id;
/**
* MySQL_DataBase class constructor
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $host
* @[member='param'] string $user
* @[member='param'] string $db
* @[member='param'] string $pass
*/
function MySQL_DataBase($host, $user, $db, $pass){
 $this->host = $host;
 $this->user = $user;
 $this->db = $db;
 $this->pass = $pass;
 $this->ConnectDB();
 $this->SelectDB();
} # end function
/**
* Function to connect to MySQL
* @[member='author'] Bobby Easland
* @version 1.0
*/
function ConnectDB(){
 $this->link_id = mysql_connect($this->host, $this->user, $this->pass);
} # end function

/**
* Function to select the database
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='Return'] resoource
*/
function SelectDB(){
 return mysql_select_db($this->db);
} # end function

/**
* Function to perform queries
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $query SQL statement
* @[member='Return'] resource
*/
function Query($query){
 return @mysql_query($query, $this->link_id);
} # end function
/**
* Function to fetch array
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] resource $resource_id
* @[member='param'] string $type MYSQL_BOTH or MYSQL_ASSOC
* @[member='Return'] array
*/
function FetchArray($resource_id, $type = MYSQL_BOTH){
 return @mysql_fetch_array($resource_id, $type);
} # end function
/**
* Function to fetch the number of rows
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] resource $resource_id
* @[member='Return'] mixed
*/
function NumRows($resource_id){
 return @mysql_num_rows($resource_id);
} # end function

/**
* Function to free the resource
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] resource $resource_id
* @[member='Return'] boolean
*/
function Free($resource_id){
 return @mysql_free_result($resource_id);
} # end function
/**
* Function to add slashes
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $data
* @[member='Return'] string
*/
function Slashes($data){
 return addslashes($data);
} # end function

/**
* Function to perform DB inserts and updates - abstracted from osCommerce-MS-2.2 project
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $table Database table
* @[member='param'] array $data Associative array of columns / values
* @[member='param'] string $action insert or update
* @[member='param'] string $parameters
* @[member='Return'] resource
*/
function DBPerform($table, $data, $action = 'insert', $parameters = '') {
 reset($data);
 if ($action == 'insert') {
   $query = 'INSERT INTO `' . $table . '` (';
   while (list($columns, ) = each($data)) {
  $query .= '`' . $columns . '`, ';
   }
   $query = substr($query, 0, -2) . ') values (';
   reset($data);
   while (list(, $value) = each($data)) {
  switch ((string)$value) {
 case 'now()':
   $query .= 'now(), ';
   break;
 case 'null':
   $query .= 'null, ';
   break;
 default:
   $query .= "'" . $this->Slashes($value) . "', ";
   break;
  }
   }
   $query = substr($query, 0, -2) . ')';
 } elseif ($action == 'update') {
   $query = 'UPDATE `' . $table . '` SET ';
   while (list($columns, $value) = each($data)) {
  switch ((string)$value) {
 case 'now()':
   $query .= '`' .$columns . '`=now(), ';
   break;
 case 'null':
   $query .= '`' .$columns .= '`=null, ';
   break;
 default:
   $query .= '`' .$columns . "`='" . $this->Slashes($value) . "', ";
   break;
  }
   }
   $query = substr($query, 0, -2) . ' WHERE ' . $parameters;
 }
 return $this->Query($query);
} # end function
} # end class

/**
* Google Sitemap Base Class
*
* The MySQL_Database class provides abstraction so the databaes can be accessed
* @package Google-XML-Sitemap-Feed
* @[member='licensed2kill'] http://opensource.org/licenses/gpl-license.php GNU Public License
* @version 1.2
* @[member='Link'] http://www.oscommerce-freelancers.com/ osCommerce-Freelancers
* @[member='Link'] http://www.google.com/webmasters/sitemaps/docs/en/about.html About Google Sitemap
* @[member='copyright'] Copyright 2005, Bobby Easland
* @[member='author'] Bobby Easland
*/
class GoogleSitemap{
/**
 * $DB is the database object
* @var object
 */
var $DB;
/**
 * $filename is the base name of the feeds (i.e. - 'sitemap')
* @var string
 */
var $filename;
/**
 * $savepath is the path where the feeds will be saved - store root
* @var string
 */
var $savepath;
/**
 * $base_url is the URL for the catalog
* @var string
 */
var $base_url;
/**
 * $debug holds all the debug data
* @var array
 */
var $debug;

/**
* GoogleSitemap class constructor
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $host Database host setting (i.e. - localhost)
* @[member='param'] string $user Database user
* @[member='param'] string $db Database name
* @[member='param'] string $pass Database password
*/
function GoogleSitemap($host, $user, $db, $pass){
 $this->DB = new MySQL_Database($host, $user, $db, $pass);
 $this->filename = "sitemap";
 $this->savepath = DIR_FS_CATALOG;
 $this->base_url = HTTP_SERVER . DIR_WS_HTTP_CATALOG;
 $this->debug = array();
} # end class constructor
/**
* Function to save the sitemap data to file as either XML or XML.GZ format
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='param'] string $data XML data
* @[member='param'] string $type Feed type (index, products, categories)
* @[member='Return'] boolean
*/
function SaveFile($data, $type){
 $filename = $this->savepath . '/' . $this->filename . $type;
 $compress = defined('GOOGLE_SITEMAP_COMPRESS') ? GOOGLE_SITEMAP_COMPRESS : 'false';
 if ($type == 'index') $compress = 'false';
 switch($compress){
  case 'true':
   $filename .= '.xml.gz';
   if ($gz = gzopen($filename,'wb9')){
 gzwrite($gz, $data);
 gzclose($gz);
 $this->debug['SAVE_FILE_COMPRESS'][] = array('file' => $filename, 'status' => 'success', 'file_exists' => 'true');
 return true;
   } else {
 $file_check = file_exists($filename) ? 'true' : 'false';
 $this->debug['SAVE_FILE_COMPRESS'][] = array('file' => $filename, 'status' => 'failure', 'file_exists' => $file_check);
 return false;
   }
   break;
  default:
   $filename .= '.xml';

   if ($fp = fopen($filename, 'w+')){
 fwrite($fp, $data);
 fclose($fp);
 $this->debug['SAVE_FILE_XML'][] = array('file' => $filename, 'status' => 'success', 'file_exists' => 'true');
 return true;
   } else {
 $file_check = file_exists($filename) ? 'true' : 'false';
 $this->debug['SAVE_FILE_XML'][] = array('file' => $filename, 'status' => 'failure', 'file_exists' => $file_check);
 return false;
   }
   break;
 } # end switch
} # end function

/**
* Function to compress a normal file
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $file
* @[member='Return'] boolean
*/
function CompressFile($file){
 $source = $this->savepath . $file . '.xml';
 $filename = $this->savepath . $file . '.xml.gz';
 $error_encountered = false;
 if( $gz_out = gzopen($filename, 'wb9') ){
  if($fp_in = fopen($source,'rb')){
   while(!feof($fp_in)) gzwrite($gz_out, fread($fp_in, 1024*512));
 fclose($fp_in);

  } else {
   $error_encountered = true;
  }
  gzclose($gz_out);
 } else {
  $error_encountered = true;
 }
 if($error_encountered){
  return false;
 } else {
  return true;
 }
} # end function

/**
* Function to generate sitemap file from data
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] array $data
* @[member='param'] string $file
* @[member='Return'] boolean
*/
function GenerateSitemap($data, $file){
 $content = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
 $content = '<?xml-stylesheet type="text/xsl" href="gss.xsl"?>' . "\n";
 $content .= '<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">' . "\n";
 foreach ($data as $url){
  $content .= "\t" . '<url>' . "\n";
  $content .= "\t\t" . '<loc>'.$url['loc'].'</loc>' . "\n";
  $content .= "\t\t" . '<lastmod>'.$url['lastmod'].'</lastmod>' . "\n";
  $content .= "\t\t" . '<changefreq>'.$url['changefreq'].'</changefreq>' . "\n";
  $content .= "\t\t" . '<priority>'.$url['priority'].'</priority>' . "\n";
  $content .= "\t" . '</url>' . "\n";
 } # end foreach
 $content .= '</urlset>';
 return $this->SaveFile($content, $file);

} # end function

/**
* Function to generate sitemap index file
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='Return'] boolean
*/
function GenerateSitemapIndex(){
 $content = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
 $content = '<?xml-stylesheet type="text/xsl" href="gss.xsl"?>' . "\n";
 $content .= '<sitemapindex xmlns="http://www.google.com/schemas/sitemap/0.84">' . "\n";
 $pattern = defined('GOOGLE_SITEMAP_COMPRESS')
	 ? GOOGLE_SITEMAP_COMPRESS == 'true'
    ? "sitemap*.xml.gz"
   :  "sitemap*.xml"
  : "sitemap*.xml";
 foreach ( array_merge(glob($this->savepath . $pattern)) as $filename ) {
 if ( eregi('index', $filename) ) continue;
 $content .= "\t" . '<sitemap>' . "\n";
 $content .= "\t\t" . '<loc>'.$this->base_url . basename($filename).'</loc>' . "\n";
 $content .= "\t\t" . '<lastmod>'.date ("Y-m-d", filemtime($filename)).'</lastmod>' . "\n";
 $content .= "\t" . '</sitemap>' . "\n";
 } # end foreach
 $content .= '</sitemapindex>';
 return $this->SaveFile($content, 'index');
} # end function
/**
* Function to generate product sitemap data
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='Return'] boolean
*/
function GenerateProductSitemap(){
 $sql = "SELECT products_id as pID, products_date_added as date_added, products_last_modified as last_mod, products_ordered
   FROM " . TABLE_PRODUCTS . "
   WHERE products_status='1'
   ORDER BY products_ordered DESC";

 if ( $products_query = $this->DB->Query($sql) ){
  $this->debug['QUERY']['PRODUCTS']['STATUS'] = 'success';
  $this->debug['QUERY']['PRODUCTS']['NUM_ROWS'] = $this->DB->NumRows($products_query);
  $container = array();
  $number = 0;
  $top = 0;
  while( $result = $this->DB->FetchArray($products_query) ){
   $top = max($top, $result['products_ordered']);
   //$location = $this->hrefLink('product' . $result['pID'] . FILENAME_PRODUCT_INFO, 'source=google', 'NONSSL', false);
   $location = $this->hrefLink(FILENAME_PRODUCT_INFO, 'products_id=' . $result['pID'], 'NONSSL', false);
   $lastmod = $this->NotNull($result['last_mod']) ? $result['last_mod'] : $result['date_added'];
   $changefreq = GOOGLE_SITEMAP_PROD_CHANGE_FREQ;
   $ratio = $top > 0 ? $result['products_ordered']/$top : 0;
   $priority = $ratio < .1 ? .1 : number_format($ratio, 1, '.', '');

   $container[] = array('loc' => htmlspecialchars(utf8_encode($location)),
					 'lastmod' => date ("Y-m-d", strtotime($lastmod)),
	  'changefreq' => $changefreq,
	  'priority' => $priority
					 );
   if ( sizeof($container) >= 50000 ){
 $type = $number == 0 ? 'products' : 'products' . $number;
 $this->GenerateSitemap($container, $type);
 $container = array();
 $number++;
   }
  } # end while
  $this->DB->Free($products_query);
  if ( sizeof($container) > 1 ) {
   $type = $number == 0 ? 'products' : 'products' . $number;
   return $this->GenerateSitemap($container, $type);
  } # end if
 } else {
  $this->debug['QUERY']['PRODUCTS']['STATUS'] = 'false';
  $this->debug['QUERY']['PRODUCTS']['NUM_ROWS'] = '0';
 }

} # end function
/**
* Funciton to generate category sitemap data
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='Return'] boolean
*/
function GenerateCategorySitemap(){
 $sql = "SELECT categories_id as cID, date_added, last_modified as last_mod
   FROM " . TABLE_CATEGORIES . "
   ORDER BY parent_id ASC, sort_order ASC, categories_id ASC";
 if ( $categories_query = $this->DB->Query($sql) ){
  $this->debug['QUERY']['CATEOGRY']['STATUS'] = 'success';
  $this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = $this->DB->NumRows($categories_query);
  $container = array();
  $number = 0;
  while( $result = $this->DB->FetchArray($categories_query) ){
   //$location = $this->hrefLink('category' . $this->GetFullcPath($result['cID']) . FILENAME_DEFAULT, 'source=google', 'NONSSL', false);
   $location = $this->hrefLink(FILENAME_DEFAULT, 'cPath=' . $this->GetFullcPath($result['cID']), 'NONSSL', false);
   $lastmod = $this->NotNull($result['last_mod']) ? $result['last_mod'] : $result['date_added'];
   $changefreq = GOOGLE_SITEMAP_CAT_CHANGE_FREQ;
   $priority = .5;

   $container[] = array('loc' => htmlspecialchars(utf8_encode($location)),
					 'lastmod' => date ("Y-m-d", strtotime($lastmod)),
	  'changefreq' => $changefreq,
	  'priority' => $priority
					 );
   if ( sizeof($container) >= 50000 ){
 $type = $number == 0 ? 'categories' : 'categories' . $number;
 $this->GenerateSitemap($container, $type);
 $container = array();
 $number++;
   }
  } # end while
  $this->DB->Free($categories_query);
  if ( sizeof($container) > 1 ) {
   $type = $number == 0 ? 'categories' : 'categories' . $number;
   return $this->GenerateSitemap($container, $type);
  } # end if
 } else {
  $this->debug['QUERY']['CATEOGRY']['STATUS'] = 'false';
  $this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = '0';
 }
} # end function
/**
* Function to retrieve full cPath from category ID
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] mixed $cID Could contain cPath or single category_id
* @[member='Return'] string Full cPath string
*/
function GetFullcPath($cID){
 if ( ereg('_', $cID) ){
  return $cID;
 } else {
  $c = array();
  $this->GetParentCategories($c, $cID);
  $c = array_reverse($c);
  $c[] = $cID;
  $cID = sizeof($c) > 1 ? implode('_', $c) : $cID;
  return $cID;
 }
} # end function
/**
* Recursion function to retrieve parent categories from category ID
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] mixed $categories Passed by reference
* @[member='param'] integer $categories_id
*/
function GetParentCategories(&$categories, $categories_id) {
 $sql = "SELECT parent_id
	  FROM " . TABLE_CATEGORIES . "
   WHERE categories_id='" . (int)$categories_id . "'";
 $parent_categories_query = $this->DB->Query($sql);
 while ($parent_categories = $this->DB->FetchArray($parent_categories_query)) {
  if ($parent_categories['parent_id'] == 0) return true;
  $categories[sizeof($categories)] = $parent_categories['parent_id'];
  if ($parent_categories['parent_id'] != $categories_id) {
   $this->GetParentCategories($categories, $parent_categories['parent_id']);
  }
 }
} # end function

/**
* Function to check if a value is NULL
* @[member='author'] Bobby Easland as abstracted from osCommerce-MS2.2
* @version 1.0
* @[member='param'] mixed $value
* @[member='Return'] boolean
*/
function NotNull($value) {
 if (is_array($value)) {
  if (sizeof($value) > 0) {
   return true;
  } else {
   return false;
  }
 } else {
  if (($value != '') && (strtolower($value) != 'null') && (strlen(trim($value)) > 0)) {
   return true;
  } else {
   return false;
  }
 }
} # end function
/**
* Function to return href_link
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] mixed $value
* @[member='Return'] boolean
*/
function hrefLink($page, $parameters, $connection, $add_session_id) {
 //if ( defined('SEO_URLS') && SEO_URLS == 'true' || defined('SEO_ENABLED') && SEO_ENABLED == 'true' ) {
 if ( (SEARCH_ENGINE_FRIENDLY_URLS == 'true') ) {
  return tep_href_link($page, $parameters, $connection, $add_session_id);
 } else {
  return $this->base_url . $page . '?' . $parameters;
 }
} # end function

/**
* Utility function to read and return the contents of a GZ formatted file
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='param'] string $file File to open
* @[member='Return'] string
*/
function ReadGZ( $file ){
 $file = $this->savepath . $file;
 $lines = gzfile($file);
 return implode('', $lines);
} # end function
/**
* Utility function to generate the submit URL
* @[member='author'] Bobby Easland
* @version 1.0
* @[member='Return'] string
*/
function GenerateSubmitURL(){
 $url = urlencode($this->base_url . 'sitemapindex.xml');
 return htmlspecialchars(utf8_encode('http://www.google.com/webmasters/sitemaps/ping?sitemap=' . $url));
} # end function
} #  end class
?>


If the only tool you have is a hammer, all your problems look like nails

Share this post


Link to post
Share on other sites

@@lyonsperf The problem is that the date is invalid in your database. This addon is just using what is there. You have to fix the database date. Also, please don't post code like that unless asked or unless it is unique. It makes the thread difficult to read and serves no purpose.

Share this post


Link to post
Share on other sites

I am using the addon. When my site was hosted on a shared server, it ran ok. Now it is on a vps server and getting the following error after "...write sitemapproducts10.xml":

 

Fatal error: Maximum execution time of 30 seconds exceeded in /home/lifeom5/public_html/googlesitemap/sitemap.class.php on line 469

is there a solution to generate the files in different sessions to avoid the timeout, or how to change server settings (just for this/time)?

 

 

Thank you,

chao

Share this post


Link to post
Share on other sites

30 seconds is a very low timeout value for a VPS. I suggest you ask your host to increase it.

Share this post


Link to post
Share on other sites

Thank you Jack! I thought it is the setting in php.ini, but it did not help. so it is somewhere for the vps settings?

chao

Share this post


Link to post
Share on other sites

followed all instructions but i got

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Share this post


Link to post
Share on other sites

Internal Server errors can be caused by many things. Try looking in the error log as the message says and if nothing is there, as your host to look at the problem, as the message says.

Share this post


Link to post
Share on other sites

On the topic of enable/disable categories, after fooling with it for several @##$% hours, for the life of me I cannot integrate

 

categories_status = 1

 

into

 

/**
* Funciton to generate category sitemap data
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='Return'] boolean
*/
function GenerateCategorySitemap(){
 $quotes = (defined('QUOTES_CATEGORY_NAME') ? " where cd.categories_name NOT LIKE '" . QUOTES_CATEGORY_NAME . "' " : '');
$sql = "SELECT c.categories_id as cID, c.date_added, c.last_modified as last_mod
 FROM " . TABLE_CATEGORIES . " c left join " . TABLE_CATEGORIES_DESCRIPTION . " cd on c.categories_id = cd.categories_id
	 " . $quotes . "
ORDER BY c.parent_id ASC, c.sort_order ASC, c.categories_id ASC";

if ( $categories_query = $this->DB->Query($sql) ){
$this->debug['QUERY']['CATEOGRY']['STATUS'] = 'success';
$this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = $this->DB->NumRows($categories_query);
$container = array();
$number = 0;
while( $result = $this->DB->FetchArray($categories_query) ){
$location = $this->hrefLink(FILENAME_DEFAULT, 'cPath=' . $this->GetFullcPath($result['cID']), 'NONSSL', false);
$lastmod = $this->NotNull($result['last_mod']) ? $result['last_mod'] : $result['date_added'];
$changefreq = GOOGLE_SITEMAP_CAT_CHANGE_FREQ;
$priority = .5;

$container[] = array('loc' => htmlspecialchars(utf8_encode($location)),
'lastmod' => date ("Y-m-d", strtotime($lastmod)),
'changefreq' => $changefreq,
'priority' => $priority
					 );
if ( sizeof($container) >= 50000 ){
 $type = $number == 0 ? 'categories' : 'categories' . $number;
 $this->GenerateSitemap($container, $type);
 $container = array();
 $number++;
}
} # end while
$this->DB->Free($categories_query);
if ( sizeof($container) > 0 ) {
$type = $number == 0 ? 'categories' : 'categories' . $number;
return $this->GenerateSitemap($container, $type);
} # end if
} else {
$this->debug['QUERY']['CATEOGRY']['STATUS'] = 'false';
$this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = '0';
}
} # end function

 

refused to work no matter what I tried. Is it even possible? If so please share...!

 

I finally found and gave another code block a try. I'd like to know if the below is compatible with the contribution? or could I expected to have problems??

After initial tests, the sitemap generated and it seems to be working, and is only showing categories that are enabled...

 

/**
* Funciton to generate category sitemap data - USU5 version, but seems to work, and also seems ok with added WHERE categories_status = 1
* @[member='author'] Bobby Easland
* @version 1.1
* @[member='Return'] boolean
*/	
 function GenerateCategorySitemap(){
		 $sql = "SELECT categories_id as cID, date_added, last_modified as last_mod
						 FROM " . TABLE_CATEGORIES . "
WHERE categories_status = 1
						 ORDER BY parent_id ASC, sort_order ASC, categories_id ASC";
		 if ( $categories_query = tep_db_query($sql) ){
				 $this->debug['QUERY']['CATEOGRY']['STATUS'] = 'success';
				 $this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = tep_db_num_rows($categories_query);
				 $container = array();
				 $number = 0;
				 while( $result = tep_db_fetch_array($categories_query) ){
						 $location =tep_href_link(FILENAME_DEFAULT, 'cPath=' . $this->GetFullcPath($result['cID']), 'NONSSL', false);
						 $lastmod = tep_not_null($result['last_mod']) ? $result['last_mod'] : $result['date_added'];
						 $changefreq = GOOGLE_SITEMAP_CAT_CHANGE_FREQ;
						 $priority = .5;

						 $container[] = array('loc' => htmlspecialchars(utf8_encode($location)),
																	 'lastmod' => date ("Y-m-d", strtotime($lastmod)),
																	 'changefreq' => $changefreq,
																	 'priority' => $priority
																	 );
						 if ( sizeof($container) >= 50000 ){
								 $type = $number == 0 ? 'categories' : 'categories' . $number;
								 $this->GenerateSitemap($container, $type);
								 $container = array();
								 $number++;
						 }
				 } # end while
				 tep_db_free_result($categories_query);				
				 if ( sizeof($container) > 1 ) {
						 $type = $number == 0 ? 'categories' : 'categories' . $number;
						 return $this->GenerateSitemap($container, $type);
				 } # end if					
		 } else {
				 $this->debug['QUERY']['CATEOGRY']['STATUS'] = 'false';
				 $this->debug['QUERY']['CATEOGRY']['NUM_ROWS'] = '0';
		 }
 } # end function

 

Thank you for the help.


-Dave

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×