[SOLVED] Sitemap ~ Loop up to 50,000 rows then start a new set and contenue?

[Presto-X]

Hello everyone,

 

I'm working on a custom sitemap.xml file for a site I am working on, right now the application that I have working connects to a mysql database querys all of the product ids and then loops them out and saves the list to a .xml file, this is all working very well.  My problem now is that Google sets a limit (Google Webmaster Tools) to no more than 50,000 links in any one sitemap, so I need a way to create a sitemap1.xml with 49,999 links then start a new file sitemap2.xml ext... ext...

 

You can take a look at my code below, I'm not sure if this is an easy fix or not, but I do know I have no clue how to do it lol, any help would be great thanks so much guys.

 

// INCLUDE THE CONFIG FILE
require('config.php');

// CONNECT TO THE DATABASE
@mysql_connect($host, $user, $pass);
@mysql_select_db($data);

// GET THE LIST OF OF PRODUCTS FROM THE DATABASE
$query_sitemap = "SELECT products_id FROM {$dbprefix}products";
$sitemap = mysql_query($query_sitemap) or die(mysql_error());
$row_sitemap = mysql_fetch_assoc($sitemap);
$totalRows_sitemap = mysql_num_rows($sitemap);

// GET THE CURRENT WEBSITE'S DOMAIN NAME
$domain = $_SERVER['HTTP_HOST'];

// BUILD XML SITEMAP
  $xml  = '<?xml version="1.0" encoding="utf-8"?>'."\n";
  $xml .= '	<urlset>'."\n";
do {
  $xml .= "	 	<url>"."\n";
  $xml .= "			<loc>http://{$domain}/index.php?main_page=product_info&products_id={$row_sitemap['products_id']}</loc>"."\n";
  $xml .= "			<lastmod>".date('Y-m-d')."</lastmod>"."\n";
  $xml .= "			<changefreq>monthly</changefreq>"."\n";
  $xml .= "			<priority>0.5</priority>"."\n";
  $xml .= "		</url>"."\n";
   } while ($row_sitemap = mysql_fetch_assoc($sitemap));
  $xml .= '</urlset>';

// CHECK TO SEE IF THE SITEMAP FILE IS ALREADY ON THE SERVER
if (file_exists('/home/presto/public_html/sitemap.xml')){
  // IF THE SITEMAP EXISTS THEN DELETE IT
  unlink('/home/presto/public_html/sitemap.xml');
}

// CREATE THE NEW SITEMAP XML FILE
$handle = fopen('/home/presto/public_html/sitemap.xml', 'x+');

// WRITE TO THE NEW SITEMAP FILE
fwrite($handle, $xml."\r\n");

// CLOSE THE SITEMAP FILE
fclose($handle);

// CLOSE THE MYSQL CONNECTION
mysql_free_result($sitemap);

[mikesta707]

well in this code, everytime you run the while loop, the xml page deletes the sitemap.xml page and reopens a new one. you might want to do that before your while loop.

 

as for making it make every page have only 49,999 links do something like

$runs = 1;//which page we are one
while ($row_sitemap = mysql_fetch_assoc($sitemap)){
//yourstuff
$i = 0;//counter
$limit = 49999;
$filename = "sitemap".$runs;//open this file
while ($i < 49999){
//write to the file
//remember to increment the $i value
}
$runs++;
}

 

Hope that helps!

 

[Presto-X]

Thanks for the reply mikesta,

 

How would I "increment the $i value"?

 

// something like:
$i++;
// or like this?
$i+49999;

[premiso]

How would I "increment the $i value"?

 

while ($i < 49999){
//write to the file
//remember to increment the $i value

$i++;
}

[Presto-X]

Thanks so much for the help guys, this is what I ended up coming up with, maybe others will find this useful.

 

// INCLUDE THE CONFIG FILE
require('config.php');

// CONNECT TO THE DATABASE
@mysql_connect($host, $user, $pass);
@mysql_select_db($data);

$maxRows_sitemap = 49999;
$pageNum_sitemap = 0;
if (isset($_GET['pageNum_sitemap'])) {
  $pageNum_sitemap = $_GET['pageNum_sitemap'];
}
$startRow_sitemap = $pageNum_sitemap * $maxRows_sitemap;

$query_sitemap = "SELECT products_id FROM {$dbprefix}products";
$query_limit_sitemap = sprintf("%s LIMIT %d, %d", $query_sitemap, $startRow_sitemap, $maxRows_sitemap);
$sitemap = mysql_query($query_limit_sitemap) or die(mysql_error());
$row_sitemap = mysql_fetch_assoc($sitemap);

if (isset($_GET['totalRows_sitemap'])) {
  $totalRows_sitemap = $_GET['totalRows_sitemap'];
} else {
  $all_sitemap = mysql_query($query_sitemap);
  $totalRows_sitemap = mysql_num_rows($all_sitemap);
}
$totalPages_sitemap = ceil($totalRows_sitemap/$maxRows_sitemap)-1;

// GET THE LIST OF OF PRODUCTS FROM THE DATABASE


// GET THE CURRENT WEBSITE'S DOMAIN NAME
$domain = $_SERVER['HTTP_HOST'];

// BUILD XML SITEMAP
  $xml  = '<?xml version="1.0" encoding="utf-8"?>'."\n";
  $xml .= '	<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'."\n";
do {
  $xml .= "	 	<url>"."\n";
  $xml .= "			<loc>http://{$domain}/index.php?main_page=product_info&products_id={$row_sitemap['products_id']}</loc>"."\n";
  $xml .= "			<lastmod>".date('Y-m-d')."</lastmod>"."\n";
  $xml .= "			<changefreq>monthly</changefreq>"."\n";
  $xml .= "			<priority>0.5</priority>"."\n";
  $xml .= "		</url>"."\n";
   } while ($row_sitemap = mysql_fetch_assoc($sitemap));
  $xml .= '</urlset>';


// CHECK TO SEE IF THE SITEMAP FILE IS ALREADY ON THE SERVER
if (file_exists("/home/{$user}/public_html/sitemap{$pageNum_sitemap}.xml")){
  // IF THE SITEMAP EXISTS THEN DELETE IT
  unlink("/home/{$user}/public_html/sitemap{$pageNum_sitemap}.xml");
}

// CREATE THE NEW SITEMAP XML FILE
$handle = fopen("/home/{$user}/public_html/sitemap{$pageNum_sitemap}.xml", 'x+');

// WRITE TO THE NEW SITEMAP FILE
fwrite($handle, $xml."\r\n");

// CLOSE THE SITEMAP FILE
fclose($handle);

// GET THE CURRENT NUMBER OF ROWS
$rows_sitemap = mysql_num_rows($sitemap);
// IF THE CURRENT NUMBER OF ROWS ARE EQUAL TO 49999
if($rows_sitemap == $maxRows_sitemap){
  $pageNum = $_GET['pageNum_sitemap']+1;
  $gotonext = "sitemap/index.php?pageNum_sitemap={$pageNum}";
}
else{
  $gotonext = "sitemap".$_GET['pageNum_sitemap'].".xml";
}
?>
<h3 style="color:#669900;">Your New Sitemap file (sitemap<?PHP echo $pageNum_sitemap; ?>.xml) has been created!</h3>
<meta http-equiv="refresh" content="3;url=<?PHP echo "http://{$domain}/{$gotonext}"; ?>" />

<?PHP mysql_free_result($sitemap); ?>