Any mysql query to xml

[tfoston]

Hello
I'm a php newbie, and I'm guessing this might be out there, but I wrote a couple of functions to convert any mysql query to xml. Helps xml seems to be the way to pass loads of info from a php server to flash since i haven't come across any major web hosting services to support amfphp.

There are 2 functions, 1 sorts the xml nodes by row, and the other lumps the info together by field. All you have to do is pass it your query result, and it will return the xml ($xml_string)

Since I'm new if anyone has any comments please let me know. Again, I hope this helps someone



<?php
//Converts and Query to an returns an XML formated string, accepts a query object
function QueryToXML_Field($result){

//Define all variables
$field_array = Array(); //instance of a new array object to hold the field names in the recordset
$field_count = 0; //the amount of fields in the query
$record_count = mysql_num_rows($result); //The number of records returned from the query
$xml_string; //The resulting xml string

//Loop through the fields in the query results and add the field name to the array object
while ($property = mysql_fetch_field($result)){
$field_array[$field_count] = $property->name;
$field_count++;
}

//Here I will start to build my xml document
$xml_string = "<category>";

//Ok, now I have all the fields in an array. I want to cycle through the field array
for($i=0;$i<$field_count;$i++){
//I will sort the xml nodes by field name
$xml_string = $xml_string."<".$field_array[$i].">";

//run a loop that will toss the contents of each row in the field in the xml document

for($j=0;$j<$record_count;$j++){
$field_result = mysql_result($result,$j,$field_array[$i]);
$xml_string = $xml_string."<item>".$field_result."</item>";
}//end loop

$xml_string = $xml_string."</".$field_array[$i].">";
}//end loop
$xml_string = $xml_string."</category>";

//return the value of $sml_string as a result of the function
return $xml_string;
}


//function accepts a mysql query result object
function QueryToXML_Row($result){

//Define all variables
$total_records = mysql_num_rows($result); //number of rows returned from the query
$fields_array = Array(); //array that holds the name of the fields
$count = 0; //How many fields in the query
$xml_string = ''; //The resulting xml string/documents

//Populate the fields_array with the name of the fields
while ($property = mysql_fetch_field($result)){
$fields_array[$count] = $property->name;
$count++;
}

//$count will also hold the amount of fields in the query/recordset

//Run the loop for every record in the recordset
for($i=0;$i<$total_records;$i++){

$xml_string = $xml_string."<row>";
//run loop here that goes through all the fields
for($a=0;$a<$count;$a++){
$xml_string = $xml_string."<".$fields_array[$a].">";
$xml_string = $xml_string.mysql_result($result,$i,$fields_array[$a]);
$xml_string = $xml_string."</".$fields_array[$a].">";
}//end loop
$xml_string = $xml_string."</row>";
}//end loop

//return the xml string as a result of the function
return $xml_string;
}//end function

?>

[bwh2]

you should put your php strings in single quotes instead of double b/c it's faster:


// ex...
$xml_string = $xml_string.'</'.$fields_array[$a].'>';

also, you should use a while loop to go through your records instead of a for:


while( $row = mysql_fetch_array($result) ) {
// do work
// echo $row['colname'];
}
you could also use the [d-php]array_keys[/d-php] function to get your colnames.

use "\r\n" for carriage returns and line breaks, "\t" for tab. that makes your xml prettier.

lastly, use the [php] tags for posting php.

[tfoston]

Didn't know that, Thanks I'm taking that to the bank! So is it better to use the mysql_fetch_arrary instead of the mysql_result function?

[tfoston]

And by better, I mean faster?

[bwh2]

yeah, it's faster to use mysql_fetch_array.

[duncanhall]

I don't want to throw any more confusion into the pot, but there's no special requirements needed to be able to use amfphp - just php enabled, which you obviously have, and the ability to upload the gateway and services to the root of site, which you must have.

It's definately worth it, rather than changing your MySQL output into XML just to change it back again in Flash.