HTMLHelp Forums > Automatic XML Sitemap

HTMLHelp Forums > Programming > Server-side Scripting

Dante Monaldo

Apr 9 2011, 05:25 PM

I found some PHP scripts to automatically create a XML sitemap by using each product in MySQL database as its own page. I think there must be an error in my sitemap.php page, because when I try to view it, it's just a blank page. Could someone read through the code really quick and let me know of any errors? See the code below.

CODE

<?php

require_once('config.php');
require_once('common.php');

$xml = '';
$priority = '0.5';
$freq = 'weekly';

$xml .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$xml .= "<urlset\n";
$xml .= "  xmlns=\"http://www.google.com/schemas/sitemap/0.84\"\n";
$xml .= "  xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"\n";
$xml .= "  xsi:schemaLocation=\"http://www.google.com/schemas/sitemap/0.84\n";
$xml .= "                      http://www.google.com/schemas/sitemap/0.84/sitemap.xsd\">\n\n";

$xml .= "<url>\n";
$xml .= "  <loc>http://$_SERVER[HTTP_HOST]/</loc>\n";
$xml .= "  <priority>$priority</priority>\n";
$xml .= "  <changefreq>$freq</changefreq>\n";
$xml .= "</url>\n";

$seo_ind = get_seo_ind();

$query = "SELECT id, name FROM categories;";
database_inquiry($query,$rows);
foreach ($rows as $item)
   {
   $xml .="<url>\n";
   if ($seo_ind)
      {
      $id = $item['id'];
      $xml .="  <loc>http://$_SERVER[HTTP_HOST]/products_{$id}_1.html</loc>\n";
      }
   else
      {
      $xml .= htmlspecialchars  ("  <loc>http://$_SERVER[HTTP_HOST]/product_list.php?category=$item[id]</loc>\n");
      }
   $xml .="  <priority>$priority</priority>\n";
   $xml .="  <changefreq>$freq</changefreq>\n";
   $xml .="</url>\n";
   }

$query = "SELECT id, url_name, category FROM products;";
database_inquiry($query, $rows);

foreach ($rows as $item)
   {
   $xml .="<url>\n";
   if ($seo_ind)
      {
      $name = $item['url_name'];
      $xml .="  <loc>http://$_SERVER[HTTP_HOST]/product_$name.html</loc>\n";
      }
   else
      {
      $xml .= htmlspecialchars  ("  <loc>http://$_SERVER[HTTP_HOST]/product.php?id=$item[id]&category=$item[category]</loc>\n");
      }
   $xml .="  <priority>$priority</priority>\n";
   $xml .="  <changefreq>$freq</changefreq>\n";
   $xml .="</url>\n";
   }

$xml .= "</urlset>\n";

header('Content-type: text/xml');
echo $xml;

exit();

?>

Thanks!

vettenut

Apr 9 2011, 05:36 PM

I have found PHP files cannot be previewed in a browser. When I tried it all I saw in the browser was the raw PHP code.

Dante Monaldo

Apr 9 2011, 05:42 PM

I have it on my site at http://www.priceprospector.com/sitemap.php

I don't get why it's not working because there is an echo $xml; at the bottom, so shouldn't it just echo the non-styled version of the sitemap?

The reason why I need it automatic is because I have close to a million products but unless you search for the individual product on my site, there is no link to it. All the data is stored in MySQL database.

Brian Chandler

Apr 10 2011, 12:38 AM

Sorry, but it's just not reasonable to expect someone to "read through" your program and point out all the errors. You have to do something called debugging.

Why generate an xml file? It won't display properly in browsers (as far as I know). But anyway it's just as easy to generate a genuine html file, which will.

If your database has 1 million entries, how many pages are going to be in the "site map"?

Dante Monaldo

Apr 10 2011, 09:03 PM

I figured 1 million pages would be in the site map. The main reason I want a sitemap is to have my products indexed by Google.

The problem is that all of my products are in MySQL database, and unless I link directly to the product page, Google won't find the product.

Forgetting the code above and sitemaps, do you have any ideas how I could initially create a simple HTML page where I could have a list of links to all the pages of my site?

My vision is a couple categories with a '+' next to them, and when you click the '+', a bunch of links drop down and so on. Like a tree with links branching out on several levels.

I hope that makes sense.

Any help would be appreciated.

Brian Chandler

Apr 11 2011, 12:57 AM

Well, a page containing links to 1 million pages is going to be Large (50 MB if your filenames a short...) In practice it's not going to be human-usable, unless all these plus buttons are some ajax stuff, and the user has a fast connection. And if it's ajax, search engines won't see it.

