Improve efficiency of php and or mysql when displaying grid of data

Hi,

I have a problem with writing code in php/mysql and would like your help.

I am trying to display a list of players for each game of the season.

The main issue I am facing is how I retreive the players names rather than their Player_IDs. For each game we have numbers 1-16 and any player can be any number.

My players are stored as Player_ID, First_Name, Second_Name in Players
and the Match_Stats table is Match_ID, Pos_1, Pos_2, Pos_3 etc. but I want to show the Second Name of the player.

My code looks at the stats table and then for every Position it does a select on the player table and brings back the Second Name. As you can imagine if we have 40 games and 16 positions each game it means I'm querying the tables far too many times!

I thought about a case statement but my php/mysql skills are not very good so I would appreciate a few ideas with code samples on how I should structure this page

thanks in advance


$sql = "SELECT m.Match_ID, Pos_1,Pos_2,Pos_3,Pos_4,Pos_5,Pos_6,Pos_7,Pos_8,Pos_9,Pos_10,Pos_11,Pos_12,Pos_14,Pos_15,Pos_16,Pos_17
FROM Match_Stats m, Fixtures f
WHERE m.Match_ID = f.Match_ID
ORDER BY f.Match_Date"; 
		
		
		$result = mysql_query($sql) or die ("Query failed main");
		$numofrows = mysql_num_rows($result);
 
$p_page = 'fixtures';
 
 
 
?>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- Rendered: ptvws02-new at 08 Aug 2007 22:30:09 BST -->
<html>
<head>
<title>Alresford Town FC</title>
<meta http-equiv="Expires" content="Thu, 08 Aug 2007">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="Cache-Control" content="no-cache">
<meta name="keywords" content="Alresford Town FC">
<meta name="description" content="Welcome to the Official Alresford Town FC website">
 
