Need help to format xml from complex result returned.

[blue-genie]

Please have a look at the attached image.

those are the results i'm returning with my query.

 

  $result = mysql_query("SELECT gameName, startDate, endDate, FROM (gamedetails left join gamestats on gamedetails.gameID= gamestats .gameID) left join playerdetails on gamestats.playerID = playerdetails.playerID left join gameprizes on gameprizes.gamePrizeID = gamedetails.gameID WHERE gamedetails.gameID = '".$gameID."' AND statsID is not NULL order by score desc");

 

basically what i'm trying to achieve is a leaderboard.

so i send through a game id

i need to get

a) the player at position 1

b) his score

c) the prize for rank 1 (if there is one)

 

i was hoping i could get the xml into some format like this

<data>

<name>Game name</name>

<position>

<player>uni</player>

<score>523></score>

<prize>banana game prize 1></prize>

<position>

<player>test</player>

<score>58></score>

<prize>banana game prize 2></prize>

etc ... if there is no prize for say position 3 and assuming there was a  3rd person - empty prize node etc.

 

Can someone help me please

blu.

 

 

 

[attachment deleted by admin]

[runnerjp]

Ok are you getting the data from an xml sheet OR mysql db??

[ignace]

Excel has an option to format XML:

 

1. remove the empty row #1

2. Create a dummy XML file with all elements/attributes and empty values

3. Go to Options for Excel > Popular > Show tab developers

4. Go to the new tab Developers, press Source a window on the right appears

5. Press XML assignments a new dialog box appears, press Add

6. Select your dummy XML and press Ok

7. In the window on the right your elements/attributes appear, drag & drop them on your heading

8. Return to the Developers tab and press Export

 

This may be a quicker way to format your data as Excel is compatible with Access which in turn can use ODBC for which their is a MySQL driver

[blue-genie]

i all, i'm getting the data from mysql db - just pasted the details into excel so you guys can see what's happening. I'm trying to format the resultset from php in a nice and tidy xml format for the flash to handle. if i can't get the php to do it i'll need flash to loop through the result and clean it up there but that's a 2 step phase.

[blue-genie]

i'm manged to get this to a semblance of what i need. i was using a for loop out of place. thanks all.