{"authorId":"1","title":"Creating a Dynamic Google Sitemap","slug":"creating-a-dynamic-google-sitemap","postDate":1184713996,"expiryDate":null,"enabled":"1","fields":{"2":"<p>My site has a <a href=\"http:\/\/trevordavis.net\/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>\n\n<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>\n\n<!--more-->\n\n<h2>Setting it Up<\/h2>\n\n<p>First, you will need to send the correct headers, create the opening xml tag, and connect to your database.<\/p>\n\n<div class=\"codeblock\"><code>\n&lt;?php<br \/>header(\"Content-Type:&nbsp;text\/xml;charset=iso-8859-1\");<br \/>echo&nbsp;'&lt;?xml&nbsp;version=\"1.0\"&nbsp;encoding=\"UTF-8\"?&gt;<br \/>&lt;urlset&nbsp;xmlns=\"http:\/\/www.sitemaps.org\/schemas\/sitemap\/0.9\"&gt;';<br \/>require_once('DB&nbsp;CONNECTION&nbsp;GOES&nbsp;HERE');&nbsp;\/\/This&nbsp;is&nbsp;where&nbsp;I&nbsp;would&nbsp;require&nbsp;my&nbsp;DB&nbsp;connection&nbsp;file&nbsp;\n\n<\/code><\/div>\n\n<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&rsquo;t figure it out still, <a href=\"\/contact\/\">let me know, and I can help<\/a>.<\/p>\n\n<h2>Get the Categories<\/h2>\n\n<p>Next, we want to query the database to get the categories used in Wordpress:<\/p>\n\n<div class=\"codeblock\"><code>\n$query&nbsp;=&nbsp;\"SELECT&nbsp;cat_ID,&nbsp;category_nicename<br \/>&nbsp;&nbsp;FROM&nbsp;wp_categories<br \/>&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;category_nicename\";<br \/>$result&nbsp;=&nbsp;@mysql_query($query);&nbsp;\n\n<\/code><\/div>\n\n<p>Then, we need to loop through the categories and display a url entry for each category:<\/p>\n\n<div class=\"codeblock\"><code>\nwhile($row&nbsp;=&nbsp;mysql_fetch_array($result,&nbsp;MYSQL_ASSOC))&nbsp;&#123;<br \/>&nbsp;echo&nbsp;'&lt;url&gt;<br \/>&nbsp;&nbsp;&lt;loc&gt;http:\/\/trevordavis.net\/blog\/'&nbsp;.&nbsp;$row&#91;'category_nicename'&#93;&nbsp;.&nbsp;'\/&lt;\/loc&gt;<br \/>&nbsp;&nbsp;&lt;changefreq&gt;weekly&lt;\/changefreq&gt;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;\/url&gt;';&nbsp;\n\n<\/code><\/div>\n\n<h2>Get the Entries<\/h2>\n\n<p>Next, we need to create a query to return all of the entries for each category:<\/p>\n\n<div class=\"codeblock\"><code>\n$artQuery&nbsp;=&nbsp;\"SELECT&nbsp;p.post_name,&nbsp;DATE_FORMAT(p.post_date,&nbsp;'%Y-%m-%d')&nbsp;AS&nbsp;createdOn<br \/>&nbsp;&nbsp;FROM&nbsp;wp_posts&nbsp;AS&nbsp;p,&nbsp;wp_categories&nbsp;AS&nbsp;cat,&nbsp;wp_post2cat&nbsp;AS&nbsp;pc<br \/>&nbsp;&nbsp;WHERE&nbsp;p.ID&nbsp;=&nbsp;pc.post_id&nbsp;AND&nbsp;pc.category_id&nbsp;=&nbsp;\"&nbsp;.&nbsp;$row&#91;'cat_ID'&#93;&nbsp;.&nbsp;\"<br \/>&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;p.ID<br \/>&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;p.ID&nbsp;DESC\";<br \/>$artResult&nbsp;=&nbsp;@mysql_query($artQuery);&nbsp;\n\n<\/code><\/div>\n\n<p>Finally, we want to create a url entry for each blog entry:<\/p>\n\n<div class=\"codeblock\"><code>\nwhile($artRow&nbsp;=&nbsp;mysql_fetch_array($artResult,&nbsp;MYSQL_ASSOC))&nbsp;&#123;<br \/>&nbsp;echo&nbsp;'&lt;url&gt;<br \/>&nbsp;&nbsp;&lt;loc&gt;http:\/\/trevordavis.net\/blog\/'&nbsp;.&nbsp;$row&#91;'category_nicename'&#93;&nbsp;.&nbsp;'\/'.&nbsp;$artRow&#91;'post_name'&#93;&nbsp;.&nbsp;'.php&lt;\/loc&gt;<br \/>&nbsp;&nbsp;&lt;lastmod&gt;'.$artRow&#91;'createdOn'&#93;.'&lt;\/lastmod&gt;<br \/>&nbsp;&nbsp;&lt;changefreq&gt;weekly&lt;\/changefreq&gt;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;\/url&gt;';<br \/>&#125;&nbsp;\n\n<\/code><\/div>\n\n<p>To finish it off, we just close everything up:<\/p>\n\n<div class=\"codeblock\"><code>\n&#125;<br \/>echo'&lt;\/urlset&gt;';?&gt;&nbsp;\n\n<\/code><\/div>\n\n<h3>The Whole Script<\/h3>\n\n<p>Here is the finished script:<\/p>\n\n<div class=\"codeblock\"><code>\n&lt;?php<br \/>header(\"Content-Type:&nbsp;text\/xml;charset=iso-8859-1\");<br \/>echo&nbsp;'&lt;?xml&nbsp;version=\"1.0\"&nbsp;encoding=\"UTF-8\"?&gt;<br \/>&lt;urlset&nbsp;xmlns=\"http:\/\/www.sitemaps.org\/schemas\/sitemap\/0.9\"&gt;';<br \/>require_once('DB&nbsp;CONNECTION&nbsp;GOES&nbsp;HERE');&nbsp;\/\/This&nbsp;is&nbsp;where&nbsp;I&nbsp;would&nbsp;require&nbsp;my&nbsp;DB&nbsp;connection&nbsp;file<br \/><br \/>$query&nbsp;=&nbsp;\"SELECT&nbsp;cat_ID,&nbsp;category_nicename<br \/>&nbsp;&nbsp;FROM&nbsp;wp_categories<br \/>&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;category_nicename\";<br \/>$result&nbsp;=&nbsp;@mysql_query($query);<br \/><br \/><br \/>while($row&nbsp;=&nbsp;mysql_fetch_array($result,&nbsp;MYSQL_ASSOC))&nbsp;&#123;<br \/><br \/>&nbsp;echo&nbsp;'&lt;url&gt;<br \/>&nbsp;&nbsp;&lt;loc&gt;http:\/\/trevordavis.net\/blog\/'&nbsp;.&nbsp;$row&#91;'category_nicename'&#93;&nbsp;.&nbsp;'\/&lt;\/loc&gt;<br \/>&nbsp;&nbsp;&lt;changefreq&gt;weekly&lt;\/changefreq&gt;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;\/url&gt;';<br \/><br \/>&nbsp;$artQuery&nbsp;=&nbsp;\"SELECT&nbsp;p.post_name,&nbsp;DATE_FORMAT(p.post_date,&nbsp;'%Y-%m-%d')&nbsp;AS&nbsp;createdOn<br \/>&nbsp;&nbsp;&nbsp;FROM&nbsp;wp_posts&nbsp;AS&nbsp;p,&nbsp;wp_categories&nbsp;AS&nbsp;cat,&nbsp;wp_post2cat&nbsp;AS&nbsp;pc<br \/>&nbsp;&nbsp;&nbsp;WHERE&nbsp;p.ID&nbsp;=&nbsp;pc.post_id&nbsp;AND&nbsp;pc.category_id&nbsp;=&nbsp;\"&nbsp;.&nbsp;$row&#91;'cat_ID'&#93;&nbsp;.&nbsp;\"<br \/>&nbsp;&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;p.ID<br \/>&nbsp;&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;p.ID&nbsp;DESC\";<br \/><br \/>&nbsp;$artResult&nbsp;=&nbsp;@mysql_query($artQuery);<br \/><br \/>&nbsp;while($artRow&nbsp;=&nbsp;mysql_fetch_array($artResult,&nbsp;MYSQL_ASSOC))&nbsp;&#123;<br \/><br \/>&nbsp;&nbsp;echo&nbsp;'&lt;url&gt;<br \/>&nbsp;&nbsp;&nbsp;&lt;loc&gt;http:\/\/trevordavis.net\/blog\/'&nbsp;.&nbsp;$row&#91;'category_nicename'&#93;&nbsp;.&nbsp;'\/'.&nbsp;$artRow&#91;'post_name'&#93;&nbsp;.&nbsp;'.php&lt;\/loc&gt;<br \/>&nbsp;&nbsp;&nbsp;&lt;lastmod&gt;'.$artRow&#91;'createdOn'&#93;.'&lt;\/lastmod&gt;<br \/>&nbsp;&nbsp;&nbsp;&lt;changefreq&gt;weekly&lt;\/changefreq&gt;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;\/url&gt;';<br \/>&nbsp;&#125;<br \/>&#125;<br \/>echo'&lt;\/urlset&gt;';<br \/>?&gt;&nbsp;\n\n<\/code><\/div>\n\n<h3>Modifying the .htaccess file<\/h3>\n\n<p>We also want to rewrite the url for this file so that it is available at <a href=\"http:\/\/trevordavis.net\/sitemap.xml\">http:\/\/trevordavis.net\/sitemap.xml<\/a>. Open up your .htaccess file, or create one if you don&rsquo;t have one. Then add the following:<\/p>\n\n<div class=\"codeblock\"><code>\nRewriteEngine&nbsp;on<br \/>RewriteRule&nbsp;sitemap.xml&nbsp;googleSitemap.php&nbsp;\n\n<\/code><\/div>\n\n<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&rsquo;s file structure to the sitemap as well. You can see how I did this in <a href=\"http:\/\/trevordavis.net\/sitemap.xml\">my google sitemap<\/a>.<\/p>\n\n<h3>Sitemaps Protocal<\/h3>\n\n<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>\n\n<h3>By the Way&hellip;<\/h3>\n<p>Let me know if you can think of any way to improve this.<\/p>","14":{"old_id":{}}}}