Developer

Create XML with MySQL and PHP

PHP is a popular scripting environment that allows you to work with XML. Get an introduction to using open source tools such as PHP and MySQL to manipulate XML.


XML has emerged as a standard for data exchange. Java and .NET both include inherent XML support, but developers outside of these environments need not worry about being left out. PHP is a popular scripting environment that also allows you to work with XML.

In this article, I'll introduce you to using open source tools like PHP and MySQL to manipulate XML. I'll execute a MySQL query and format the data into well-formed XML. Finally, I'll explain how to write XML to a file and examine the system setup before diving into the code.

To use the code I've included in this article, you must have PHP and MySQL running, and you need to know your MySQL host name, username, and password to fully use the samples. The sample MySQL database has the format shown in Figure A. Let’s look at how to connect to the sample database with PHP.

Figure A


Establishing a database connection with PHP
The following sample PHP script connects to the database and executes a query:
 
 <?php
$db_name = "xrandomusa_4";
$connection = mysql_connect("MySQL.somewhere.com", "username", "password") or die("Could not connect.");
$table_name = 'pages';


Querying MySQL
With the connection made, you must establish the current database using the MySQL connection. The following code handles this task:
 
$db = mysql_select_db($dbName, $link);

Now, write a SQL statement to select all rows in $table_name.
$query = "select * from " . $table_name;

If necessary, you can add attributes later. For now, execute the query like so:
$result = mysql_query($query, $connection) or die("Could not complete database query");
$num = mysql_num_rows($result);


For reference, you can view all MySQL functions via the PHP.net Web site.

Build and write the XML
At this point, you're ready to create a new XML document. There are many ways to approach it, but I think the approach used in Listing A suffices for most purposes:

Here’s a breakdown of what’s happening. Variable num represents the presence of row data from your query, measurable using MySQL’s mysql_num_rows function. This leads us into your procedural output of the XML. Variable $file contains a pointer to the file object produced when PHP successfully reads the file system in search of results.xml. If results.xml is found, your PHP file object, named file, is created and made writeable. Now you can print the contents of a variable to it, which is what you’ll do because your directory permissions are set up to allow PHP to do this.

Keep in mind that for security reasons, this is a dumb thing to do in real-world Web applications. To make sure your implementation of the concepts covered in this article are secure, you should provide a full path to a directory containing the files you wish to open for writing, and make sure it’s in a directory above your Web root.

Next, PHP's mysql_fetch_array function converts the query variable $result to an array, and loops through its keys. If pageTitle was among the columns returned in the query, for each row returned, some XML-formatted text is written to string variable $_xml.

Note that the operator ".=", which is being used to append the XML-formatted strings as values, is read from $row. When the loop is finished, the root XML node is printed to variable $_xml, and the whole variable is written to file.xml using PHP’s fwrite function.

At this point, a link appears on the screen. Make sure this link points to the path of your XML file, or you won’t be able to see the formatted XML produced by PHP from your MySQL query.

Additional resources
Check out these links to learn more about the technologies used in this article:

A test drive
You can easily test the code by running it in a browser. If all goes well, you will see a new file in your directory called results.xml. This is the XML file created with the PHP. It should look like this:
 
<?xml version="1.0" encoding="UTF-8" ?>
<site>
<page title="Page One">
<file>http://www.yoursite.com/pageOne</file>
</page>
<page title="Page Two">
<file>http://www.yoursite.com/pageTwo</file>
</page>
</site>


Only the start of something bigger
The resulting code from this article would be useful as an include file in the context of a larger application. It is a tried-and-true technique for creating and updating XML documents, but it's not the only solution: There are other technologies that extend PHP—PEAR, for instance—that present some exciting alternatives.

 

Editor's Picks

Free Newsletters, In your Inbox