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.
is it possible to import data from XML to MySQL? if so can this be automated?
Any help would be greatly appreciated
Thanks
Yes, it’s possible to parse received XML document and save it to the MySQL database (actually, to the any database). You will have to create interface – perhaps in PHP – to extract XML nodes. I will point you to the SimpleXML PHP extension which provides a very simple and easily usable toolset to convert XML to an object that can be processed with normal property selectors and array iterators.
Hope this will help.
I have two tables. one is holding the gallery name
and the second table is holding the gallery name with the image name and caption.
I need to be able to output the xml script in the exact same way as you have yours here.
but when I typed in all your info, and followed all the tutorial on this page, nothing happened.
here is my code that I have now….
<?php
require(“config.php”);
function parseToXML($htmlStr)
{
$xmlStr=str_replace(”,’>’,$xmlStr);
$xmlStr=str_replace(‘”‘,’"’,$xmlStr);
$xmlStr=str_replace(“‘”,’'’,$xmlStr);
$xmlStr=str_replace(“&”,’&’,$xmlStr);
return $xmlStr;
}
// Opens a connection to a MySQL server
$connection=mysql_connect (‘localhost’, $username, $password);
if (!$connection) {
die(‘Not connected : ‘ . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die (‘Can\’t use db : ‘ . mysql_error());
}
// Select all the rows in the markers table
$query = “SELECT image FROM photogallery WHERE image ORDER BY title DESC”;
$result = mysql_query($query);
if (!$result) {
die(‘Invalid query: ‘ . mysql_error());
}
header(“Content-type: text/xml”);
// Start XML file, echo parent node
echo ”;
echo ”;
// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
echo ”;
echo ” . parseToXML($row[‘title’]) . ”;
echo ‘http://www.cbchangar.com/uploads/images/’ . $row[‘image’] . ”;
echo ” . $row[‘description’] . ”;
echo ”;
}
echo ”;
// End XML file
?>
how would I get it to output the xml like this
1
Gallery 1
2
Gallery 2
basically I need to have the php script output the mysql data, but it needs to list all images associated with the gallery name. so when the client updates the photo gallery, when they create a new gallery they give it a name… that name is stored into the database by gname. so the xml file would list say gallery 1 then list all the images that are tagged or associated with gallery 1, then it would close that gname tag and then go to gname again which would say gallery 2, and so forth.
any help would be greatly appreciated
Jasen,
as I can read from your comment, you have MySQL table “photogallery” with attributes gname, title and image (gname is gallery name). To produce XML hierarchy with sql2xml function from this post, you will have to create small PHP file:
<DOCUMENT>
</DOCUMENT>
Optional parameter ‘1’ means to have first column (gname) in level 1 while other columns from the select will be placed to the lower level. Please download redips1.tar.gz where you can find prepared examples and short readme.txt file. You will have to set MySQL username and password in sql2xml.php file (line 13) and edit test.php file (just replace select statement).
How do I get it to structure the xml file like so …
<gname>Cars</gname>
<imgName>Image 1</imgName>
<caption>Image 1 Caption</caption>
<gname>Jasen and Jenn</gname>
basically..your code works, I mean obviously..
but it does not format the xml into the above format.
it just lists it out in a straight line.
so the code is doing what it needs to do, but its not
structuring it.
am I missing something somewhere?
thanks
Jasen
Yes, sql2xml listed data in a straight line. I made a little modification in PHP code and instead of print, function uses printf, so XML output will be nicely indented. Function now has one optional parameter more – starting indent – to add additional spaces before printed line.
I will like to mention that each record (row) fetched from the database will be closed in <ROW></ROW> tags. Fetched records should be somehow grouped and nested if you need.
Anyway, XML should be well formed – properly opened and closed tags, to be parse able with XML processors. Indentation is nice to have but not necessary. You can try to save output from sql2xml to the XML file (don’t forget xml suffix) and open with FF or IE. Browser should recognize XML document type and display it indented – no matter if original XML is listed in straight line or not.
Please comment if anything has been left out or is not clear.
Dear Sir.
I have a “some.sql” file in my hand, just like the one you use for creating your “test” database, it’s 2 tables and their record.
So .. How to convert this file to XML format ?
Actualy the final result I need is XML format that compatible with PgSQL
Sincerely
-bino-
Bino – If you have LAMP server near to your hands, and “some.sql” is MySQL compatible, then sql2xml() can help you. Procedure can go:
1) save “some.sql” to the database
2) create XML with PHP function sql2xml()
I assume that generated XML will not be compatible with PgSQL, but you can apply XSLT on XML to get wanted XML format. Yes, that means to create XSL to transform XML to another XML. Whole procedure seems complicated. I’m just thinking, but did you try to modify “some.sql” like search and replace attribute types for PgSQL syntax – if the goal is to make “some.sql” compatible with PgSQL? Please comment if anything has been left out or is not clear.
Hi,
Is there a particular reason you use mysql_pconnect() instead of mysql_connect() in your code?
Thanks
@Jack – mysql_pconnect will try to find already opened database connection to avoid overhead regarding connecting and disconnecting to the database. This type of connection will not be closed after PHP script ends. Instead, it will be saved for future use. Please see documentation of mysql_pconnect for a more details.
On the other hand, if you don’t have possibility for Apache and MySQL tuning (like settings of max number of httpd childs or max number of persitent connections to the MySQL server), then mysql_connect will be just fine.
My only goal was to make sql2xml function more efficient.
Very cool. thank you very much..
George.
Thank you for sharing superb informations. Your web-site is so cool. Im impressed by the details that you have on this web site. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for extra articles. You, my pal, ROCK! I found just the info I already searched all over the place and just could not come across. What an ideal web-site.
Hey, is it possible to create individual names for row1, row2 and so on…
I would like to create an xml files for my order import (like SAP)
So i need a head like row0. It Contains the Ordernumber and something else.
Then i need a block like row 1 for the Adress (name like positions)
For Positions i also need a block like row 1 but with an individual name like “positions”
Can someone help me? (sorry for my poor english)
@geodeta – Thanks, I’m glad you like my work. Cheers!
@Michael – sql2xml() doesn’t have option to define names for hierarchy XML nodes. You are free to modify function and instead of lines:
… you can call your own function to print different node names. Something like this:
// open node myprint($level, ''); // or close node myprint($level, '/'); // or in case with $i variable myprint($i, '/'); ... ... // and the source of function function myprint($level, $slash) { // define custom node names $names = Array('name1', 'name2', 'name3'); // set node name $name = $names[$level]; // print node print "<$slash$name>\n"; }Another approach would be to use XSL and transform XML to other XML (needed for SAP import). Hope this tips will be helpful.
Thank you for sharing this script, which is working fine with my own data and has been very helpful. But I couldn’t get the function to replace ROW0, ROW1 etc. with my own names to work.
If I change
to
… then the script fails. Am I missing something?
@Stewart McKie – Did you include myprint() function to your PHP script? Just copy myprint() below sql2xml() and this should work.
Hi;
I want to get an xml file of this type (with attribute between the tags)
how to do.
Thank you in advance for your reply :)
@sameh – sql2xml PHP function generates XML from SQL. Every attribute in SQL select will be one node in XML. It is possible to define hierarchy and some nodes will be nested (placed deeper). sql2xml doesn’t write nor create any attribute in XML. All output from select will be written in nodes. As you can see, XML generator function is not complicated so you can make modification to fit your needs.
Hi there,
I’ve been able to get an XML file output from my database table, and it’s in a nice generic format (Thanks to Tony Marstens web site).
simon <simon ...However I’m after one more step.
simon simon ... ...Does anyone have any ideas on how I can achieve this?
My reason for this is that I have a legacy system that spits out a nice ‘flat’ SQL table. I need to send that to an external web service using XML.
I am a bit of a newb with PHP, however database stuff is great – Oracle/MSQL not so much though.
@btg – Is your question related to XML indentation? WordPress cut out most of comment so is not easy to understand your posted examples. If so, XML indent are not important for parsing. XML should be well-formed (element should be opened and subsequently closed, an empty element must be terminated and element should be properly nested so that it does not overlap).
On the other hand, indentation can increase the size of XML document. This can have impact in processing/parsing big XML documents.
If I missed the point, please send me an email with examples and I will publish your question as a comment.
Thanks