PHP select from multiple tables and output logical xml format

I tried combining all the things people have helped me with the last few days and I'm failing miserably.
I have four tables.
1. game details
2. player details
3. prizes for each rank
4. game stats

so when i want to get the scores for a game I'm doing a join like this

SELECT * 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 = '65' AND statsID is not NULL order by score desc

the xml format i'm trying to get to is this

<?xml version="1.0"?>
<dataxml>
<row gameID="65">
<gameName>banana</gameName>
<start>2010-03-19 00:00:00</start>
<end>2010-03-24 00:00:00</end>
<position id="1">
<score>58</score>
<name>test 1</name>
<rank>1</rank>
<desc>banana game prize 1</desc>
<position id="2">
<score>38</score>
<name>test 2</name>
<rank>2</rank>
<desc>banana game prize 2</desc>
</position>
</row>
</dataxml>

i know its rather impossible without seeing my db and all that but if someone can give me a pointer it would be help.
if there is more info I can provide let me know.

many thanks.
blu.
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct
   $gameID = $_REQUEST['gameID'];
   $result = mysql_query("SELECT * 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");

               	echo '<?xml version="1.0"?>';
				echo '<dataxml>';
				if (mysql_num_rows($result)){
					$count = 1;
       				$max_number_of_prizes = 11;
					
					if ($row = mysql_fetch_array($result)) {
					
						echo '<row gameID="'.$row['gameID'].'">';
                		echo "<gameName>".$row['gameName']."</gameName>";
						echo "<start>".$row['startDate']."</start>";
						echo "<end>".$row['endDate']."</end>";
					}
					
       				while ($row = mysql_fetch_array($result)) {
						
                		echo '<position id="'.$count.'">';
						echo "<score>".$row['score']."</score>";
						echo "<name>".$row['playerUserName']."</name>";
               			for($i = 1; $i < $max_number_of_prizes + 1; $i++){
                      		 if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL') { 
								if ($count == $i) {
									echo "<rank>".$i."</rank><desc>".$row['rank'.$i]."</desc>";
								}
                       		 } 


//xml output


<?xml version="1.0"?>
<dataxml>
<row gameID="65">
<gameName>banana</gameName>
<start>2010-03-19 00:00:00</start>
<end>2010-03-24 00:00:00</end>
<position id="1">
<score>58</score>
<name>test</name>
<rank>1</rank>
<desc>banana game prize 1</desc>
<rank>2</rank><desc>banana game prize 2</desc>
<rank>3</rank><desc>banana game prize 3</desc>
<rank>4</rank><desc>banana game prize 4</desc>
<rank>5</rank><desc>banana game prize 5</desc>
rank>11</rank><desc>banana game prize 11 slowest loser</desc>
</position>
</row>
</dataxml>
							 $count++;
                		}
						echo '</position>';
						echo "</row>";
						
   					}
				} else {
					echo '<row type="error">';
 					echo '<error>no results</error>';  
					echo '</row>';
				}
				echo '</dataxml>';
							// print_r ($data);
				mysql_free_result($result);

 
?>

Open in new window

LVL 39
blue-genieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
Wat are you getting as a result?

Are you limiting the while loop to an UPPER of max_number_of_prizes ?

Post more detail please
0
NerdsOfTechTechnology ScientistCommented:
I think I understand the logic see if this helps:
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct

$gameID = $_REQUEST['gameID'];
$result = mysql_query("SELECT * 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");
echo '<?xml version="1.0"?>';
echo '<dataxml>';

$max_number_of_prizes = 11;
if (mysql_num_rows($result))
{
 $gameid = 0;
 while ($row = mysql_fetch_array($result)) 
 {
  echo '<row gameID="'.$row['gameID'].'">';
  echo "<gameName>".$row['gameName']."</gameName>";
  echo "<start>".$row['startDate']."</start>";
  echo "<end>".$row['endDate']."</end>";
  for($i = 1; $i < $max_number_of_prizes + 1; $i++)
  {                                                
   if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL')
   { 
    echo '<position id="'.$i.'">';
    echo "<score>".$row['score']."</score>";
    echo "<name>".$row['playerUserName']."</name>";
    echo "<rank>".$i."</rank>";
    echo "<desc>".$row['rank'.$i]."</desc>";
    echo '</position>';
   }
  }
  echo "</row>";
 } 
}
echo '</dataxml>';
// print_r ($data);
mysql_free_result($result);
?>

Open in new window

