<p>My site has a <a href="//trevor-davis.com/blog/news/switched-to-wordpress.php">Wordpress blog on it</a>, but it does not power the whole site, just the blog. If my entire site were powered by Wordpress, then I could generate a <a href="http://www.google.com/webmasters/sitemaps/">sitemap for Google</a> using Wordpress functions.</p>

<p>I did not want to have Wordpress control everything; I wanted to have more control. By doing some quick queries, you can generate a sitemap for Google that has all of your blog entries in it.</p>



<h2>Setting it Up</h2>

<p>First, you will need to send the correct headers, create the opening xml tag, and connect to your database.</p>

<div class="codeblock"><code>
&lt;?php<br />header("Content-Type: text/xml;charset=iso-8859-1");<br />echo '&lt;?xml version="1.0" encoding="UTF-8"?&gt;<br />&lt;urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"&gt;';<br />require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file 

</code></div>

<p>If you are not sure how to connect to your database, you will need to contact your host to get that information, and then do a <a href="http://www.google.com/search?source=ig&amp;hl=en&amp;q=php+mysql+connection&amp;btnG=Google+Search">search for php MySQL connection</a>. If you can’t figure it out still, <a href="/contact/">let me know, and I can help</a>.</p>

<h2>Get the Categories</h2>

<p>Next, we want to query the database to get the categories used in Wordpress:</p>

<div class="codeblock"><code>
$query = "SELECT cat_ID, category_nicename<br />  FROM wp_categories<br />  ORDER BY category_nicename";<br />$result = @mysql_query($query); 

</code></div>

<p>Then, we need to loop through the categories and display a url entry for each category:</p>

<div class="codeblock"><code>
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {<br /> echo '&lt;url&gt;<br />  &lt;loc&gt;//trevor-davis.com/blog/' . $row['category_nicename'] . '/&lt;/loc&gt;<br />  &lt;changefreq&gt;weekly&lt;/changefreq&gt;<br />      &lt;/url&gt;'; 

</code></div>

<h2>Get the Entries</h2>

<p>Next, we need to create a query to return all of the entries for each category:</p>

<div class="codeblock"><code>
$artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn<br />  FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc<br />  WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "<br />  GROUP BY p.ID<br />  ORDER BY p.ID DESC";<br />$artResult = @mysql_query($artQuery); 

</code></div>

<p>Finally, we want to create a url entry for each blog entry:</p>

<div class="codeblock"><code>
while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {<br /> echo '&lt;url&gt;<br />  &lt;loc&gt;//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php&lt;/loc&gt;<br />  &lt;lastmod&gt;'.$artRow['createdOn'].'&lt;/lastmod&gt;<br />  &lt;changefreq&gt;weekly&lt;/changefreq&gt;<br />      &lt;/url&gt;';<br />} 

</code></div>

<p>To finish it off, we just close everything up:</p>

<div class="codeblock"><code>
}<br />echo'&lt;/urlset&gt;';?&gt; 

</code></div>

<h3>The Whole Script</h3>

<p>Here is the finished script:</p>

<div class="codeblock"><code>
&lt;?php<br />header("Content-Type: text/xml;charset=iso-8859-1");<br />echo '&lt;?xml version="1.0" encoding="UTF-8"?&gt;<br />&lt;urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"&gt;';<br />require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file<br /><br />$query = "SELECT cat_ID, category_nicename<br />  FROM wp_categories<br />  ORDER BY category_nicename";<br />$result = @mysql_query($query);<br /><br /><br />while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {<br /><br /> echo '&lt;url&gt;<br />  &lt;loc&gt;//trevor-davis.com/blog/' . $row['category_nicename'] . '/&lt;/loc&gt;<br />  &lt;changefreq&gt;weekly&lt;/changefreq&gt;<br />      &lt;/url&gt;';<br /><br /> $artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn<br />   FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc<br />   WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "<br />   GROUP BY p.ID<br />   ORDER BY p.ID DESC";<br /><br /> $artResult = @mysql_query($artQuery);<br /><br /> while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {<br /><br />  echo '&lt;url&gt;<br />   &lt;loc&gt;//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php&lt;/loc&gt;<br />   &lt;lastmod&gt;'.$artRow['createdOn'].'&lt;/lastmod&gt;<br />   &lt;changefreq&gt;weekly&lt;/changefreq&gt;<br />        &lt;/url&gt;';<br /> }<br />}<br />echo'&lt;/urlset&gt;';<br />?&gt; 

</code></div>

<h3>Modifying the .htaccess file</h3>

<p>We also want to rewrite the url for this file so that it is available at <a href="//trevor-davis.com/sitemap.xml">//trevor-davis.com/sitemap.xml</a>. Open up your .htaccess file, or create one if you don’t have one. Then add the following:</p>

<div class="codeblock"><code>
RewriteEngine on<br />RewriteRule sitemap.xml googleSitemap.php 

</code></div>

<p>Upload this file along with the googleSitemap.php script to your site root, and you are set. You can also add the rest of your site’s file structure to the sitemap as well. You can see how I did this in <a href="//trevor-davis.com/sitemap.xml">my google sitemap</a>.</p>

<h3>Sitemaps Protocal</h3>

<p>You can <a href="http://sitemaps.org/protocol.php">read more about the sitemaps protocal</a> to see what other attributes you can apply to each url entry.</p>

<h3>By the Way…</h3>
<p>Let me know if you can think of any way to improve this.</p>