Generate dynamic Google Sitemaps.xml using PHP & MySQL

The other day I was trying to generate a Google Sitemap XML file dynamically (not for WordPress btw) using PHP, pulling data from a MySQL database and populating the XML file with such data – as-and-when new data is entered into the database.

Sounds easy enough, but I had difficulty getting PHP to render & run properly within an XML environment. Google doesn't need the sitemap.xml to be in a .xml format, but I would presume it helps and it would look better if it were. So, here's my work-around.

Firstly, I put this chunk of code into my .htaccess file. These couple of lines tell all .xml files to render as PHP.

<Files *xml>
ForceType application/x-httpd-php
</Files>

Secondly, create a new file and call it sitemap.xml, then put this code at the very top.

<?php
header('Content-type: text/xml');
echo '<?xml version="1.0" encoding="UTF-8"?>';
?>

The rest is pretty easy to be honest, just output whatever you want. Here's my example

<?php
header('Content-type: text/xml');
echo '<?xml version="1.0" encoding="UTF-8"?>';

echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">';

$data = array();
$data[] = array(
    'url'        => 'http://www.domain.com/',
    'freq'        => 'weekly',
    'priority'    => '1.00'
);
$data[] = array(
    'url'        => 'http://www.domain.com/path/to/page',
    'freq'        => 'weekly',
    'priority'    => '0.80'
);foreach ($data as $d)
{
    echo '<url>';
        echo '<loc>'.$d['url'].'</loc>';
        echo '<changefreq>'.$d['freq'].'</changefreq>';
        echo '<priority>'.$d['priority'].'</priority>';
    echo '</url>';
}
echo '</urlset>';

Pretty cool. Works well and (so far) Google is allowing this 100%

 

Geek: Improve SEO on WordPress blog with HTACCESS

Even though this blog now mainly consists of drifting, motorsport, cars and other cool things, I still aim to keep my geek readers updated too! I’ve managed to source 2 different chunks of code to help any WordPress user along the way with (hopefully) improved SEO results to your blog. This post is talking entirely about that odd little file known as .htaccess, so pay attention, some real SEO advantages can come from this…

Firstly, I’ve now moved my blog from a .co.uk to a .com TLD, so I needed a htaccess mod to permanently move all requests from the old domain to the new domain. A two-liner from SEOblogr.com did it for me, and this is below

RewriteCond %{HTTP_HOST} ^(www.)?old-domain.co.uk
RewriteRule (.*) http://www.new-domain.com/$1 [R=301,L]

And the other part to my htaccess is the ability to remove query strings from the URL’s, and then again permanently redirect them to the blog post. A query string, by the way, is anything after the main request URI which isn’t already rewritten. ?query for example, and this code is here, taken from BrianCray.com

RewriteCond %{QUERY_STRING} !=""
RewriteCond %{QUERY_STRING} !^p=.*
RewriteCond %{REQUEST_URI} !^/wp-admin.*
RewriteRule ^(.*)$ /$1? [R=301,L]

And that’s basically it! Just put both these bits of code after the line

RewriteBase /

In your .htaccess file, and you’re good to go. I’m seeing good results already from this. /endgeek

Using htaccess to ensure correct site domain name & redirect

If you’re lucky enough to have more than one extension of the same name domain name (.com and .co.uk for example), or even if you don’t, but have multiple domain names pointing to the same site, then you will almost certainly benefit to redirect all traffic to just one domain name.

Continuing with the SEO side of things, Google & other search engines out there aren’t too keen on duplicate content, as I’m sure you’re aware of. If you’re running with more than one domain name and they’re both pointing to the same patch on the Internet, then there’s a good chance Google will see this as Dup Content.

Even if you’re only running with one domain, you may notice you can type in http://domain.com and http://www.domain.com and still get to the same thing (in most cases). Even this might be seen as another site, and therefore possible Dup Content. There’s an easy fix, however…

After some searching about to get a chunk of code which worked out of the box, this bit of htaccess code does the trick perfectly. Simply create a file named .htaccess in the root of your web directory, and paste in the following code:

RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^www.domain.com$ [NC]
RewriteRule ^(.*)$ http://www.domain.com/$1 [L,R=301]

In simple terms, the above script is basically saying, if the domain name is not www.domain.com, then redirect the page requested to http://www.domain.com with a 301-header permanent redirect on it.

So, for example, if you were to visit http://domain.com/page.html, then you’d be redirected to http://www.domain.com/page.html. Perfect!