Create XML with MySQL and PHP

XML is presented as a data exchange scale. Both Java and. NET support XML, but developers on other platforms do not have to worry about being abandoned by this technique. PHP is a popular script environment supporting XML. In this article

XML is presented as a data exchange scale. Both Java and. NET support XML, but developers on other platforms do not have to worry about being abandoned by this technique. PHP is a popular script environment supporting XML.

In this article, I will show you how to use open source tools such as PHP and MySQL to process XML. I will illustrate how to perform a MySQL Query and convert the data structure obtained from the query into XML. Finally, before writing the code, I will first write the XML into the file and check the system settings.

To run the code provided in this article, you need to run PHP and MySQL. to make full use of these examples, you also need to know the host name, user name, and password. The MySQL database pattern A in the example is shown. Let's see how to use PHP to connect to the database.

Use PHP to establish a database connection

The following PHP script establishes a connection to the database and performs a query:

Reference content is as follows:
$ Db_name = "xrandomusa_4 ";
$ Connection = mysql_connect ("MySQL.somewhere.com", "username", "password") or die ("cocould not connect .");
$ Table_name = 'page ';

Query MySQL

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

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

Now, use an SQL statement to select all rows in $ table_name:
$ Query = "select * from". $ table_name;

If necessary, you can add attributes later. Now, perform the following query:
$ Result = mysql_query ($ query, $ connection) or die ("cocould not complete database query ");
$ Num = mysql_num_rows ($ result );

You can use the PHP.net website to obtain reference materials for all MySQL functions.

Create and write XML

Now you are ready to create a new XML document. There are many ways to do this, but I think the methods used in listing A can meet most of the goals.

Now let's take this process into consideration. The variable num indicates whether the queried data row is displayed or not. it is measurable for MySQL's mysql_num_rows function. The variable $ file contains a pointer pointing to the file object generated when PHP successfully searches results. xml in the file system. If result. xml is found, your php file object and the specified file are created, and its attributes are writable. Now you can print the content of a variable to the created File (because your directory has been set to allow PHP to write files, there is no title for this ).

Be careful, from the security perspective, it is really clumsy to do so in the actual use of program development. To ensure that you can safely implement the concepts involved in this article, you should provide a complete path for the file you want to open and write, and ensure that the file is under your Web root directory.

Then, the mysql_fetch_array function of PHP converts the query variable $ result into an array and loops according to its key words. If pgaeTitle is in the column returned by the query, some text is written into the string variable $ _ xml for each returned row.

Pay attention to the operator ". =" (which is used to concatenate strings in XML format as values) to read $ row. When the loop ends, the root XML node is printed to the variable $ _ xml. all the variables are written into the file. xml file using the fwrite function of PHP.

Now, a link is displayed on the screen. Make sure that this link points to the path of your XML file, otherwise you will not be able to see this structured XML (this file is generated when PHP is used to process MySQL queries ).