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

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