How to create XML with PHP Mysql
 Rating: 1 Star2 Stars3 Stars4 Stars5 Stars
Loading ... Loading ...

How To Create XML With PHP MySQL



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.

xml php mysql How To Create XML With PHP MySQL

Establishing a database connection with PHP
The following sample PHP script connects to the database and executes a query:

If necessary, you can add attributes later. For now, execute the query like so:

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:

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.

Related Posts

php-domain-information

How To Get Domain, Subdomain, TLD, CTLD & Path from URL In PHP

braintree-payment-gateway-integration-php

Integrating BrainTree Payment Gateway With PHP

RBS WorldPay Integration With PHP

Integrating RBS WorldPay XML Direct Method with PHP

wordpress Posting Content via XML RPC

WordPress XMLRPC – Posting Content From Outside WordPress Admin Panel

  • http://www.ideaglow.com/ idea management tool

    MY 100% vote goes to this excellent script.I am totally agree to read this nice article.It’s a knowledgeable post of this blog. 

  • AABBCCD

    The post is full of knowledge.I use all your codes and it has a knowledgeable experience .really your post help me a lot to understand XML,PHP…..I am waiting for your updates.
    windows
    1079

    thanks.