Creating XML with MySQL and PHP

Mysql|xml| creates XML as a data exchange standard. Both Java and. NET support XML internally, but developers on other platforms do not have to worry about being abandoned by the technology. PHP is a fairly popular scripting environment that supports XML.


In this article, I'll introduce you to the process of using open source tools such as PHP and MySQL to work with XML. I'll illustrate this by executing a MySQL query and formatting the data from the query into XML. Finally, before I begin to immerse myself in writing code, I'll explain how to write XML to a file and detect system settings.

In order to be able to run the code given in this article, you need to be able to run PHP and MySQL, and to make the most of these examples, you need to know the hostname, username, and password. The MySQL database in the example format is shown in Figure A. Let's see how to connect to the database in PHP.

Figure A





Building a database connection with PHP

The following PHP script establishes a connection 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 ';




Query MySQL

After the MySQL connection is established, you must use the connection to establish the current database. The following code is used to accomplish this task:

$db = mysql_select_db ($dbName, $link);

Now, use an SQL statement to select all the rows in $table_name:
$query = "SELECT * from". $table _name;

If necessary, you can then add attributes. Now execute the query as follows:
$result = mysql_query ($query, $connection) or die ("could not complete database query");
$num = mysql_num_rows ($result);

You can get references to all MySQL functions through the php.net website.


Create and write XML


Now, you should be ready to set up a new XML document. There are a number of ways to do this, but I think the approach used in listing a can do most of the work.

Let's now describe the process in detail. The variable num indicates whether the data row you are querying is present or not, and its mysql_num_rows function for MySQL is measurable. The variable $file contains a pointer to the file object generated by PHP's successful search to Results.xml in the file system. If Result.xml is found, create your php file object and specify the file, and its properties are writable. Now you can print the contents of a variable to the created file (since your directory has been set to allow PHP to write files, so this is no problem).

Note that from a security standpoint, doing so in real-world application development is really stupid. To ensure that you can safely implement the concepts involved in this article, you should provide a full path to the file you want to open and write, and make sure that the file is located under your Web root directory.

Then, the PHP mysql_fetch_array function converts the query variable $result into an array and loops by its keyword. If pgaetitle in the column returned by the query, the return value for each row writes some text to the string variable $_xml.

Note that the operator ". =" (which is used to splice XML-formatted strings as numeric values) reads $row. When the loop ends, the root XML node prints into the variable $_xml, and all the variables are written to the File.xml file using the PHP fwrite function.

Now, a link appears on the screen. Make sure that the link points to the path to your XML file, otherwise you won't be able to see the formatted XML (the file is generated using PHP to process MySQL queries).