How to create XML file dynamically using PHP and MySQL

Page Visited: 48030
The following are the step by step guildelines for creating XML sitemap file dynamically with PHP and MySQL:
1. Connect to your MySQL database and create a table:
CREATE TABLE `blog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`blogtitle` varchar(150) DEFAULT NULL,
`blogdesc` varchar(255) DEFAULT NULL,
`recdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `urlid` (`urlid`)
);
2. Create a connection file named "db_config.php":
$username = "YourUserName";
$password = "YourPassword";
$host = "localhost"; //mention here the actual hostname
$dbname = "YourDatabaseName";
?>
3. Create XML generation file named "xml_gen.php":
include "db_config.php"; // your database connection file
//variables definition
$XMLFileName = "xmlfilename.xml";
$XMLText = '';
//open file and write data into it from the database object
$File = fopen($XMLFileName, 'w') or die("The file could not be opened");
$XMLText .= '';
$XMLText .= '
xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">';
// Select Data
$query = 'select
concat("",
char(10),
"","http://www.yoursitename.com/blog/",urlid,"",
char(10),
"",convert(date_format(recdate,"%Y-%m-%d") using latin1),"",
char(10),
"weekly",
char(10),
"0.80",
char(10),
"",
char(10)) AS XML_PART
from blog';
try {
$stmt = $db->prepare($query);
$result = $stmt->execute();
} catch (PDOException $ex) {
die("Failed to run query: " . $ex->getMessage());
}
$row = $stmt->fetch();
$stmt = $db->query($query);
if ($stmt->rowCount() > 0) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$XMLText .= $row["XML_PART"] . chr (10);
}
}
$XMLText .= '';
fwrite($File, $XMLText);
fclose($File);
?>
4. Here is the final out file "xmlfilename.xml":
User Comments
test
sfdsfdsfdsfda
aaaa
aaaa
aaaa
aaaa
aaaa
test 1
saaaa