Have you ever needed to pull data from a MySQL database and use it in a program other than a web browser? Usually when we’re displaying data from a MySQL database we just SELECT it from the db and echo it out to the page. But what if we wanted to do something different, like pull data from a database and display it in a Flash application? One excellent way to do that is to generate an XML file from a php page.

For this tutorial, we’ll use the cliche book example. Lets say we have a database full of books, and we want to generate an XML file that lists every book’s title, author and publisher.

To generate this script we would need to loop through each item in the database, pull it’s corresponding information, and echo it out between some XML tags. For arguments sake, lets say that the table only has fields named ‘title’, ‘author’ and ‘publisher’. Your php code would look something like this:



Please note that the script above does not contain any formatting. If you were to actually echo that result to the page, you would get a single line of XML. If you wish to echo the page out to the browser, you would put <br> tags at the end of each item. If you were echoing out to a file, such as with the fwrite() function, you would want to place a \n newline character at the end of each line. Here’s a break down of the script

Step 1:
Select all the information from the database and run a query on it. Our table consists of only title, author and publisher fields. If you have a larger table you’ll need to specify which fields to select from, rather than using the * selector.

Step 2:
Echo out the first tag for your XML file. We are aiming for the final result to look like:


<books><book><title>The Blue Nowhere</title><author>Jeffrey Deaver</author><publisher>Pocket Books</publisher>

Step 3:
Loop through each entry in our database and display it’s information to the screen, between the proper tags. We use the mysql_fetch_assoc() function to fetch the results one entry at a time.

Step 4:
Close out your final XML tag. You’ll probably want to make sure you include the proper headers at the top if you feel like they’re needed. As usual this tutorial just covers the basics to get you by.

Once you run that script you’ll be good to go! Voila! Instant XML from a MySQL database. The information can then be parsed into whatever program you need it in, be it Flash, .NET, an RSS feed, or whatever.

I specifically use this method to integrate a MySQL database with a small Flash application I wrote for a website at work. Remember that the above tutorial only echos the xml back to the user. If you wanted to save it to a file you would have to use the fopen(), fwrite(), and fclose() functions. For now, have fun programming, and I hope this post helps alleviate some frustrations in the future. Take care.