The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.

Results 1 to 6 of 6

Thread: Any mysql query to xml

  1. #1
    151
    posts
    Registered User

    Any mysql query to xml

    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

    ?>

  2. #2
    you should put your php strings in single quotes instead of double b/c it's faster:
    PHP Code:
    // ex...
    $xml_string $xml_string.'</'.$fields_array[$a].'>'
    also, you should use a while loop to go through your records instead of a for:
    PHP Code:
    while( $row mysql_fetch_array($result) ) {
      
    // do work
      // echo $row['colname'];

    you could also use the array_keys 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.

  3. #3
    151
    posts
    Registered User
    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?

  4. #4
    151
    posts
    Registered User
    And by better, I mean faster?

  5. #5

  6. #6
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012