Phd research project - xml from MySQL with php

[j_milo_taylor]

hi there,

i am in my final year of a doctoral research program. i am developing an innovative immersive digital environment based on a mysql database. the final application will be a surround sound datascape dynamically generated in response to mysql queries, to be built in x3d. i guess i will be posting quite a lot in the next year with various problems i anticipate.

the first issue i have to address is to find the most efficient means of outputing xml from an mysql query, i am not really a programmer, and in researching this question found myself quite lost in what seems quite a problematic thing to do. i imagine this can be done with php but have had no success in trying the various tutorials i have found so far

can anybody advise me on this?

best regards

[alimadzi]

This shouldn't be too difficult. In a nutshell, I would say to fetch your data from the database and then build the XML with heredoc syntax. Something like this...

<?php

$row = mysql_fetch_assoc( mysql_query( "SELECT x_coord, y_coord, z_coord FROM coords WHERE id = '$foo'" ) );

$xml = <<<EOT
<?xml version="1.0"?> 
<dataPoint>
  <x_coord>$row[x_coord]</x_coord>
  <y_coord>$row[y_coord]</y_coord>
  <z_coord>$row[z_coord]</z_coord>
</dataPoint>
EOT;

?>

As you can see, I don't know the first thing about x3d, so I just made something up. If you need to retrieve multiple rows of data, just use a while() loop.

Keep the questions coming. This could be very interesting.

[j_milo_taylor]

alimadzi ,

thanks very much for your comment. i am working alone, am not really a coder, and it really good for me to here that this may actually be possible.

at this stage i am finding it pretty hard to negotiate all the different languages i am using. (mysql, xml, ajax, x3d) i would call myself a newbie at all of these.

right now i can't even figure out how to get an xml file from a mysql query. could you help me on this?

something like select * from artists where abstract like "%_search", when i've been working through various web tutorials i keep getting frustrating 'error, not well formed' in firefox

best regards
milo

[alimadzi]

Go ahead and post your code here so we can see what you're trying. Be sure to use the [ PHP ] tags in the forum editor so we can read it easily.

[TobyRT]

i can see this getting interesting too... i've had similar issues when looking at getting xml from a sqldb

[NogDog]

This is the method I use in my MySQL class. (Note that $this->result is a mysql_query() result resource ID.)

<?php
   /**
   *  str getXml()
   *  Purpose: get query results as an XML string
   */
   function getXml()
   {
      mysql_data_seek($this->result, 0);
      $xml = "<?xml version='1.0' encoding='ISO-8859-1'?>\n<data>\n";
      $count = 1;
      while($row = mysql_fetch_assoc($this->result))
      {
         $xml .= "  <record row='$count'>\n";
         foreach($row as $key => $val)
         {
            $xml .= "    <$key>$val</$key>\n";
         }
         $xml .= "  </record>\n";
         $count++;
      }
      $xml .= "</data>";
      return($xml);
   }  // end getXml()

The resulting string will look something like:

<?xml version='1.0' encoding='ISO-8859-1'?>
<data>
  <record row='1'>
    <id>1</id>
    <last_name>Jones</last_name>
    <first_name>Don</first_name>
    <age>33</age>
    <gender>m</gender>
  </record>
  <record row='2'>
    <id>2</id>
    <last_name>Smith</last_name>
    <first_name>Elizabeth</first_name>
    <age>25</age>
    <gender>f</gender>
  </record>
  <record row='3'>
    <id>3</id>
    <last_name>Doe</last_name>
    <first_name>Jane</first_name>
    <age>41</age>
    <gender>f</gender>
  </record>
  <record row='4'>
    <id>4</id>
    <last_name>Doe</last_name>
    <first_name>John</first_name>
    <age>41</age>
    <gender>m</gender>
  </record>
  <record row='5'>
    <id>5</id>
    <last_name>Johnson</last_name>
    <first_name>Arlene</first_name>
    <age>29</age>
    <gender>f</gender>
  </record>
  <record row='6'>
    <id>6</id>
    <last_name>Zimmerman</last_name>
    <first_name>Frances</first_name>
    <age>51</age>
    <gender>f</gender>
  </record>
  <record row='7'>
    <id>7</id>
    <last_name>Ajax</last_name>
    <first_name>Charles</first_name>
    <age>50</age>
    <gender>m</gender>
  </record>
  <record row='8'>
    <id>8</id>
    <last_name>Smith</last_name>
    <first_name>Robert</first_name>
    <age>33</age>
    <gender>m</gender>
  </record>
</data>

[TobyRT]

your line

  mysql_data_seek($this->result, 0); 

Could one simply replace that with a normal SQL Query? Still haven't got my head round those value pairs!

[NogDog]

TobyRT wrote:

your line

  mysql_data_seek($this->result, 0); 

Could one simply replace that with a normal SQL Query? Still haven't got my head round those value pairs!

The mysql_data_seek is just making sure that the result row pointer is set to the first row (in case any other method calls have moved it to another row).

If you use this as a stand-alone function, you could replace the $this->result with a query result variable passed to the function:

<?php
   function getXml($queryResult)
   {
      mysql_data_seek($queryResult, 0);
      $xml = "<?xml version='1.0' encoding='ISO-8859-1'?>\n<data>\n";
      $count = 1;
      while($row = mysql_fetch_assoc($queryResult))
      {
         $xml .= "  <record row='$count'>\n";
         foreach($row as $key => $val)
         {
            $xml .= "    <$key>$val</$key>\n";
         }
         $xml .= "  </record>\n";
         $count++;
      }
      $xml .= "</data>";
      return($xml);
   }

$result = mysql_query($yourQuery);
$xml = getXml($result);
echo $xml;