converting MySQL queries to xml

Posted in mysql, php | Posted on 05-08-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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/**
 * @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;

1
2
3
4
5
6
7
8
9
10

.
.
/* 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.

1
2
3
4
5
6
7
8
9
10
11

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


No Comments
Del.icio.us | Digg | Stumble | Reddit

Write a Comment