Query processing with the help of PEAR::XML
Project that works on XML technologies requires another method of the document formatting.
How can we form the XML data received from the database after adding to the project XSLT support?
<?php
$result = mysql_query("SELECT
DATE_FORMAT (news_date, "%e.%c.%Y") AS date_typed,
DATE_FORMAT(news_date, "%d.%m.%Y") AS date_url,
title,
announce
FROM
news
ORDER BY news_date
DESC LIMIT 10
");
if(!mysql_error()) {
$xml .= "\r\n";
while($row = mysql_fetch_assoc($result))
$xml .= "
{$row[date_url]}
{$row[date_typed]}
{$row[announce]}
\r\n";
$xml .= "\r\n";
}
?>
Date in that example is formatted by database and returned in the required format.
Class uses another class of the PEAR library for accessing the database - PEAR::DB. It uses DOM XML function for working with XML document. It converts the result of the query to the XML tree and returns it like XML document text or DOM object.
PEAR installing
For calling the PEAR::DB you have to run the following command:
include_once("DB/${type}.php");
If the included file isn’t found, PHP searches it in its directory or in the directory where PEAR is installed. As you can see files are included relatively include_path. If include_path isn’t set you can set it in the .htaccess by means of the following line:
php_value include_path my_dir/pear
Instead of my_dir insert the address of the required directory
<?php
ini_set("include_path", "my_dir/pear");
include("DB.php");
$dsn = "mysql://user:pass@host/db_name";
$db = DB::connect($dsn, true);
if (DB::isError($db)) {
die ($db->getMessage());
}
$db->setFetchMode(DB_FETCHMODE_ASSOC);
ini_restore("include_path");
?>
After PEAR files including, it is better to return include_path by means of the ini_restore command.
If php is used in the safe mode by host-provider include_path can’t be changed. In that case you can put PEAR files to the site root or edit include in files manually.
DOM XML in PHP
DOM (Document Object Model) is a model of working with document. DOM model is a W3C standard.
When you work with DOM you operate with variables.
Examples of working with DOM XML:
<?php
// XML-document creating
$xmldoc = domxml_new_doc("1.0");
// There is created my_root element.
$my_element = $xmldoc->create_element("my_root");
// after that that element is appended to the document as a child-node
$my_root = $xmldoc->append_child($my_element);
// There is created another element – text.
It is appended as a parent node.
$my_element = $xmldoc->create_text_node
(iconv("windows-1251", "UTF-8", "It is the content of the
//root node of the XML-document."));
$my_root->append_child($my_element);
print($xmldoc->dump_mem());
?>
The following example shows the principle of the elements deleting:
<?php
$xmldoc = domxml_open_mem(<?xml version="1.0" encoding="windows-1251"?>
Something
");
$my_root = $xmldoc->document_element();
// my_root children are recorded to the array $children
$children = $my_root->child_nodes();
// First child is deleted (node something)
$children[0]->unlink_node();
// There is created new node named “new” and is recorded to the variable $new
$new = $my_root->append_child($xmldoc->create_element("new"));
$new->append_child($xmldoc->create_text_node(iconv("windows-1251", "UTF-8",
"It is the content of the new node of the XML-document.")));
print($xmldoc->dump_mem());
?>
As a result you’ll get the following XML-document:
<?xml version="1.0" encoding="windows-1251"?>
<my_root><new> It is the content of the
new node of the XML-document.</new></my_root>
If you change the initial XML document to:
<?php
$xmldoc = domxml_open_mem("<?xml version="1.0" encoding="windows-1251"?>
Something
");
?>
The result will be as follows:
<?xml version="1.0" encoding="windows-1251"?>
<my_root><something>?????</something>
<new> It is the content of the new node of the XML-document.</new></my_root>
SQL2XML class
That class includes all required functionality for transformation the SQL-queries results to XML. For connection with database class uses either current connection of PEAR::DB class or its own connection (it creates inside itself the DB class object). According to the query result there is created the XML tree. For example:
mysql> select * from bands;
+----+--------------+------------+-------------+-------------+
| id | name | birth_year | birth_place | genre |
+----+--------------+------------+-------------+-------------+
| 1 | The Blabbers | 1998 | London | Rock'n'Roll |
| 2 | Only Stupids | 1997 | New York | Hip Hop |
+----+--------------+------------+-------------+-------------+
mysql> select * from albums;
+----+---------+------------------+------+-----------------+
| id | bandsID | title | year | comment |
+----+---------+------------------+------+-----------------+
| 1 | 1 | BlaBla | 1998 | Their first one |
| 2 | 1 | More Talks | 2000 | The second one |
| 3 | 2 | All your base... | 1999 | The Classic |
+----+---------+------------------+------+-----------------+
It is a data set. Here we have the class call and results. php-code:
<?php
include_once("XML/sql2xml.php");
$sql2xmlclass = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xmlclass->getxml("select * from bands");
?>
XML-document:
<?xml version="1.0"?>
<root>
<result>
<row>
<id>1</id>
<name>The Blabbers</name>
<birth_year>1998</birth_year>
<birth_place>London</birth_place>
<genre>Rock'n'Roll</genre>
</row>
<row>
<id>2</id>
<name>Only Stupids</name>
<birth_year>1997</birth_year>
<birth_place>New York</birth_place>
<genre>Hip Hop</genre>
</row>
</result>
</root>
The result can be printed either as a text or DOM object. Also you can take only one value by means of XPath. We can also make queries like integrated nodes. php-code:
<?php
include_once("XML/sql2xml.php");
$sql2xml = new xml_sql2xml("mysql://username:password@localhost/xmltest");
$xmlstring = $sql2xml->getxml
("select * from bands left join albums on bands.id = bandsID");
?>
XML-document:
<?xml version="1.0"?>
<root>
<result>
<row>
<id>1</id>
<name>The Blabbers</name>
<birth_year>1998</birth_year>
<birth_place>London</birth_place>
<genre>Rock'n'Roll</genre>
<row>
<id>1</id>
<bandsID>1</bandsID>
<title>BlaBla</title>
<year>1998</year>
<comment>Their first one</comment>
</row>
<row>
<id>2</id>
<bandsID>1</bandsID>
<title>More Talks</title>
<year>2000</year>
<comment>The second one</comment>
</row>
</row>
<row>
<id>2</id>
<name>Only Stupids</name>
<birth_year>1997</birth_year>
<birth_place>New York</birth_place>
<genre>Hip Hop</genre>
<row>
<id>3</id>
<bandsID>2</bandsID>
<title>All your base...</title>
<year>1999</year>
<comment>The Classic</comment>
</row>
</row>
</result>
</root>
If you want to get the typical result of the query you can disable that property. You can also change nodes names for result and row. If you want another format you can transform DOM object to the required format.
