Introduction
This tutorial will show you how to take data that is stored in your mySQL database and easily turn it into XML. From there you can use the XML however you wish. This is useful for blogs, shoutboxes, guestbooks, or even larger things such as back-end CMS (content management systems) for your site or sites. While writing this tutorial I am making the assumption that if you are looking at this tutorial then you already have a decent grasp of some of the basics of PHP and also that you know basic XML syntax rules. I will not show you how to setup your database in this tutorial, nor will I go over every single PHP function that is used. Resources to check out are PHP: Introduction and PHP: mySQL With that said...

Most people just take their database and pull the data directly into Flash with the use of a server-side script. This isn't necessarily incorrect, but in the long-run it could be the most time consuming and most difficult to update or change if needed. This tutorial also doesn't limit you to Flash integration. You can use the XML for anything. Again many people will just take their data and use PHP to place the data into a guestbook or shoutbox or something like that. Outputting the data as XML may seem like an extra step that isn't really needed, but its not! Trust me!

Using the XML method helps in a number of ways. It helps to keep your data organized, makes it easy to integrate into a Flash environment, makes it easy to eventually transfer over to a Flash environment if you are currently using an HTML front-end. Also, if you couple this tutorial with the other PHP/XML tutorials I have written ({Beginner Parsing} - {Intermediate Parsing}) you can have a fully dynamic PHP/XML/SQL driven application.

mySQL set-up
One of the good things about this tutorial is that you will not have to change the structure of your database if you already have begun to fill it up. The only changes you will need to make are within the PHP script, but I will show you how to change what is needed for that later on in the tutorial. For now I will just show you how I set up my database for a simple blog.

Since the example for this tutorial is just a simple blog, I decided to only include the date and the text for each of the blogger's entries. More complex blogs could include reader responses, mood factors, music selections, but for simplicities sake I decided to include only two. I will show you how to manipulate the PHP in order to account for more in the next section.

Scripting The PHP
Keeping with the tradition of my other tutorials I will post the completed PHP code and then go through line by line and explain what each does.

<?php

header("Content-type: text/xml");

$host = "localhost";
$user = "root";
$pass = "";
$database = "test";

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");

$query = "SELECT * FROM blog ORDER BY date DESC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<entries>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "\t<entry>\n";
    $xml_output .= "\t\t<date>" . $row['date'] . "</date>\n";
        // Escaping illegal characters
        $row['text'] = str_replace("&", "&", $row['text']);
        $row['text'] = str_replace("<", "<", $row['text']);
        $row['text'] = str_replace(">", "&gt;", $row['text']);
        $row['text'] = str_replace("\"", "&quot;", $row['text']);
    $xml_output .= "\t\t<text>" . $row['text'] . "</text>\n";
    $xml_output .= "\t</entry>\n";
}

$xml_output .= "</entries>";

echo $xml_output;

?>


Easy, right? Well, there are three main steps to get this done. The first step is connecting to our database. I'm assuming you already know how to do that as well as issuing a query statement to extract data from the database. These are things that you must be proficient with before you actually tackle this project. I'm not going to go into much depth on those parts of the script.

Accounting for variation
Well from the script that we just went over you should see that adding more fields to your PHP script is easy, right? Say we have a field for mood similar to what you would see in online journal's like LiveJournal.com. We simply need to add this line:

$xml_output .= "\t\t<mood>" . $row['mood'] . "</mood>\n";

somewhere between the open "entry" tag and the closing "entry" tag.

Another thing you may want to change is the number of entries that show at one time. You may want to limit it to the last 5 or 6 entries rather than dumping the entire database into the XML. To do this you merely need to add a couple characters to your query string.

$query = "SELECT * FROM blog ORDER BY date DESC LIMIT 0, 5";

Notice how the only thing we changed was adding the "LIMIT 0, 5" to the end of the query. This changes it so that it will limit the number of entries from the most recent entry to the entry that is 5 posts deep.

Conclusion
Well that's about it! I hope it helped out with your blog or whatever you decide to use it for. Again, this tutorial assumes that you already have a fairly decent grasp of the PHP basics and the basics of the mysql functions in PHP.

If you have any questions the best place to ask would be on the forums in the Server-side Scripting Forum.