How to create a sitemap using php & mysql?
1 answer
by
june.crooks ,
6 months ago
@larry_orn
To create a sitemap using PHP and MySQL, you can follow the steps below:
- Start by setting up a MySQL database: Create a database and a table to store the URLs and other relevant information for the sitemap. Example:
1 2 3 4 5 6 7 8 |
CREATE DATABASE sitemap; USE sitemap; CREATE TABLE urls ( id INT AUTO_INCREMENT PRIMARY KEY, url VARCHAR(255), last_modified DATETIME ); |
- Create a PHP script to generate the sitemap XML: Include the necessary database connection code at the beginning of the script. Query the database to retrieve the relevant URLs and their last modified dates. Iterate over the result set and generate the XML structure for each URL. Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<?php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'sitemap';
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);
if (!$conn) {
die('Database connection failed: ' . mysqli_connect_error());
}
$query = "SELECT url, last_modified FROM urls";
$result = mysqli_query($conn, $query);
header('Content-type: text/xml');
echo '<?xml version="1.0" encoding="UTF-8"?>';
echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
while ($row = mysqli_fetch_assoc($result)) {
echo '<url>';
echo '<loc>' . $row['url'] . '</loc>';
echo '<lastmod>' . $row['last_modified'] . '</lastmod>';
echo '</url>';
}
echo '</urlset>';
mysqli_close($conn);
|
- Create a PHP script to add URLs to the database: Include the necessary database connection code at the beginning of the script. Retrieve the URLs from your website and insert them into the database table. Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<?php
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'sitemap';
$conn = mysqli_connect($dbHost, $dbUser, $dbPass, $dbName);
if (!$conn) {
die('Database connection failed: ' . mysqli_connect_error());
}
$urls = [
'http://example.com/page1',
'http://example.com/page2',
'http://example.com/page3',
];
$currentTime = date('Y-m-d H:i:s');
foreach ($urls as $url) {
$query = "INSERT INTO urls (url, last_modified) VALUES ('$url', '$currentTime')";
mysqli_query($conn, $query);
}
mysqli_close($conn);
|
- Set up a cron job or create a mechanism to periodically run the script to update the sitemap as new URLs are added or existing ones are modified.
Note: This is a basic example to get you started. You can expand on it to add more features like including metadata for each URL or generating different sitemap sections for different types of content.
15