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 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. You can also download redips1.tar.gz (3KB) package. It contains files and examples from this and next post.

Related posts

• From MySQL to HTML with PHP and XML

This entry was posted on Wednesday, 07.01.2009. at 16:30and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.