How To Convert MySQL Results To XML

[JustinK101]

Hello, I just got Dreamweaver CS3, and I love the built in SPRY framework. It allows you to create web applications that use a table listing, and details listing very quickly. Unfortunately the only data the SPRY framework can accept is XML. Is there a built in function to convert from a $row result from MySQL to XML?

 

Let me give an example:

 

Given the following MySQL table named customers:

|  id   |   first_name   |  last_name   |  date_created |
    0            John            Doe          2007-01-02
    1            Bob             Smith        2007-02-04
    2            Jill            Hill            2007-03-03 

 

And now the PHP to pull the data:

     $sql = "SELECT id, first_name, last_name, date_created FROM customers ORDER BY date_created DESC";
     $result = mysql_query($sql) or die(mysql_error());
     while($row = mysql_fetch_assoc($result))
     {
          //Get results here
     }

 

I need this to come back in proper XML such as:

 

<result>
    <{table_name}>
       <{column_1_name}>{column_1_data}</{column_1_name}>
       <{column_2_name}>{column_2_data}</{column_2_name}>
       <{column_3_name}>{column_3_data}</{column_3_name}>
       <{column_4_name}>{column_4_data}</{column_4_name}>
     </{table_name}>
    <{table_name}>
        <{column_1_name}>{column_1_data}</{column_1_name}>
       <{column_2_name}>{column_2_data}</{column_2_name}>
       <{column_3_name}>{column_3_data}</{column_3_name}>
       <{column_4_name}>{column_4_data}</{column_4_name}>
     </{table_name}>
     <{table_name}>
        <{column_1_name}>{column_1_data}</{column_1_name}>
       <{column_2_name}>{column_2_data}</{column_2_name}>
       <{column_3_name}>{column_3_data}</{column_3_name}>
       <{column_4_name}>{column_4_data}</{column_4_name}>
     </{table_name}>
</result>

 

Which using the table data above would look like:

 

<result>
    <customers>
       <id>2</id>
       <first_name>Jill</first_name>
       <last_name>Hill</last_name>
       <date_created>2007-03-03 </date_created>
     </customers>
    <customers>
        <id>1</id>
       <first_name>Bob</first_name>
       <last_name>Smith</last_name>
       <date_created>2007-02-04</date_created>
     </customers>
     <customers>
       <id>0</id>
       <first_name>John</first_name>
       <last_name>Doe</last_name>
       <date_created>2007-01-02</date_created>
     </customers>
</result>

 

Make sense? Thanks for the help.

[chigley]

<?php

$sql = "SELECT id, first_name, last_name, date_created FROM customers ORDER BY date_created DESC";
$result = mysql_query($sql) or die(mysql_error());

header("Content-Type: text/xml");

echo "<result>\n";

while($row = mysql_fetch_row($result)) {
echo "	<customers>\n	<id>{$row[0]}</id>\n	<first_name>{$row[1]}</first_name>\n	<last_name>{$row[2]}</last_name>\n	<date_created>{$row[3]}</date_created>\n	</customers>\n";
}

echo "</result>";

?>

 

Phew.. that took a while

 

EDIT: I did try to use tabs when outputting, but for some reason the forum is converting them to spaces

[JustinK101]

Chigley

 

Ok that is a start, but I want a function or class that is portable, meaning it will automatically grab the table name, and the number of fields selected in that table, the proper field labels, and proper data in each field.

 

So that the function or class may be applied to any MySQL select query, without manually doing any coding.

[Barand]

here's a general-purpose function

 

<?php
function table2xml($tablename) {
    $str = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
    $str .= "<result>\n";
    $res = mysql_query("SELECT * FROM `$tablename`") or die (mysql_error()."<p>$sql</p>");
    while ($row = mysql_fetch_assoc($res)) {
          $str .= "\t<$tablename>\n";
          foreach ($row as $fn => $val) {
              $str .= "\t\t<$fn>$val</$fn>\n";
          }
          $str .= "\t</$tablename>\n";
    }
    $str .= "</result>\n";
    
    return $str;
}

echo table2xml('oil');
?>

[JustinK101]

I'll give that a try Barand. Let you know what I find.

[JustinK101]

Barand:

 

That is perfect seems to work well. Anyway to make it so the query can be a passed in though, I dont want to define the query in the function, since I want to use this function for many different queries.

 

The second question I have is that I need the output of the function to write the code to a file. The SPRY framework must read in a XML file. So I guess it would be nice if the function could create a file, called results.xml and then stick everything in the file.

 

OK I just thought of a major flaw with this system. If everytime a user wants data they must run the query, create a file called results.xml and stick the data into the xml file, what happens if multiple people try to run the page at the same time? They would each try to create the results.xml file. Is it me, or is this going to be a huge probelm managing the results.xml file? How do I know when to delete the results.xml file? It must be deleted or else users have the possiblity of listing out old/obsolete data. This is because the MySQL database might have been changed, but since the XML file only gets updated when a select is preformed it will get out of sync/out of date.

[JustinK101]

I think I figured out how to make it work:

 

Every time a query select is needed:

 

1.) Run the query

2.) Create results_random_number.xml on the server.

3.) SPRY reads in the results_random_number.xml file.

4.) Delete the results_random_number.xml file on the server.

 

results_random_number is needed, not just results.xml, in case more than one user tries to the run the query at the same time.

 

That process should work I think...

[Barand]

This one generates a unique file name , writes to it, returns the name of the file

 

<?php
function table2xml($tname, $query, $filepath) {
    $file = $filepath . uniqid() . '.xml';
    $fp = fopen ($file, 'w');
    
    fwrite($fp, "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
    fwrite($fp, "<data>\n");
    $res = mysql_query($query) or die (mysql_error()."<p>$sql</p>");
    while ($row = mysql_fetch_assoc($res)) {
          fwrite($fp, "\t<$tname>\n");
          foreach ($row as $fn => $val) {
              fwrite($fp, "\t\t<$fn>$val</$fn>\n");
          }
          fwrite($fp, "\t</$tname>\n");
    }
    fwrite($fp, "</data>\n");
    
    fclose($fp);
    
    return $file;
}

/**
* call it 
*/

$xmlfile = table2xml('customer', 'SELECT * FROM customers', './');

[JustinK101]

Barand:

 

Hey I looked over the doc for uniqid, and quite honestly can say I don't fully understand. What is the difference in doing that versus rand() or maybe just md5(date("Y-m-d H:i:s"));

 

Thanks.

[Barand]

rand() does guarantee uniqueness