How To Make An XML Sitemap With PHP & mySQL in 5 Easy Steps

Adding sitemaps to manually is both time consuming and annoying, so why not create a sitemap that updates automatically with PHP and mySQL.

For this example lets say you have a bunch of definition pages and the URL structure is as such: https://example.com/definitions/your-term-goes-here. Lets also assume we have a database of these terms in a table called definitions. We want all of these definition pages to auto populate in our sitemap when they are added to the database.

Step 1: Set up the XML Header

Enter the following line in your PHP file. It’s what tells PHP to return an XML file. While the file extension may be .PHP, the file will be rendered as an XML file when you add this line.

header("Content-type: text/xml; charset=utf-8");

Step 2: Set up the mySQL Query

Lets go ahead and query the definitions from our database. We will lop through these and add them to our xml sitemap in the next few steps.

mysqli_select_db($connection, $database);
$queryDefinitions = "SELECT * FROM definitions";
$recordsetDefinitions = mysqli_query($connection, $queryDefinitions);
$row = mysqli_fetch_assoc($recordsetDefinitions);

Step 3: Begin the XML String

Let’s go ahead and create a variable called $xmlFeed that will store our xml string.

$xmlFeed = '<?xml version="1.0" encoding="UTF-8"?>
<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">';

Step 4: Loop Through the Database Items

Now lets loop through each definition in the table and append each URL to our $xmlFeed string we created in the previous step. For the last mod date I am simply using the current date, but feel free to change this to the timestamp if you have one in the database.

do{

$xmlFeed .= '<url>
<loc>https://example.com/definition/'.$row['definition_slug'].'</loc>
  <lastmod>'.date('Y-m-d').'</lastmod>
  <changefreq>daily</changefreq>
</url>';
	
}while($row = mysqli_fetch_assoc($recordsetDefinitions));

Step 5: End the XML String and Echo

Finally, all we need to do now is to end our $xmlFeed variable and echo it so that it outputs on the string. Since we set the content type to XML in our first step, it should now render as XML when you visit the sitemap’s URL!

$xmlFeed .= '</urlset>';
 
echo $xmlFeed;