converting MySQL queries to xml : CodeDiesel

converting MySQL queries to xml

Posted in: mysql, php | Save to del.icio.us | Twit This! 5 Aug 2008

There frequently arises a need to return mySQL query results in xml. Maybe you need to send the xml data to the browser or you want to use it as a xml request to a web service; whatever the application, the following function will return the result of a sql query in xml format.

The function accepts 3 parameters. The first is the mysql query result resource, the second is the name of the root element and the third the name of the first child of the root. All the fields from the mySQL table will be sub elements of this child.

The function is shown below:

/**
 * @param mysql_resource - $queryResult - mysql query result
 * @param string - $rootElementName - root element name
 * @param string - $childElementName - child element name
 */
function sqlToXml($queryResult, $rootElementName, $childElementName)
{ 
    $xmlData = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n"; 
    $xmlData .= "<" . $rootElementName . ">";
 
    while($record = mysql_fetch_object($queryResult))
    { 
        /* Create the first child element */
        $xmlData .= "<" . $childElementName . ">";
 
        for ($i = 0; $i < mysql_num_fields($queryResult); $i++)
        { 
            $fieldName = mysql_field_name($queryResult, $i); 
 
            /* The child will take the name of the table column */
            $xmlData .= "<" . $fieldName . ">";
 
            /* We set empty columns with NULL, or you could set 
                it to '0' or a blank. */
            if(!empty($record->$fieldName))
                $xmlData .= $record->$fieldName; 
            else
                $xmlData .= "null"; 
 
            $xmlData .= "</" . $fieldName . ">"; 
        } 
        $xmlData .= "</" . $childElementName . ">"; 
    } 
    $xmlData .= "</" . $rootElementName . ">"; 
 
    return $xmlData; 
}

How to use?
For example if we have a ‘company’ table with the following three fields:
id, companyid, name.

You can use the above function in the following manner to convert the table rows into xml;

.
.
/* Sql query */
$result = mysql_query("SELECT * from company");
 
/* If you want to process the returned xml rather than send it
    to the browser, remove the below line.
*/
header("Content-Type: application/xml");
echo sqlToXml($result, "companies", "company");

which returns the following xml.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<companies>
	<company>
		<id>1</id>
		<companyid>1</companyid>
		<name>microsoft</name>
        </company>
.
.
.
</companies>

Share this post

3 Responses

tony

November 26th, 2008 at 3:39 pm

This is great - thank you.

I was wondering where the best place to sanitize the data would be? by that I mean replace any XML special characters like & with & before outputting them in XML.

Oh - and the example works perfectly with MS SQL as well - as long as you change the function names:

mysql_fetch_object to mssql_fetch_object
mysql_num_fields to mssql_num_fields
mysql_field_name to mssql_field_name

www.redips.net | From MySQL to XML with PHP

March 17th, 2009 at 1:43 pm

Nice code, I also made my version of sql2xml, which is in production for several years … In combination with APC, it gives a good platform to build a fast Web site. Function accepts two parameters: first parameter is SQL and the second is hierarchy (optional). Hierarchy describes XML structure … Hope you will find interesting informations.
Bye!

SneakyWho_am_i

May 21st, 2009 at 2:34 am

Sanitization was the first thing to my mind, too. I was expecting a built-in, compiled function. Doh.

This function looks clever and useful but it’s not for me at all.

code-diesel