Horje Website
How to create an XML Sitemap from MYSQL? Multi XML Sitemap

It is very important to submit xml site map to google search console.

 

Create a file like sitemap.php and add following codes inside.

 
 
  1. <?php
  2. header("Content-Type: application/xml; charset=utf-8");
  3. echo '<?xml version="1.0" encoding="UTF-8" ?>' . PHP_EOL;
  4. ?>
  5. <?php echo'<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
  6. ?>
  7. <?php
  8. $conn=mysqli_connect("localhost","Username","Password","Database");
  9. ?>
  10. <?php
  11. $perpage = 4999;
  12. if(isset($_GET["page"])){
  13. $page = intval($_GET["page"]);
  14. }
  15. else {
  16. $page = 1;
  17. }
  18. $calc = $perpage * $page;
  19. $start = $calc - $perpage;
  20. $result = mysqli_query($conn, "select * from code Limit $start, $perpage");
  21. $rows = mysqli_num_rows($result);
  22. if($rows){
  23. $i = 0;
  24. while($post = mysqli_fetch_assoc($result)) {
  25. $stite=$post['title'];
  26. $stitle=strtolower("$stite");
  27. $stitle=str_replace(" ","-","$stitle");
  28. $stitle=str_replace("<","","$stitle");
  29. $stitle=str_replace(">","","$stitle");
  30. $stitle=str_replace("&","","$stitle");
  31. ?>
  32. <url><loc>https://yourdomain.com/slug/<?php echo $post["id"]; ?>-<?php echo $stitle; ?></loc></url>
  33. <?php
  34. }
  35. }
  36. ?>
  37. </urlset>

Then create a file like sitemaps.php and add following codes inside file.

 
 
  1. <?php
  2. $conn=mysqli_connect("localhost","Userman","Password","Database");
  3. ?>
  4. <?php
  5. $perpage = 4999;
  6. if(isset($_GET["page"])){
  7. $page = intval($_GET["page"]);
  8. }
  9. else {
  10. $page = 1;
  11. }
  12. $calc = $perpage * $page;
  13. $start = $calc - $perpage;
  14. $result = mysqli_query($conn, "select * from code Limit $start, $perpage");
  15. $rows = mysqli_num_rows($result);
  16. if($rows){
  17. $i = 0;
  18. while($post = mysqli_fetch_assoc($result)) {
  19. ?>
  20. <?php
  21. }
  22. }
  23. ?>
  24. <?php
  25. header("Content-Type: application/xml; charset=utf-8");
  26. echo '<?xml version="1.0" encoding="UTF-8" ?>' . PHP_EOL;
  27. ?>
  28. <?php echo'<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'; ?>
  29. <?php
  30. if(isset($page))
  31. {
  32. $result = mysqli_query($conn,"select Count(*) As Total from code");
  33. $rows = mysqli_num_rows($result);
  34. if($rows)
  35. {
  36. $rs = mysqli_fetch_assoc($result);
  37. $total = $rs["Total"];
  38. }
  39. $totalPages = ceil($total / $perpage);
  40. if($page <=1 ){
  41. }
  42. else
  43. {
  44. $j = $page - 1;
  45. }
  46. for($i=1; $i <= $totalPages; $i++)
  47. {
  48. if($i<>$page)
  49. {
  50. echo "<sitemap><loc>https://yourdomain.com/sitemap-$i.xml</loc></sitemap>
  51. ";
  52. }
  53. else
  54. {
  55. echo "<sitemap><loc>https://yourdomain.com/sitemap-$i.xml</loc></sitemap>
  56. ";
  57. }
  58. }
  59. if($page == $totalPages )
  60. {
  61. }
  62. else
  63. {
  64. $j = $page + 1;
  65. }
  66. }
  67. ?>
  68. <?php echo'</sitemapindex>'; ?>

Edit your .htaccess file.  Add following codes inside file

 
 
  1. RewriteEngine On
  2. RewriteBase /
  3. RewriteRule ^sitemaps.xml$ sitemaps.php
  4. RewriteRule ^sitemap-(.+).xml$ sitemap.php?page=$1
Published:
November 15, 2022
Author:
admin
Category:
Full Tutorials
Views:
28

This article was posted in Full Tutorials and tagged . Bookmark the permalink. Follow comments with the RSS feed for this post.Post a Comment or leave a trackback: Trackback URL.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Horje © 2011 - 2023