0
NerdsOfTechTechnology ScientistCommented:
ignore previous try:
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct

$gameID = $_REQUEST['gameID'];
$result = mysql_query("SELECT * 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");
echo '<?xml version="1.0"?>';
echo '<dataxml>';

$max_number_of_prizes = 11;
if (mysql_num_rows($result))
{
 while ($row = mysql_fetch_array($result)) 
 {
  echo '<row gameID="'.$row['gameID'].'">';
  echo "<gameName>".$row['gameName']."</gameName>";
  echo "<start>".$row['startDate']."</start>";
  echo "<end>".$row['endDate']."</end>";
  for($i = 1; $i < $max_number_of_prizes + 1; $i++)
  {                                                
   if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL')
   { 
    echo '<position id="'.$i.'">';
    echo "<score>".$row['score']."</score>";
    echo "<name>".$row['playerUserName']."</name>";
    echo "<rank>".$i."</rank>";
    echo "<desc>".$row['rank'.$i]."</desc>";
    echo '</position>';
   }
  }
  echo "</row>";
 } 
}
echo '</dataxml>';
// print_r ($data);
mysql_free_result($result);
?>

Open in new window

0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

NerdsOfTechTechnology ScientistCommented:
including a break from the for loop
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct

$gameID = $_REQUEST['gameID'];
$result = mysql_query("SELECT * 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");
echo '<?xml version="1.0"?>';
echo '<dataxml>';

$max_number_of_prizes = 11;
if (mysql_num_rows($result))
{
 while ($row = mysql_fetch_array($result)) 
 {
  echo '<row gameID="'.$row['gameID'].'">';
  echo "<gameName>".$row['gameName']."</gameName>";
  echo "<start>".$row['startDate']."</start>";
  echo "<end>".$row['endDate']."</end>";
  for($i = 1; $i < $max_number_of_prizes + 1; $i++)
  {                                                
   if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL')
   { 
    echo '<position id="'.$i.'">';
    echo "<score>".$row['score']."</score>";
    echo "<name>".$row['playerUserName']."</name>";
    echo "<rank>".$i."</rank>";
    echo "<desc>".$row['rank'.$i]."</desc>";
    echo '</position>';
   }
   else
   {
    break 1; // break for loop
   }
  }
  echo "</row>";
 } 
}
echo '</dataxml>';
// print_r ($data);
mysql_free_result($result);
?>

Open in new window

0
blue-genieAuthor Commented:
Hi NerdsOfTech. thanks for your repsonses.

this is the result I get from the last solution.
<?xml version="1.0"?>
<dataxml><row gameID="65">
<gameName>banana</gameName>
<start>2010-03-19 00:00:00</start>
<end>2010-03-24 00:00:00</end>
<position id="1">
score>523</score>
<name>uni</name>
<rank>1</rank>
<desc>banana game prize 1</desc>
</position>
<position id="2">
<score>523</score>
<name>uni</name>
<rank>2</rank>
<desc>banana game prize 2</desc>
</position>
<position id="3">
<score>523</score>
<name>uni</name>
<rank>3</rank>
<desc>banana game prize 3</desc>
</position>
</row></dataxml>

any way to get it to a new node for each position with the correct player; score; and prize?
maybe the sql statement needs to change? I'll fiddle some more of my side.

0
blue-genieAuthor Commented:
ok i think my for loop is way out of place

ive made some changes and that's very almost what i wanted -although the game stuff is duplicated, the position stuff is correct - will need to add some mock data and check.

output as follows
<?xml version="1.0"?>
<dataxml>
<row gameID="65">
<gameName>banana</gameName>
<start>2010-03-19 00:00:00</start>
<end>2010-03-24 00:00:00</end>
<position id="1"><score>523</score>
<name>uni</name><rank>1</rank>
<desc>banana game prize 1</desc>
</position></row><row gameID="65">
<gameName>banana</gameName>
<start>2010-03-19 00:00:00</start>
<end>2010-03-24 00:00:00</end>
<position id="2"><score>58</score>
<name>test</name>
<rank>2</rank>
<desc>banana game prize 2</desc>
</position>
</row>
</dataxml>
0
NerdsOfTechTechnology ScientistCommented:
post your code

I think if you make a count varible you can do an if ($count==1){
//run the game output code once
}

understand?
0
NerdsOfTechTechnology ScientistCommented:
post your code :)
0
blue-genieAuthor Commented:
this is what i ended up doing.
i ignore the duplicated top bits in Flash.
just for info and to learn would be cool to see how it should be done.
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct

$gameID = $_REQUEST['gameID'];
$result = mysql_query("SELECT * 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");
echo '<?xml version="1.0"?>';
echo '<dataxml>';

$max_number_of_prizes = 11;
$i=1;
if(mysql_num_rows($result) == 0){
	echo '<row type="error">No results for selected game</row>';                
} else {
	 while ($row = mysql_fetch_array($result)) 
	 {
		echo '<row gameID="'.$row['gameID'].'">';
		echo "<gameName>".$row['gameName']."</gameName>";
		echo "<start>".$row['startDate']."</start>";
		echo "<end>".$row['endDate']."</end>";
												
		if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL')
		{ 
			echo '<position id="'.$i.'">';
			echo "<score>".$row['score']."</score>";
			echo "<name>".$row['playerUserName']."</name>";
			echo "<rank>".$i."</rank>";
			echo "<desc>".$row['rank'.$i]."</desc>";
			echo '</position>';
		}
		   $i++;
		   echo "</row>";
	 } 
}	

  

echo '</dataxml>';
// print_r ($data);
mysql_free_result($result);
?>

Open in new window

0
blue-genieAuthor Commented:
basically what i couldnt' figure out

i don't want these bits

 echo '<row gameID="'.$row['gameID'].'">';
                echo "<gameName>".$row['gameName']."</gameName>";
                echo "<start>".$row['startDate']."</start>";
                echo "<end>".$row['endDate']."</end>";

in the while loop
but then row wasn't instantiated, i went round and round and started breaking things so i figured i'd leave it to where I'm comfortable (i.e. flash ) to clean up the xml.
0
NerdsOfTechTechnology ScientistCommented:
1. how are the ranks stored?
2. are you outputting one game at a time or more than one?

I made the gameId area output only on the first record AND when the gameid changes:
<?php
session_start();
include 'config.php';
include 'opendb.php';
  
//This function will find and checks if your data is correct

$gameID = $_REQUEST['gameID'];
$result = mysql_query("SELECT * 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");
echo '<?xml version="1.0"?>';
echo '<dataxml>';

$max_number_of_prizes = 11;
$i=1;	// rank counter
$gid=0; // gameid tracker
if(mysql_num_rows($result) == 0){
        echo '<row type="error">No results for selected game</row>';                
} else {
         while ($row = mysql_fetch_array($result)) 
         {
		if($gid!=$row['gameID'])
		{
			$gid=$row['gameID'];
			$i=1;
                	echo '<row gameID="'.$row['gameID'].'">';
                	echo "<gameName>".$row['gameName']."</gameName>";
              		echo "<start>".$row['startDate']."</start>";
	               	echo "<end>".$row['endDate']."</end>";
		}
                                                                                                
               	if(!empty($row['rank'.$i]) && $row['rank'.$i] != 'NULL')
               	{ 
                       	echo '<position id="'.$i.'">';
                       	echo "<score>".$row['score']."</score>";
                       	echo "<name>".$row['playerUserName']."</name>";
                       	echo "<rank>".$i."</rank>";
                       	echo "<desc>".$row['rank'.$i]."</desc>";
                       	echo '</position>';
                }
                   $i++;
                   echo "</row>";
         } 
}       

  

echo '</dataxml>';
// print_r ($data);
mysql_free_result($result);
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blue-genieAuthor Commented:
the ranks aren't stored because it must just show who's the top X at a given time so it just pulls that out and only one game at a time.
thank so much for you help.

quick question if you don't mind. no one else seems to want to or be able to answer me.
are there different syntaxes to use based on the version of mysql or something? reason I ask some of my code works fine on my local xampp server but when i load it to a remote server (older version of xampp) i get the occasional syntax error.

thanks.
blu.
0
blue-genieAuthor Commented:
thanks for your patience and sticking with this.
0
NerdsOfTechTechnology ScientistCommented:
Yes there is differences.

he newer mySQL versions will have functions that have different handling, functions that older versions don't have, and other fixes and changes.

What is nice about mySQL is that they keep a nice reference for each of the major versions.

My recommendation is to analyze the old code, change it to conform with the newest version of mysql, and upgrade.

Sincerely,
=nerdsoftech
0
NerdsOfTechTechnology ScientistCommented:
wampserver is a similar "LAMP" configuration (for windows) however, you can DYNAMICALLY change your mySQL version.

This could help you with development/upgrading etc.

http://www.wampserver.com/en/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.