<link rel="stylesheet" type="text/css" media="screen"
href="./style2.css">
 
 
</head>
<body class="body">
<div class="bodyBorder" id="bodyBorder">
	<div class="layout">
		<table width="100%" cellpadding="0" cellspacing="0" class="layoutTable">
			<tr>
				<td valign="top" class="layout leftWrap">
					<div class="layout">
						<img src="./images/Banner.jpg"  alt='Alresford Town'>
					</div>
					<div class="layout clearWrap">
						<table width="1000px" cellpadding="0" cellspacing="0" class="layoutTable">
							<tr>
								<td valign="top" class="layout menuColumn">
							    <?php
										
										include './menu.php';
									?>  
									<img src="./images/Advertise.jpg" alt="Advertise"  align="bottom"  border="0">
								</td>
								<td valign="top" class="layout contentWrap">
									<div class="layout mainStory">
										<table width="100%" cellpadding="0" cellspacing="0" class="layoutTable">
											<tr>
												
												<td valign="top" class="layout mainStoryText">
													   
      												<table width="750px" cellpadding="0" cellspacing="0" >
														<tr>
															<td valign="top"  width="750px" align="center">
																<table cellpadding="3" cellspacing="1" class="tablehead" align="center">
																	<tr bgColor=#C00000>
																		<td></td>
																		<td colspan=22 class="stathead">2007/08  Fixtures</td>
																	</tr>
																	<tr class="colhead">
																		<td></td>
																		<td nowrap>Date</td>
																		<td align="right">HOME</td>
																		<td>Score</td>
																		<td nowrap>AWAY</td>	
																		<td>1</td>
																		<td>2</td>
																		<td>3</td>
																		<td>4</td>
																		<td>5</td>
																		<td>6</td>
																		<td>7</td>
																		<td>8</td>
																		<td>9</td>
																		<td>10</td>
																		<td>11</td>
																		<td>12</td>
																		<td>14</td>
																		<td>15</td>
																		<td>16</td>
																		<td>17</td>
																		
																	</tr>
 
																	<?php
																	
																	
																	
 
 
																	
																		for($i = 0; $i < $numofrows; $i++) 
																			{
																
																				$row = mysql_fetch_array($result); //get a row from our result set
															
																     																		
 
 
																							
																							$sqlHomeTeamGoals = "SELECT Home_Team
																						FROM alresford_main.Results T
																						where Match_ID = '$row[0]'; ";
																			
																		$resultHomeTeamGoals = mysql_query($sqlHomeTeamGoals) or die ("Query failed home goals");
																		$numofrowsHomeTeamGoals = mysql_num_rows($resultHomeTeamGoals);
																		$rowHomeTeamGoals = mysql_fetch_array($resultHomeTeamGoals); //get a row from our result set
																		
																		$sqlAwayTeamGoals = "SELECT Away_Team
																						FROM alresford_main.Results T
																						where Match_ID = '$row[0]'; ";
																			
																		$resultAwayTeamGoals = mysql_query($sqlAwayTeamGoals) or die ("Query failed away goals");
																		$numofrowsAwayTeamGoals = mysql_num_rows($resultAwayTeamGoals);
																		$rowAwayTeamGoals = mysql_fetch_array($resultAwayTeamGoals); //get a row from our result set
																				
																				
																				$sqlp1 = "SELECT  First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[1]' = Player_ID";
		
																			$resultp1 = mysql_query($sqlp1) or die ("Query failed 1");
																			$rowp1 = mysql_fetch_array($resultp1); //get a row from our result set
																			
																			$sqlp2 = "SELECT  First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[2]' = Player_ID";
		
																			$resultp2 = mysql_query($sqlp2) or die ("Query failed 2");
																			$rowp2 = mysql_fetch_array($resultp2); //get a row from our result set
																			
																			$sqlp3 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[3]' = Player_ID";
		
																			$resultp3 = mysql_query($sqlp3) or die ("Query failed 3");
																			$rowp3 = mysql_fetch_array($resultp3); //get a row from our result set
																			
																			$sqlp4 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[4]' = Player_ID";
		
																			$resultp4 = mysql_query($sqlp4) or die ("Query failed 4");
																			$rowp4 = mysql_fetch_array($resultp4); //get a row from our result set
																				
																				$sqlp5 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[5]' = Player_ID";
		
																			$resultp5 = mysql_query($sqlp5) or die ("Query failed 5");
																			$rowp5 = mysql_fetch_array($resultp5); //get a row from our result set
																				
																				$sqlp6 = "SELECT  First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[6]' = Player_ID";
		
																			$resultp6 = mysql_query($sqlp6) or die ("Query failed 6");
																			$rowp6 = mysql_fetch_array($resultp6); //get a row from our result set
																				
																				$sqlp7 = "SELECT  First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[7]' = Player_ID";
		
																			$resultp7 = mysql_query($sqlp7) or die ("Query failed 7");
																			$rowp7 = mysql_fetch_array($resultp7); //get a row from our result set
																			
																			$sqlp8 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[8]' = Player_ID";
		
																			$resultp8 = mysql_query($sqlp8) or die ("Query failed 8");
																			$rowp8 = mysql_fetch_array($resultp8); //get a row from our result set
																			
																			$sqlp9 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[9]' = Player_ID";
		
																			$resultp9 = mysql_query($sqlp9) or die ("Query failed 9");
																			$rowp9 = mysql_fetch_array($resultp9); //get a row from our result set
																			
																			$sqlp10 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[10]' = Player_ID";
		
																			$resultp10 = mysql_query($sqlp10) or die ("Query failed 10");
																			$rowp10 = mysql_fetch_array($resultp10); //get a row from our result set
																			
																			$sqlp11 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[11]' = Player_ID";
		
																			$resultp11 = mysql_query($sqlp11) or die ("Query failed 11");
																			$rowp11 = mysql_fetch_array($resultp11); //get a row from our result set
																			
																			$sqlp12 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[12]' = Player_ID";
		
																			$resultp12 = mysql_query($sqlp12) or die ("Query failed 12");
																			$rowp12 = mysql_fetch_array($resultp12); //get a row from our result set
																			
																			$sqlp14 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[14]' = Player_ID";
		
																			$resultp14 = mysql_query($sqlp14) or die ("Query failed 14");
																			$rowp14 = mysql_fetch_array($resultp14); //get a row from our result set
																			
																			$sqlp15 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[15]' = Player_ID";
		
																			$resultp15 = mysql_query($sqlp15) or die ("Query failed 15");
																			$rowp15 = mysql_fetch_array($resultp15); //get a row from our result set
																			
																			$sqlp16 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[16]' = Player_ID";
		
																			$resultp16 = mysql_query($sqlp16) or die ("Query failed 16");
																			$rowp16 = mysql_fetch_array($resultp16); //get a row from our result set
																			
																			$sqlp17 = "SELECT First_Name, Second_Name
																					FROM  alresford_main.Players P
																					where '$row[17]' = Player_ID";
		
																			$resultp17 = mysql_query($sqlp17) or die ("Query failed 17");
																			$rowp17 = mysql_fetch_array($resultp17); //get a row from our result set
																				
																				if ($i % 2) 
																				   { 
																							echo'<tr class="evenrow">';
																					   } 
																				else 	
																				   { 
																							echo'<tr class="oddrow">';
																					   }
	
																				if ($ptest1=='1')
																				{
																				echo 'li';
																				}
																				echo'
																						<td></td>
																						<td nowrap>'.$row['newdate'].'</td>
																						<td align="right" nowrap>'.$row2['Team_Name'].'</td>
																						<td nowrap width="50px">'.$rowHomeTeamGoals['Home_Team'].' - 
																						'.$rowAwayTeamGoals['Away_Team'].'</td>
																						<td align="left" nowrap>'.$row3['Team_Name'].'</td>
																						<td nowrap>'.$rowp1['Second_Name'].'</td>
																						<td nowrap width="100px" style="text-align=center">'.$rowp2['Second_Name'].'</td>
																						<td nowrap class="'.$ptest1.'">'.$rowp3['Second_Name'].'</td>
																						<td nowrap>'.$rowp4['Second_Name'].'</td>
																						<td nowrap>'.$rowp5['Second_Name'].'</td>
																						<td nowrap>'.$rowp6['Second_Name'].'</td>
																						<td nowrap>'.$rowp7['Second_Name'].'</td>
																						<td nowrap>'.$rowp8['Second_Name'].'</td>
																						<td nowrap>'.$rowp9['Second_Name'].'</td>
																						<td nowrap>'.$rowp10['Second_Name'].'</td>
																						<td nowrap>'.$rowp11['Second_Name'].'</td>
																						<td nowrap>'.$rowp12['Second_Name'].'</td>
																						<td nowrap>'.$rowp14['Second_Name'].'</td>
																						<td nowrap>'.$rowp15['Second_Name'].'</td>
																						<td nowrap>'.$rowp16['Second_Name'].'</td>
																						<td nowrap>'.$rowp17['Second_Name'].'</td>
																					</tr>';
																			}
										
																	?>
																</table>
															</td>
														</tr>
													</table>
												</td>
											</tr>
										</tr>
									</div>
									
								</td>
							</tr>
							
						</table>
					</div>
				</td>
			</tr>
			</table>
		</div>
	</div>

