HowTo: Dump your MySQL records right to XML File

Posted by morad
September 20, 2007

If you are going to be using Ajax to pull something out from your database for every single key stroke or trigger the user makes, then you better think twice! Unless, you are not expecting for your site to become that popular..

But anyway, apparently things are faster with accessing files than DB. And this is specially useful for blogs, CMS files, or anything that keeps getting retrieved over and over again.

Let’s begin:

MySQL
Let’s say we have a table that is called “HateList” and that list keeps growing bigger and bigger and everyone wants to see who you hate… and let’s say this is the structure of it:

Name Reason
John Doe Smashed my screen
Many Monai Stole my mouse
Linda Alinda Corrupted my file 
CREATE TABLE `hate_list` (
 `id` integer(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
 `name` varchar(25) NULL,
 `reason` varchar(150) NULL
 )

The Actual Code: PHP
Here is how you create XML file right from your database

<?php
//Step 1
header("Content-type: text/xml");
//Step 2
$hostname = "localhost";
$username = "root";
$password = "";
$database = "lab";

$ConnectionID = mysql_connect($hostname, $username, $password) or die("Could not connect to host.");
mysql_select_db($database, $ConnectionID) or die("Could not find database.");

$query = "SELECT * FROM hate_list ORDER BY id DESC";
$mysql_result = mysql_query($query, $ConnectionID) or die("Data not found.");
//Step 3
$xml_output = "<?xml version=\"1.0\"?>n";
$xml_output .= "<entries>n";
//Step 4
for($x = 0 ; $x < mysql_num_rows($mysql_result) ; $x++){
 $row = mysql_fetch_assoc($mysql_result);
 $xml_output .= "t<entry>n";

// Escaping illegal characters
 $row['name'] = str_replace("&", "&", $row['name']);
 $row['name'] = str_replace("<", "<", $row['name']);
 $row['name'] = str_replace(">", ">", $row['name']);
 $row['name'] = str_replace("\"", """, $row['name']);
//Adding the clean version into the output variable
 $xml_output .= "\t\<name>" . $row['name'] . "</name>n";

 // Escaping illegal characters
 $row['reason'] = str_replace("&", "&", $row['reason']);
 $row['reason'] = str_replace("<", "<", $row['reason']);
 $row['reason'] = str_replace(">", ">", $row['reason']);
 $row['reason'] = str_replace("\"", """, $row['reason']);

//Adding the clean version into the output variable
 $xml_output .= "\t\t<reason>" . $row['reason'] . "</reason>n";
 $xml_output .= "\t</entry>n";
}
//Step 5
$xml_output .= "</entries>";

echo $xml_output;

?>

Let me explain how the heck this code works:

  1. Specify the type of output of the file is XML.
  2. It connects you to the database to retrieve the data you want
  3. Save the result into a variable
  4. Loop through the result of the SQL query and insert every loop into the variable that we started with. And parses characters out that will mess the XML output.
  5. End the XML output with a closing tag and then echo it if you desire :)

Now that was simple and here what you should kinda get when you run it on your fav. browser:

Powered by Qumana

Sphere: Related Content

Print This Post Print This Post
Uncategorized

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)