Generate Dynamic XML Sitemap Using PHP/MySQL

Hello, you know its gonna be cool if you have your own sitemap been generated automatically from database, this is also for those who update items to db.

How i wrote this code is that, assuming you have an item in the db which details are shown in a page, just generate the links and submit to search engine, take a look at the below code

  1. <?php
  2. //Lets define our database details
  3. define('DBHOST','localhost');
  4. define('DBUSER','root');
  5. define('DBNAME','sitemap');
  6. define('DBPWD','');
  7. define('DOMAIN','http://2netlodge.com');
  8. //Ooh, what are we doing without the database? Lets initiate the MySQL database connection
  9. $connect = new MySQLi(DBHOST,DBUSER,DBPWD,DBNAME)or die(mysqli_error());
  10. //Yea, we all know this is a php code and file, but lets tell the browser that its an XML content
  11. header('Content-Type: text/xml');
  12. //Adding some sitemap essentials
  13. echo <?xml version="1.0" encoding="UTF-8" ?>
  14. <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
  15. //our link listing starts here
  16. echo '<url>';
  17. //get data from database
  18. $query = $connect->query("select * from items");
  19. while($row=$query->fetch_array(MYSQL_ASSOC))
  20. {
  21. echo '<loc>
  22. ',DOMAIN,'?page=',$row['id'],
  23. '
  24. </loc>';
  25. //you can add some date
  26. }
  27. echo '</url>';
  28. echo '</urlset>';
  29. ?>
CHANGE THESE TO YOURS 

define(‘DBHOST’,’localhost’);
define(‘DBUSER’,’root’);
define(‘DBNAME’,’sitemap’);
define(‘DBPWD’,”);
define(‘DOMAIN’,’http://2netlodge.com‘);

If you want to turn the extension to .xml, just add this to your .htaccess

  1. RewriteRule ^(.*).xml $1.php

Hope it helps