A PHP XML class for MySQL

Mysql|xml I admit I'm not a leader in PHP. However, after looking at some PHP information, I think there are some features that need to be added to handle database connections and consolidate XML. To do this, I think I can create a class that handles the connection to MySQL and uses the Domxml feature in PHP to provide XML output. Then I can declare this class anywhere in the PHP script and provide XML functionality when I need to use it.


I assume that people are using PHP because of his price tag: free. MySQL provides a free database solution for developers who need to add database functionality to the system. The disadvantage of these solutions is that they are somewhat complex to set up and manage.

The PHP version I use in this article is the PHP 4.3.4 for Win32, which can be downloaded from the PHP group. The MySQL version is the MySQL 4.0.16 for Win32, which can be obtained from mysql.com. MySQL installation is easy?? Just simply follow its instructions. PHP is a little bit more complicated.

In the PHP download page There are two files: a zip file and an installation file. Because we need to add extensions to the zip file, all two files are downloaded. Here's a simple step to do after downloading:

1. Install PHP with installation files.

2. Unzip the Iconv.dll and put it in the Windows System folder.

3. Create a directory under the PHP installation directory (default is c:php) "extensions".

4. Extract Php_domxml.dll files to this directory.

5. Locate the php.ini file under Windows folder, and then open it using Notepad or another text editor. Locate "extensions_dir=" in this file and modify its value to the full path of the extended folder set in step 3rd.

6. Find "; Extension=php_domxml.dll" and delete the semicolon at the beginning of the line.

7. Restart the Web server.

Then use the following code in your Web directory to create a PHP page "test.php". (This code works correctly with Windows SP3, which is running IIS 5.0.) ）

<?php

$myxml = new Cmysqlxml ("localhost", "test_user", "Password", "Test");

echo $myxml->run_sql_return_xml ("SELECT * from users");

Classcmysqlxml {

var $host;

var $user;

var $password;

var $db;

Functioncmysqlxml ($host, $user, $password, $db) {

$this->host = $host;

$this->user = $user;

$this->password = $password;

$this->db = $db;

}

Functionrun_sql_return_xml ($sql _string) {

$connection = mysql_connect ($this->host, $this->user, $this->password,

$this->db);

mysql_select_db ($this->db);

$result = mysql_query ($sql _string);

$doc = Domxml_open_mem ("<root/>");

while ($row = Mysql_fetch_array ($result, Mysql_assoc)) {

$num _fields = Mysql_num_fields ($result);

$row _element = $doc->create_element (mysql_field_table ($result, 0));

$doc _root = $doc->document_element ();

$row _element = $doc _root->append_child ($row _element);

for ($i = 0; $i < $num _fields; $i + +) {

$field _name = Mysql_field_name ($result, $i);

$col _element = $doc->create_element ($field _name);

$col _element = $row _element->append_child ($col _element);

$text _node = $doc->create_text_node ($row [$field _name]);

$col _element->append_child ($text _node);

}

}

Mysql_free_result ($result);

Mysql_close ($connection);

Return $doc->dump_mem (false);

}

}
This example requires you to have a database "test" on MySQL with a table of "users." Also, you need to create a user for accessing the data on the test database. Steps to create a database, table, and so on to view the MySQL documentation.


If you analyze the code, you will understand that I created a class called Cmysqlxml. The Cmysqlxml constructor accepts four parameters: MySQL's hostname, a valid username, a password, and a database name. The constructor uses these four parameters to set the host, user, password, and DB member variables of the class.

The only one method provided by this class is Run_sql_return_xml (). It accepts an SQL query string parameter. When this method executes, it creates a connection to the MySQL database and selects the database. The query string is executed and the result is stored in the variable $result. Creates a new DOMDocument object using the Domxml_open_mem () function. The code then starts looping through all the records in the result set. For each record, add a row element with the same name as the table of the result set to the DOMDocument document element. Then add an element to the row element for each field, and the element name is the field name. Finally, a text node is added to each field node, and the value of the node is the value of the field.

After looping through all rows, the code releases the result set and closes the connection. The resulting DOMDocument XML is returned from the function.

At the beginning of the PHP page you will see that the Cmysqlxml object is instantiated and the Run_sql_return_xml () method is invoked. The return value of this method is returned to the customer. The Domxml feature adheres to the DOM specification in addition to the PHP function naming convention.

If you need more information about the DOM specification, you can access the Web site of the consortium. More domxml information can be found from the PHP group, where you can download documents in different formats.