joehodge
asked on
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
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>
ASKER
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
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
>>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.
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.
ASKER
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
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
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.
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.
ASKER
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
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
ASKER
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?
Player_ID Match_ID Position
1 1 1
3 1 2
4 1 3
16 1 4
do you agree?
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($resultp
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_#.
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($resultp
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_#.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good shout, I think I'll add the new table and go for the one select.
thanks for your help
thanks for your help
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?