Open in new window

joehodgeAsked:
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.

hieloCommented:
To correctly retrieve Second_Name, you need a relationship between Players and Match_Stats. For example, based on what you provided:
Players
  Player_ID
  First_Name
  Second_Name

Match_Stats
  Match_ID
  Pos_1
  Pos_2
  Pos_3

we can't tell which player played in which match. Is there a Player_ID field in Match_Stats? If not, how are you determining who played in which match?
0
joehodgeAuthor Commented:
Hi hielo,

I have
Players
Player_ID               1
First_Name            Joe
Second_Name       Hodge

and then
Match_Stats
Match_ID               1
Pos_1                    1

This would then tell me for the first game I was playing in Goal (Shirt number 1)

does that make sense?

thanks

Joe
0
hieloCommented:
>>does that make sense?
Absolutely NOT

If you "concentrate" only on Match_Stats, looking at Match_ID=1 and/or Pos_1=1, I cannot tell which player it is referring to. If there is no relationship between these tables, what you are asking cannot be done.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

joehodgeAuthor Commented:
Hi hielo,

I thought that my database was denormalised but I could quite easily have made a mistake!
If I have a list of 30 players and only 16 of these play in each game. They could play in different positions in each game so this is the best way I thought I could structure the Match_Stats table - (I've got another 5 or 6 tables and these or all linked by Match_ID)

Have I gone wrong with how I've set things up? How would you have organised things?

thanks

Joe
0
hieloCommented:
I woul have had another table called
PlayerMatches
  PlayerID
  MatchID

Which esentially "links/associates" my Players table to my Match_Stats table. In other word, you could simply look at the PlayerMatches table and you would already know which players played on a given match OR which player has played on which matches. Hope this makes sense.
0
joehodgeAuthor Commented:
I thought about that when I was creating the site but I was doing things one at a time and my reasoning behind my design was that when I'm inserting the stats it was a lot easier (and I thought more efficient in terms of space) to have 1 row per match. My html form was on 1 line and I had 16 drop downs, 1 submit button and it was easy to update.

Match_ID   Pos_1    Pos_2   Pos_3   Pos_4 etc
1                 1            3             4           16

rather than
Player_ID       Match_ID
1                      1
3                      1
4                      1
16                    1

I'm not sure how I would create the form to enter details based on this design - maybe list all players and have a check box by each, when pressing submit once this would then loop through 16 times entering seperate rows..... I'm babbling apologies..

Anyway, if you think that this is the right way to go about it, I'll re design my tables with your suggestion.
and have rethink on my insert form.

thanks for your help

Joe
0
joehodgeAuthor Commented:
thinking about it, I would need to include another column called Position
Player_ID     Match_ID     Position
1                   1                  1
3                   1                  2
4                   1                  3
16                 1                  4

do you agree?
0
hieloCommented:
Now I see the relationship. Those "Pos_#" are the PlayerID. I thought it was the player's Position (pitcher, first base, etc.).
At any rate, you are already doing what you are asking. Based on your setup, you need to do it exactly as you are doing it on Lines 125 to 235.

To clarify, this is what you have now:
$sqlp1 = "SELECT  First_Name, Second_Name
            FROM  alresford_main.Players P
            where '$row[1]' = Player_ID";
            
            $resultp1 = mysql_query($sqlp1) or die ("Query failed 1");
            $rowp1 = mysql_fetch_array($resultp1); //get a row from our result set

after that segment is executed, $rowp1[0] has the First_Name and $rowp1[1] has the Second_Name. Your code is doing the same for all the Pos_#.
0
joehodgeAuthor Commented:
sorry I didnt explain it very well!

would it be wiser to create some sort of view (I normally code in oracle/PLSQL) or temporary table so I'm not always going back and forth to the database?

I get a timeout code every now and again when I try and view this page.
0
hieloCommented:
>>temporary table so I'm not always going back and forth to the database
If you do this you risk viewing "stale" data.

>>I get a timeout code every now and again when I try and view this page.
Must be due to the multiple queries due to the way you structured the tables:
Match_ID   Pos_1    Pos_2   Pos_3   Pos_4 etc
1                 1            3             4           16

If you had structured it this way:
Player_ID       Match_ID
1                      1
3                      1
4                      1
16                    1

Then your sql would have been:
select Players.Player_ID, Players.First_Name, Players.Second_Name FROM Players, PlayerMatches WHERE Players.Player_ID=PlayerMatches.Player_ID AND MatchID=1;

That single sql would retrieve all the necessary data in a single query. The way I see things, you need to decide whether to "suffer":
a.when inserting the data
"...my reasoning behind my design was that when I'm inserting the stats it was a lot easier"
in reference to this:
Match_ID   Pos_1    Pos_2   Pos_3   Pos_4 etc
1                 1            3             4           16

b. when retrieving the data
"I'm not sure how I would create the form to enter details based on this design "
In reference to this:
Player_ID       Match_ID
1                      1
3                      1
4                      1
16                    1

The HTML form design should not play a part of your db design. You can leave your form as it currently is. If you decide to adopt this alternate model you would need to do as many INSERT statements necessary to insert the data into the database. Currently you are basically doing the same thing, but in reverse: One INSERT, multiple SELECT with the model on option a. The design option below will require multiple INSERTs, one SELECT.

So, in terms of efficiency, either way boils down to the same thing. But it seems your issue is more of a timeout issue. I think that if you did a single SELECT the timeout problem will go away.
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
joehodgeAuthor Commented:
good shout, I think I'll add the new table and go for the one select.

thanks for your help
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.