Creating XML sitemap with PHP and SQL and submitting to search engines

Sitemap are important if you own a website and want search engines to index important URLs from your site fast to improve your SEO. Most famous search engines like Google and Bing when look at your website they look for sitemap nowadays in XML format. It is also necessary to update your Sitemap regularly as you are updating content on your site. In this post we will create XML sitemap with PHP and SQL which will update your sitemap without uploading new files.

There are some formats in which XML sitemap can be created but Google recommend creating sitemap based on Sitemap Protocol which we are following.

To create Sitemap your URLs should be SEO friendly which are preferred, you can create sitemap on dynamic URLs but that will not fullfill our goal for SEO improvement.

Recommended: Creating SEO friendly URLs with PHP and SQL

Here we assume  that we have SEO freindly URLs stored in our Database table name urls. Now we will create PHP file naming it xmlsitemap.php with following code.

define veriables for next line and tabs.

$t = "\t";
$n = "\n";

$xml = '<?xml version="1.0" encoding="UTF-8"?>'."$n";
$xml.= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"'."$n";


 $sql = mysqli_query("select * from urls ");
 while($row = mysqli_fetch_array($sql))
  {
	$xml.= '<url>'."$n";
	$xml.= "$t".'<loc>'."YOUR-SITE-ADDRESS/".$row['url'].'</loc>'."$n";
	$xml.= '</url>'."$n";
  }

$xml.= '</urlset>';
$handle = fopen('sitemap.xml','w+');
fwrite($handle,$xml);
fclose($handle);

First we will define the xml version and urlset, then we will add url tag in which we will define location of URL in which the data is coming from database following the closing tag of urlset. Once the XML data is defined we will write this file in XML format with PHP function fopen , fwrite and fclose. In fopen we define the name sitemap.xml and w+ which will find the file sitemap.xml and rewrite if exists, if it does't exist it will create one. Now every time we open the file xmlsitemap.php it will update sitemap.xml.

Submission of Sitemap to search engines

Once sitemap is created you can add them to popular search engines so they index these URLs fast. To add them to search engines you must verify your account for website.

Recommended: Adding and verifying website to popular search engines

To add Sitemap to Google sign in with your Google id and open webmaster tools, on site dashboard you will see the option to add sitemap click on the button and add the sitemap URL.

Now to add Sitemap to Bing sign in with your Microsoft id and open webmaster tools, select configure my site from left menu and add Sitemap URL to sitemap option.