From MySQL to XML with PHP

More...

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.

This entry was posted on January 7, 2009 and is filed under PHP

Related posts

27 Responses to From MySQL to XML with PHP

  1. aquib says:

    hi I am using

    in an php page to create xml response ,working perfectly on WAMP server but not on domain server, can any one help me on this.....

  2. dbunic says:

    @aquib - Please check PHP configuration for your domain server and compare it to your WAMP server. Please verify if you have installed php-xml package on domain server. If your domain server is in production then is possible that error messages are turned off but you can redirect all error messages to the file.

    Any error message is more than welcome when analyzing this problem.

  3. Kim says:

    Hmm does the php configuration work similar to a LAMP install like this? http://draalin.com/installing-lamp-in-ubuntu/

    Or is it better to install it manually?

    thanks

  4. dbunic says:

    @Kim - It's always better to use packet manager for Apache, MySQL and PHP installation. This way all packet dependencies will be resolved automatically. I use RedHat Linux family (Fedora, CentOS, RHEL) and everything is installed with rpm command. In case when RPM packet doesn't exist (and this is very rare case) then I try to build RPM package and install it.

    The similar situation is with Ubuntu and apt-get.

  5. tubidy says:

    thanks

  6. Nia Rianti says:

    Thanks for the information... How about PostgreSQL? can we do the same since it has slightly different syntax.

  7. dbunic says:

    @Nia Rianti - Yes, actually I'm using very similar code for building XML from Actian Ingres database. About decade ago, the same PHP code was used for Oracle. So, it is possible to make small modification and to customize it for PostgreSQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

In case of posting HTML tags or JavaScript code please convert special characters to HTML entities.
Especially pay attention to convert "<" character to "&lt;" entity!