From MySQL to XML with PHP

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 optional second parameter to describe the XML hierarchy, while first parameter is SQL query. Second parameter is string in form of integers separated by commas. For example, to describe XML structure in three levels, second parameter can look like “2,3”. Each integer defines number of columns in SQL query. The last level is not needed to specify.

Here is example of how to use the sql2xml PHP function.

<?php include('sql2xml.php') ?>

<DOCUMENT>
<?php 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 < 4
               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() function.

<?php
/**
 * sql2xml prints structured XML
 *
 * @param string  $sql       - SQL statement
 * @param string  $structure - XML hierarchy
 */
function sql2xml($sql, $structure = 0) {
    // init variables for row processing
    $row_current = $row_previous = null;
    // set MySQL username/password and connect to the database
    $db_cn = mysql_pconnect('localhost', 'username', 'password');
    mysql_select_db('test', $db_cn);
    $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 {
        // set hierarchy levels and number of levels
        $hierarchy = explode(',', $structure);
        $deep = count($hierarchy);
        // 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
            // it should be "!==" and not "!="
            if ($row_current !== $row_previous) {
                // close current tag and all tags below
                for ($i = $deep; $i >= $level; $i--) {
                    if ($tagOpened[$i]) {
                        print "</ROW$i>\n";
                    }
                    $tagOpened[$i] = false;
                }
                // reset the rest of rowPrev
                for ($i = $pos; $i < $ncols; $i++) {
                    $rowPrev[$i] = microtime();
                }
                // set flag to open
                $tagOpened[$level] = true;
                print "<ROW$level>\n";
                // loop through hierarchy levels
                for ($i = $pos; $i < $pos + $hierarchy[$level]; $i++) {
                    $name = strtoupper(mysql_field_name($result, $i));
                    print "<$name>";
                    print trim(htmlspecialchars($row[$i],$i));
                    print "</$name>\n";
                }
            }
            // increment row position
            $pos += $hierarchy[$level];
            // reset row segments (part of columns)
            $row_current = $row_previous = '';
        }
        // print rest
        print "<ROW$level>\n";
        for ($i = $pos; $i < $ncols; $i++) {
            $name = strtoupper(mysql_field_name($result, $i));
            print "<$name>";
            print trim(htmlspecialchars($row[$i],$i));
            print "</$name>\n";
        }
        print "</ROW$level>\n";
        // remember previous row
        $rowPrev = $row;
    }
    // close opened tags
    for ($level = $deep; $level >= 0; $level--) {
        if ($tagOpened[$level]) {
            print "</ROW$level>\n";
        }
    }
}
?>

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 separated presentation layer with cleaner and simpler PHP code. You can also download redips1.tar.gz (3KB) package. It contains files and examples from this and next post.