Simple PHP script to create XML sitemap with data (URL addresses) saved in MySQL database.
Data are selected from MySQL database with PHP PDO, and saved into a file on server (sitemap.xml).
- Code:
/*
 Simple PHP script to create XML sitemap with data (URL addresses) saved in MySQL database
 From: https://coursesweb.net/
*/
$xmlfile = 'sitemap.xml';

// this variable will contain the XML sitemap that will be saved in $xmlfile
$xmlsitemap = '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

// Connection data (server_address, name, password, database_name)
$hostdb = 'localhost';
$userdb = 'username';
$passdb = 'password';
$namedb = 'database_name';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT `col_url` FROM `table_name` WHERE 1";
  $result = $conn->query($sql);

  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    // Parse the result set, and add the URL in the XML structure
    foreach($result as $row) {
      $xmlsitemap .= '<url>
<loc>'. $row['col_url'] .'</loc>
<priority>0.5</priority>
<changefreq>weekly</changefreq>
</url>';
    }
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

$xmlsitemap .= '</urlset>';
file_put_contents($xmlfile, $xmlsitemap);          // saves the sitemap on server

// outputs the sitemap (delete this instruction if you not want to display the sitemap in browser)
echo $xmlsitemap;
- In this line of code:
$sql = "SELECT `col_url` FROM `table_name` WHERE 1";
Replace "col_url" with the name of the column in which the URL addresses are stored.
Replace "table_name" with the name of your MySQL table.
Also, replace "col_url" in this variable: $row['col_url'] too.

This PHP script creates a valid XML sitemap, with this structure:
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
 <loc>https://coursesweb.net/</loc>
 <priority>0.5</priority>
 <changefreq>weekly</changefreq>
</url>
<url>
 <loc>https://coursesweb.net/php-mysql/</loc>
 <priority>0.5</priority>
 <changefreq>weekly</changefreq>
</url>
.....
</urlset>

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Click on the HTML tag which creates an horizontal line in web page.
<br /> <em> <hr />
Some content ...
<hr />
Content under line ...
Which CSS property defines the text color?
font-style font-variant color
h2 {
  color: #cbdafb;
}
Click on the function which searches if a character, or text exists in a string.
indexOf() toString() split()
var str = "Web courses - http://CoursesWeb.net/";
if(str.indexOf("http://") == -1) alert("http:// isn`t in string");
else alert("http:// is in string");
Which function splits a string into an array of strings based on a separator?
array_merge() explode() implode()
$str = "apple,banana,melon,pear";
$arr = explode(",", $str);
var_export($arr);      // array (0=>"apple", 1=>"banana", 2=>"melon", 3=>"pear")
XML sitemap with data from MySQL

Last accessed pages

  1. Input text fields (2285)
  2. Insert, Select and Update NULL value in MySQL (30219)
  3. Register and show online users and visitors (25571)
  4. JavaScript Slideshow Content (855)
  5. Get Attribute (ID, Class, Name, Title, Src) with jQuery (55241)

Popular pages this month

  1. Insert, Select and Update NULL value in MySQL (515)
  2. Read Excel file data in PHP - PhpExcelReader (430)
  3. PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files (319)
  4. PHP getElementById and getElementsByTagName (255)
  5. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (234)