free google sitemap

user1 · #1 01-14-2010, 06:15 AM

I was looking for a module that creates a sitemap for Google. I found one for free at xcartmod.com .

It is a nice mod but it has some downsides:

1. It looks the db and creates the urls the following way:

www.site.com//product.php?productid=xx

which is bad when you use clean urls.

2. Doesn't include the manufacturer urls

So I made some modifications to fix the above mentioned problems and, since it is a free module, I thought I could post the modifications so someone else can use them.

If for any reason you think that is wrong, please tell me and I will remove everything from this post.

So I opened the ../modules/xCartMod_Google_Sitemap/sitemap_xml.php and made the following changes:

1. Replaced

Code:

$cats = func_query("SELECT * FROM $sql_tbl[categories] WHERE avail='Y'");

if (is_array($cats[0]))
    foreach($cats as $ca) {
        $xml .= '<url>
<loc>'.$http_location.'/home.php?cat='.$ca["categoryid"].'</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.8</priority>
</url>';
    }

with

Code:

$cats = func_query("SELECT * FROM $sql_tbl[categories], $sql_tbl[clean_urls] WHERE resource_type = 'C' AND categoryid = resource_id AND avail = 'Y'");

if (is_array($cats[0]))
    foreach($cats as $ca) {
        $xml .= '<url>
<loc>'.$http_location.'/'.$ca["clean_url"].'</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.8</priority>
</url>';
    }

2. Replaced

Code:

$prods = func_query("SELECT * FROM $sql_tbl[products] WHERE forsale='Y'");
if (is_array($prods[0]))
    foreach($prods as $pr) {
        $xml .= '<url>
<loc>'.$http_location.'/product.php?productid='.$pr["productid"].'</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.6</priority>
</url>';
    }

with

Code:

$prods = func_query("SELECT * FROM $sql_tbl[products], $sql_tbl[clean_urls] WHERE resource_type = 'P' AND productid = resource_id AND forsale = 'Y'");
if (is_array($prods[0]))
    foreach($prods as $pr) {
        $xml .= '<url>
<loc>'.$http_location.'/'.$pr["clean_url"].'.html</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.6</priority>
</url>';
    }

3. Replaced

Code:

$pages = func_query("SELECT * FROM $sql_tbl[pages] WHERE active='Y'");
if (is_array($pages[0]))
    foreach($pages as $pa) {
        $xml .= '<url>
<loc>'.$http_location.'/pages.php?pageid='.$pa["pageid"].'</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.6</priority>
</url>';
    }

with

Code:

$pages = func_query("SELECT * FROM $sql_tbl[pages], $sql_tbl[clean_urls] WHERE resource_type = 'S' AND pageid = resource_id AND active = 'Y'");
if (is_array($pages[0]))
    foreach($pages as $pa) {
        $xml .= '<url>
<loc>'.$http_location.'/'.$pa["clean_url"].'.html</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.6</priority>
</url>';
    }

4. Added

Code:

$manufacturers = func_query("SELECT * FROM $sql_tbl[manufacturers], $sql_tbl[clean_urls] WHERE resource_type = 'M' AND manufacturerid = resource_id AND avail = 'Y'");
if (is_array($manufacturers[0]))
    foreach($manufacturers as $manuf) {
        $xml .= '<url>
<loc>'.$http_location.'/'.$manuf["clean_url"].'</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>monthly</changefreq>
<priority>0.6</priority>
</url>';
    }

before

Code:

$xml .= '<url>
<loc>'.$http_location.'/help.php?section=business</loc>
<lastmod>'.$today.'</lastmod>

You are ready! Your sitemap.xml contains your products,categories, manufacturer and static pages clean urls. It is updated once a day (if you visit the admin side of your shop) and is stored in www.yoursite.com/sitemap.xml .