I suggest you go to google and do some reading -- I don't know what the answer is, but google is generally helpful. If you just generate 1 million pages you may find google interprets it as junk.

I think it's difficult to suggest more without some idea of what these million items are!

pandy

Apr 11 2011, 10:57 AM

I can't answer how to do it, but wouldn't a better approach be several pages? First a page with links to the categories, then a page for each category (or several if needed).

Dante Monaldo

Apr 11 2011, 09:31 PM

Okay, good idea Brian.

I learned that as long as I have all of the links to my product pages, search engines will be able to find the pages. And if I use pandy's idea, I can spread the links out over several pages so as not to have the page too big.

Now does anyone know how to go through a MySQL database and display all of the links on a simple HTML page, preferably using a PHP script?

If not, any ideas how I would go about finding out how to do this?

The help is really appreciated.

Dante Monaldo

Apr 14 2011, 11:25 PM

Brian Chandler

Apr 15 2011, 01:51 AM

QUOTE

Now does anyone know how to go through a MySQL database and display all of the links on a simple HTML page, preferably using a PHP script?

That should be Example 1 in a tutorial on "Using PHP and MySQL for a database-backed website". Sorry, I can't recommend specific material.

But you have to decide if you are going to invest the time and energy to learn how to write simple PHP programs, or whether you want to pay someone else to do it for you. You can't really expect other people to write the programs for free; and generally this stuff can't be done by finding a "script" to do it, because 95% of it is the customisation required. But a really simple loop through the database only takes 10-20 lines of code, so is not a big deal.

Dante Monaldo

Apr 20 2011, 06:52 PM

thanks for the help

steve1986

Apr 21 2011, 02:43 AM

I create only with this tool http://www.sitemapwriter.com/download.html

Frederiek

Apr 21 2011, 04:13 AM

That's an off line PC only tool.

Search for "sitemap builder" and you'll find online tools too.

Dante Monaldo

Apr 23 2011, 12:10 PM

Actually, I have now run into another problem with my sitemap.

I figured out how to successfully echo 50000 links from my database by using

CODE

http://www.priceprospector.com/product_$URL_NAME.html

But now I realized that I have multiple duplicates, but I don't want to delete them from my database due to other reasons. I have tried using unique_array, but it doesn't work because all it sees is 50000 values containing:

CODE

http://www.priceprospector.com/product_$URL_NAME.html

It doesn't read all of the 50000 links after they have the values from the database plugged in for $URL_NAME.

Is there a way to remove the links after they have duplicate values from the database? See how it looks now: http:/www.priceprospector.com/sitemap.php. You'll see how many duplicates there are.

If you were interested how I figured out doing it in the first place, below is the code.

CODE

<?php

require_once('config.php');
require_once('common.php');

  $page = @$_GET['page'];
// how many rows to show per page
$drowsPerPage = 50;

// by default we show first page
$pageNum = 1;

// if $page defined, use it as page number
if(isset($page))
{
    $pageNum = $page;
}

// counting the offset
$offset = ($pageNum -1) * $drowsPerPage;

$db = mysql_connect("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE",$db);

$sql = "select * from `products` LIMIT $offset, $drowsPerPage";
$result = mysql_query($sql, $db) or die(mysql_error());
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">
";

{
while($row = mysql_fetch_array($result)) {
  $URL_NAME = $row["url_name"];
echo "<url>
        <loc>http://www.MYSITE.com/product_$URL_NAME.html</loc>
      </url>";
}
}
?>
</urlset>

Brian Chandler

Apr 23 2011, 01:23 PM

If you really have a million entries, you should work on the SQL: instead of getting '*' (all columns of the table), just get "UNIQUE url_name" -- but you have to look up the syntax of 'UNIQUE' because I can't remember.

Also, you haven't explained why you want to generate an XML file, instead of an HTML file.

And "url_name" is a very odd name: it's really the name of the product; the URL is the whole string from 'http' to '.html'. This won't stop it from working, but readable programs are always more reliable in the long term.

Dante Monaldo

Apr 23 2011, 02:07 PM

The reason why I want an XML file is because I need it to be my sitemap. Thanks for all that helpful info, but I mainly wanted to know if there is a way to delete the duplicates after the page has loaded.

Dante Monaldo

Apr 23 2011, 06:41 PM

Thanks Brian Chandler.

I changed it so it only retrieves url_name from the database, and it dramatically cut down on the load time.

As for using url_name, I hired a programmer and had him build the majority of my site, and it would be a lot of hassle to change it now. Maybe in the future.

I just keep hitting my head trying to delete duplicate links from the sitemap.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.