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.
Date |
Text |
09/03/2003 |
Welcome to my blog. This
is where I will whine about how unfair life is for a
middle-class white American adolescent male. |
10/03/2003 |
Oh! Tragedy, tragedy!
Angst-ridden life... |
11/03/2003 |
How come no one reads
this? Am I not interesting enough for you!? You all hate
me! Well I hate you too! |
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(">",
">",
$row['text']);
$row['text']
= str_replace("\"",
""",
$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("&",
"&",
$row['text']);
$row['text']
= str_replace("<",
"<",
$row['text']);
$row['text']
= str_replace(">",
">",
$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.