I made the changes on my 4.3.0 test installation (I'm now on 4.2.3 and want to upgrade to 4.3.0) and seems to work perfectly. It will work and on previous versions if the database tables maintain the same structure with 4.3.0.

Please test before you do anything on your live shop!!!

Best regards,
Andreas

Babydoll · #2 01-25-2010, 04:16 PM

thanks for mentioning this.
i modified it like you said without the last manufacturer part.
is it supposed to show the clean url after? mine is still showing the ~productid=xx

user1 · #3 01-26-2010, 11:13 PM

Quote:

Originally Posted by Babydoll

thanks for mentioning this.
i modified it like you said without the last manufacturer part.
is it supposed to show the clean url after? mine is still showing the ~productid=xx

The module runs each time you load a page in the admin part of your site.
The module also creates two entries in the "config" table of your database. In one of them "xcartmod_sitemap_time", it saves the current date.
Since we need our sitemap created only once in a day there is an if statement inside it.
Before creating the sitemap, it checks if the current date is the same with the one in "xcartmod_sitemap_time".
If it isn't, it creates a new sitemap and writes the current date to the db. If it is, it does nothing.

So you have created your sitemap but you can't create another one before the day passes. Just delete the value in "xcartmod_sitemap_time" entry in the "config" table. If you don't want (for testing purposes) to update the date entry and check the date in the db then replace:

Code:

$xm_today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
if ($xm_today == $config["xcartmod_sitemap_time"])
    return;

db_query("UPDATE $sql_tbl[config] SET value='$xm_today' WHERE name='xcartmod_sitemap_time'");

with

Code:

//$xm_today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
//if ($xm_today == $config["xcartmod_sitemap_time"])
//    return;
//
//db_query("UPDATE $sql_tbl[config] SET value='$xm_today' WHERE name='xcartmod_sitemap_time'");

Don't forget to revert this change after you finish with testing or else you will create a new sitemap each time you visit the admin part of your site!

Andreas Nikolaidis

dmpinder · #4 01-28-2010, 09:47 AM

You are a gentleman, sir. Thank you for posting.

user1 · #5 01-28-2010, 10:15 AM

You are welcome

Andreas Nikolaidis

stu1artuk · #6 08-04-2010, 02:35 AM

Works great on 4.2.

You just need to make sure you have a sitemap.xml with urlset headers and the file is set to chmod 666 in your public_html folder.

I've attached one for any novices.

dmpinder · #7 08-04-2010, 02:36 AM

Quote:

Originally Posted by stu1artuk

Works great on 4.2.

You just need to make sure you have a sitemap.xml with urlset headers and the file is set to chmod 666 in your public_html folder.

I've attached one for any novices.

I found that I had to set mine to 777 in order for the script to be able to write the XML data.

Darren

HumanNature · #8 08-11-2010, 05:11 PM

Woooh this modification is great thank you very much, it works perfectly in 4.2.3.

Just one question, i want to add an extra condition to the product query because if the main category of the product is not available I don't want the products to appear in the sitemap but i don't know how to made the JOIN to the category table in the query.

Can you help me?

$prods = func_query("SELECT * FROM $sql_tbl[products], $sql_tbl[clean_urls] WHERE resource_type = 'P' AND productid = resource_id AND forsale = 'Y'");
if (is_array($prods[0]))
foreach($prods as $pr) {
$xml .= '<url>
<loc>'.$http_location.'/'.$pr["clean_url"].'.html</loc>
<lastmod>'.$today.'</lastmod>
<changefreq>weekly</changefreq>
<priority>0.8</priority>
</url>';
}

user1 · #9 08-12-2010, 07:33 AM

For that extra condition to work we need to search two extra tables in the database.

Try to replace

Code:

$prods = func_query("SELECT * FROM $sql_tbl[products],  $sql_tbl[clean_urls] WHERE resource_type = 'P' AND productid =  resource_id AND forsale = 'Y'");

with

Code:

$prods = func_query("SELECT * FROM $sql_tbl[xcart_clean_urls],  $sql_tbl[xcart_products_categories], $sql_tbl[xcart_products],  $sql_tbl[xcart_categories] WHERE xcart_clean_urls.resource_type = 'P'  AND xcart_products.productid = xcart_clean_urls.resource_id AND main =  'Y' AND xcart_products.productid = xcart_products_categories.productid  AND xcart_products.forsale = "Y" AND  xcart_products_categories.categoryid = xcart_categories.categoryid AND  xcart_categories.avail = 'Y'");

I hope this works for you. Please check carefully to see if the result is correct.

Andreas

HumanNature · #10 08-12-2010, 09:26 AM

Quote:

Originally Posted by user1

For that extra condition to work we need to search two extra tables in the database.

Try to replace

Code:

$prods = func_query("SELECT * FROM $sql_tbl[products],  $sql_tbl[clean_urls] WHERE resource_type = 'P' AND productid =  resource_id AND forsale = 'Y'");

with

Code:

$prods = func_query("SELECT * FROM $sql_tbl[xcart_clean_urls],  $sql_tbl[xcart_products_categories], $sql_tbl[xcart_products],  $sql_tbl[xcart_categories] WHERE xcart_clean_urls.resource_type = 'P'  AND xcart_products.productid = xcart_clean_urls.resource_id AND main =  'Y' AND xcart_products.productid = xcart_products_categories.productid  AND xcart_products.forsale = "Y" AND  xcart_products_categories.categoryid = xcart_categories.categoryid AND  xcart_categories.avail = 'Y'");

I hope this works for you. Please check carefully to see if the result is correct.

Andreas

Hi Andreas, thank you very much, I have tried the folowing:

Code:

$prods = func_query("SELECT * FROM $sql_tbl[xcart_clean_urls], $sql_tbl[xcart_products_categories], $sql_tbl[xcart_products], $sql_tbl[xcart_categories] WHERE xcart_clean_urls.resource_type = 'P' AND xcart_products.productid = xcart_clean_urls.resource_id AND xcart_products_categories.main = 'Y' AND xcart_products.productid = xcart_products_categories.productid AND xcart_products.forsale = 'Y' AND xcart_products_categories.categoryid = xcart_categories.categoryid AND xcart_categories.avail = 'Y'");

that is the same as you wrote but changing

Code:

AND main =  'Y'

for

Code:

AND xcart_products_categories.main = 'Y'

I get the following error:

INVALID SQL: 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , WHERE xcart_clean_urls.resource_type = 'P' AND xcart_products.productid = ' at line 1
SQL QUERY FAILURE:SELECT * FROM , , , WHERE xcart_clean_urls.resource_type = 'P' AND xcart_products.productid = xcart_clean_urls.resource_id AND xcart_products_categories.main = 'Y' AND xcart_products.productid = xcart_products_categories.productid AND xcart_products.forsale = 'Y' AND xcart_products_categories.categoryid = xcart_categories.categoryid AND xcart_categories.avail = 'Y'

don't know what happens...