Output mySQL data as XML with PHP

by Jubba   | filed under Web, HTML, CSS, and XML

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.

Well lets get started on the code. This first line is what tells the browser, and any other program that will be using this file, to interpret it as XML and not as PHP.

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

The next few lines are variables that hold our database connection values. Again I'm not going to go too indepth with this because you should already be familiar with database connection functions.

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

And now to connect to the mySQL server and select the database we are using, with the variables we defined before.

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

The next two lines are pretty straight-forward, just the query we want to run. Then using the mysql_query function to run the query and saving it to the $resultID variable.

$query = "SELECT * FROM blog ORDER BY date DESC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
Now we have to start with our XML output variable. I prefer to do it this way, its not the only way thats possible and it may not even be the best way. Anyway, I start saving the XML output to a variable. You should be familiar with XML syntax and the rules and such, so my variables look like this:

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

The next bunch of lines is a for loop that outputs our query data and it keeps looping it however number of times we have of rows in our database. Again, you should be familiar with the mysql functions. mysql_fetch_assoc() takes the current row in the database and outputs it as an associative array. This makes it easy to keep track of our info because when we want to output different fields we just put $arrayName['fieldName']. The three rows that use the str_replace are escaping the illegal XML characters that could possibly mess up the XML output and cause major errors. The reason that I only escaped one part of the output (the 'text' part) is because that is the only section of my blog that would ever have the possibility of containing those characters. Another thing to note about this part is how I output the data. I place the database output "$row['date']" in between the custom XML 'date' tags.

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("&", "&amp;", $row['text']);
        $row['text'] = str_replace("<", "&lt;", $row['text']);
        $row['text'] = str_replace(">", "&gt;", $row['text']);
    $xml_output .= "\t\t<text>" . $row['text'] . "</text>\n";
    $xml_output .= "\t</entry>\n";
}

I close out the XML output with the final upper-level closing tag.

$xml_output .= "</entries>";

And then output the XML string:

echo $xml_output;

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.

Jubba

Just a final word before we wrap up. What you've seen here is freshly baked content without added preservatives, artificial intelligence, ads, and algorithm-driven doodads. A huge thank you to all of you who buy kirupa's books, became a paid subscriber, watch the videos, and/or interact on the forums.

Your support keeps this site going! 😇