7

I have a large amount of user profiles hosted in this schema https://example.com/stats.php?player=name (It's not the prettiest URL, but .htaccess dramatically slows down my site and I don't understand apache2.conf). Anyhow, how can I get Google to index all of these pages dynamically without having to enter all 90,000 accounts into sitemap.xml?

  • @w3d. While you're editing like that, you should also use RFC 2606 domain names. – TRiG Jan 28 '15 at 18:47
  • 2
    Watch out: The top element <urlset> is a container for a set of up to 50,000 document elements. Hence you should submit at least two sitemaps, i.e., split the database output accordingly (use LIMIT in the sql query) – Hagen von Eitzen Jan 28 '15 at 20:24
12

Sitemaps can be dynamic just like web pages. Just have a PHP script grab those names from the database and make a loop to echo out the XML for each one. Also, make sure you output the proper content type for your XML. That's it.

The code below is a basic script for generating a dynamic XML sitemap. Please not this is only an example intended to point you, or anyone looking to create a dynamic sitemap, in the right direction. There really should be better error checking and handling here.

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$query = "SELECT name FROM users";
if ($result = mysqli_query($link, $query)) {
    echo header("Content-type: text/xml");
?>
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<?php
    $date = date('Y-m-d');
    while ($row = mysqli_fetch_assoc($result)) {
?>
     <url>
      <loc>https://domain.com/stats.php?player=<?php echo $row['name']; ?></loc>
      <lastmod><?php echo $date; ?></lastmod>
      <changefreq>monthly</changefreq>
      <priority>0.8</priority>
   </url>
<?php       
    }
    mysqli_free_result($result);
?>
</urlset> 
<?php   
}
mysqli_close($link);
?>

Keep in mind that a single sitemap may have only 50,000 records in it so this script has its limits. But you should be able to modify it to handle multiple sitemaps which allows you to grow beyond the 50,000 limit.

  • I think I ecall that giveing identical priority to everything triggers an error in the Google webmaster tools. But this can easily be mended by specifically adding the start page of your site with priority 1, say. – Hagen von Eitzen Jan 28 '15 at 20:14
  • @HagenvonEitzen To tell you the truth I would omit that field entirely. – John Conde Jan 28 '15 at 20:15
  • 1
    If you don't actually track when the profiles have been last modified in your database, I'd strongly suggest leaving the <lastmod> field out, instead of using a dummy date like in this example code. At best, there will be no difference; at worst, lying about when your pages were last modified can cause crawlers to miss changes (if the date is too old) or waste time inefficiently recrawling pages that haven't changed (if it's too new). Of course, the best option would be to actually store a last modification timestamp for each profile in the database, and report it in the sitemap. – Ilmari Karonen Jan 28 '15 at 23:34
3

A sitemap does not usually help get pages indexed. See The Sitemap Paradox.

To get pages indexed:

  1. You need to link to each page from some other page or preferably multiple other pages.
  2. Include enough unique content on each profile that Google doesn't view the pages as duplicate.
  3. Provide a good landing page experience for anybody coming to the page from Google search. If Google does index the page but then find that nobody clicks to it, or everybod that clicks returns back to click on another result, it will drop in rankings or fall out of the index pretty quickly.

In general, user profile pages are generally not great candidate pages for inclusion in Google's search index. They usually don't have a lot of content, nor are they good landing pages.

  • 2
    Actually, pages in the sitemap and not directly linked will get spidered: I have a php script as wrapper around html files in a directory structure and a dynamic sitemap translated the directory tree to wrapped URLs. An "experimental" directory was specifically excluded in the wrapper skript, but sometime later appeared indexed. Only when I blocked with robot.txt and obtained all those warnings about blocked links in the sitemap, I noticd that the experimental directory was included in the sitemap ... – Hagen von Eitzen Jan 28 '15 at 20:18
  • Items in a sitemap will get crawled, yes, but they are not super likely to get indexed. Even if they do get indexed, they won't rank particularly well. – Stephen Ostermiller Jan 28 '15 at 21:21
  • 1
    By "not indexed" do you mean, "will not be found even by a search for player name site:example.com"? Or do you just mean, won't draw in loads of organic search traffic from sexy high-volume terms? ;-) – Steve Jessop Jan 29 '15 at 4:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.