\"\"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":