PHP XML generator can be written in less then 100 lines. In this example, data from the MySQL database are converted to the XML. PHP function sql2xml has an optional second parameter to describe the XML hierarchy, while first parameter is SQL query. Second parameter has the form of integers separated by commas.
For example, to describe XML structure in three levels, second parameter can be "2,3". Each integer defines number of columns from SQL in XML hierarchy. The last level is not necessary to specify.
Here is example of how to use the sql2xml PHP function.
<? include('sql2xml.php') ?>
<DOCUMENT>
<? sql2xml('select a.alb_id, a.alb_name,
s.sng_number, s.sng_name
from album a, song s
where a.alb_id = s.alb_id and s.sng_number < 3
order by a.alb_id, s.sng_number', '2') ?>
</DOCUMENT>
PHP function will transform SQL to the XML and here is the result. Column names from the SQL are used for XML tags. This example has a hierarchical structure in two levels because only first level is defined. Two columns (alb_id and alb_name) are the first level, while the other columns from the SQL are in the second level.
<DOCUMENT>
<ROW0>
<ALB_ID>1</ALB_ID>
<ALB_NAME>Nevermind</ALB_NAME>
<ROW1>
<SNG_NUMBER>1</SNG_NUMBER>
<SNG_NAME>Breed</SNG_NAME>
</ROW1>
<ROW1>
<SNG_NUMBER>2</SNG_NUMBER>
<SNG_NAME>Come As You Are</SNG_NAME>
</ROW1>
</ROW0>
<ROW0>
<ALB_ID>2</ALB_ID>
<ALB_NAME>Band of Gypsys</ALB_NAME>
<ROW1>
<SNG_NUMBER>1</SNG_NUMBER>
<SNG_NAME>Who Knows</SNG_NAME>
</ROW1>
<ROW1>
<SNG_NUMBER>2</SNG_NUMBER>
<SNG_NAME>Machine Gun</SNG_NAME>
</ROW1>
</ROW0>
</DOCUMENT>
PHP function will make connection to the MySQL database and transform SQL to the XML. Please, don't forget to set MySQL username and password in mysql_pconnect line at the beginning of sql2xml().
/**
* sql2xml prints structured XML
*
* @param string $sql - SQL statement
* @param string $structure - XML hierarchy
* @param integer $indent - starting indent (number of spaces)
*/
function sql2xml($sql, $structure=0, $indent=0){
// init variables for row processing
$row_current = $row_previous = null;
// set MySQL username and password
$db_cn = mysql_pconnect('localhost', 'username', 'password');
mysql_select_db('test', $db_cn); // connect to the database test
$result = mysql_query($sql, $db_cn);
// get number of columns in result
$ncols = mysql_num_fields($result);
// is there a hierarchical structure
if ($structure == 0) {$deep = -1; $pos = 0;}
else{
$hierarchy = explode(',', $structure); // hierarchy levels
$deep = count($hierarchy); // number of levels
// set flags for opened tags
for ($i=0; $i <= $deep; $i++) $tagOpened[$i] = false;
// set initial row
for ($i=0; $i < $ncols; $i++) $rowPrev[$i] = microtime();
}
// loop through result set
while($row = mysql_fetch_row($result)){
// loop through hierarchy levels (data set columns)
for ($level=0,$pos=0; $level<$deep; $level++){
// prepare row segments to compare
for ($i=$pos; $i < $pos+$hierarchy[$level]; $i++){
$row_current .= trim($row[$i]);
$row_previous .= trim($rowPrev[$i]);
}
// test row segments between row_current and row_previous
if ($row_current != $row_previous){
// close current tag and all tags below
for ($i=$deep; $i >= $level; $i--){
if ($tagOpened[$i]) printf('%'.($i*2+$indent)."s</ROW%d>\n", '', $i);
$tagOpened[$i] = false;
}
// reset the rest of rowPrev
for ($i=$pos; $i < $ncols; $i++) $rowPrev[$i] = microtime();
// set flag to open
$tagOpened[$level] = true;
printf('%'.($level*2+$indent)."s<ROW%d>\n", '', $level);
// loop through hierarchy levels
for ($i=$pos; $i < $pos+$hierarchy[$level]; $i++){
$name = strtoupper(mysql_field_name($result, $i));
printf('%'.(($level+1)*2+$indent)."s<%s>%s</%s>\n",
'', $name, trim(htmlspecialchars($row[$i],$i)), $name);
}
}
// increment row position
$pos += $hierarchy[$level];
// reset row segments (part of columns)
$row_current = $row_previous = '';
}
// print rest
printf('%'.($level*2+$indent)."s<ROW%d>\n", '', $level);
for ($i=$pos; $i < $ncols; $i++){
$name = strtoupper(mysql_field_name($result, $i));
printf('%'.(($level+1)*2+$indent)."s<%s>%s</%s>\n",
'', $name, trim(htmlspecialchars($row[$i],$i)), $name);
}
printf('%'.($level*2+$indent)."s</ROW%d>\n", '', $level);
$rowPrev = $row; // remember previous row
}
// close opened tags
for ($level=$deep; $level>=0; $level--)
if ($tagOpened[$level])
printf('%'.($level*2+$indent)."s</ROW%d>\n", '', $level);
}
Here are create table statements for tables album and song used in this example.
create table album (
alb_id int(11) not null auto_increment,
alb_name varchar(32) not null,
PRIMARY KEY (alb_id)
);
insert into album values (1,'Nevermind'), (2,'Band of Gypsys');
create table song (
sng_id int(11) not null auto_increment,
alb_id int(11) not null,
sng_number int(11) not null,
sng_name varchar(64) not null,
PRIMARY KEY (sng_id)
);
insert into song values
(1, 1, 1,'Breed'),
(2, 1, 2,'Come As You Are'),
(3, 1, 3,'Drain You'),
(4, 1, 4,'Endless, Nameless'),
(5, 1, 5,'In Bloom Lyrics'),
(6, 1, 6,'Lithium'),
(7, 1, 7,'Lounge Act'),
(8, 1, 8,'On A Plain'),
(9, 1, 9,'Polly'),
(10,1,10,'Smells Like Teen Spirit'),
(11,1,11,'Something In The Way'),
(12,1,12,'Stay Away'),
(13,1,13,'Territorial Pissings'),
(14,2, 1,'Who Knows'),
(15,2, 2,'Machine Gun'),
(16,2, 3,'Changes'),
(17,2, 4,'Power to Love'),
(18,2, 5,'Message to Love'),
(19,2, 6,'We Gotta Live Together');
In my next post From MySQL to HTML with PHP and XML, you can find how to transform XML from this example to the HTML. Sounds a bit complicated, but if the WEB architecture is set in this way, you will have a separate presentation layer with cleaner and simpler PHP code.
