Nobody can deny the power and benefits of XML which is mostly used in Java and .Net environment extensively. Using XML in PHP is also very popular and you will notice many PHP based web applcations using XML to it’s extent. A popular PHP/Zend software Magento ecommerce is also using XML a lot which tells that XML is here to stay with PHP as well.
I am going to introduce you to using 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.
Establishing a database connection with PHP
The following sample PHP script connects to the database and executes a query:
$db_name = "mydatabase_name";
$connection = mysql_connect("localhost", "username", "password") or die("Could not connect.".mysql_error());
$db = mysql_select_db($dbName, $link);
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".mysql_error());
$num = mysql_num_rows($result);
PHP.net is a great source to view all PHP and even MySQL functions.
Create 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.
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" ?>
<page title="Page One">
<page title="Page Two">
This is just a very basic of XML created with PHP and MySQL. 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.
I would love to hear your experiences of using XML with PHP and MySQL. Please leave us a comment and let us know. Subscribe our RSS to receive latest PHP, MySQL and XML Updates.