Standings table with specific needs

How to set up?

[mooger35]

Table: Standings (contains 10 teams ex. team1 - team10)
Fields: teamid ¦ teamname ¦ wins ¦ losses ¦ ties ¦ gf ¦ ga

What I need is a small table on my front page that shows 5 teams but team1 is the featured team. By this I mean that if team1 is in 3rd through 8th position they will appear in the middle like this example where they are in 5th:

3. Team2
4. Team5
5. Team1
6. Team3
7. Team9

But if they are in 1st, 2nd, 9th or 10th I need the positioning to be different as I still want to show 5 teams like this when they are in 2nd:

1. Team4
2. Team1
3. Team2
4. Team5
5. Team9

What I would like to know is how to set up this query so I can get the correct ranking position and how to echo it to display team1 and the other 4 teams surrounding them.

[jatar_k]

2 options

select team1's rank and then issue a select that gets the ones around it

a better option, since there are only 10 teams is to select them all and figure out where in your array team1 is and show the appropriate teams around them.

I see a 10 part switch statement involved probably. It is pretty messy no matter how you do it.

[mooger35]

hmmm thanks...

Currently I have an "indexed" field where I manually select the 5 teams to include on the front (condensed) standings table. Guess I should just leave as is if it's that messy.

Just thought there may be an easier way to pull the info without having to worry about manual error.

[jatar_k]

unless someone else ahs a better idea and it is working as is I would leave, no use making things complicated if they already work. ;)

[ramoneguru]

Ok, after reading other posts I think I get it, you want to pick the teams surrounded by the "feature team" if the feature team is in 3rd through 8th, namely the 2 teams above the feature team and the 2 teams below?

feature team = team1
Rank, team
1, team7
2, team9
3, team 6
4, team5
5, team10
6, team1
7, team2
8, team4
9, team3
10, team8

Then, would the front page be this (took me a second to get that ahaha):
4, team5
5, team10
6, team1
7, team2
8, team4

--Nick

[mooger35]

Yes. The site is (using previous example) team1's site. So they are the featured team.

There are five possibilities.

1st - they are the top team and 4 are below.
2nd - they are the second team and 3 are below.
3rd through 8th - they are in the middle.
9th - they are second from the bottom and 3 above.
10th - they are bottom and 4 teams above.

[jatar_k]

if rank = 1,2,3
then show 1 to 5

if rank = 8,9,10
then show 5 to 10

if rank = 4
then show 2 to 6

if rank = 5
then show 3 to 7

if rank = 6
then show 4 to 8

if rank = 7
then show 5 to 9

that would be the switch

[ramoneguru]

Yeah, that switch looks better.
--Nick

[mooger35]

That makes sense, but how do I rank the query results to use for the switch? Do I stick the results into an array?

so basically I've started with:

SELECT "teamid, teamname,wins,ties,losses, (wins * 3) + ties AS pts
FROM standings
ORDER BY pts DESC"

/sorry, pretty new to PHP.

[edited by: mooger35 at 7:24 pm (utc) on Feb. 14, 2006]

[jatar_k]

it is all keyed on what rank your featured team is, assign their rank to a variable and have the switch work on that. Use the case to set your range $min and $max, then loop through your results and start displaying at min and exit the loop when max is displayed

[mooger35]

it all makes sense... except for the one thing. How do I get the rank of team1? Is there a rank function for a mysql query?

[jatar_k]

you have it in your array or you could select it specifically if you like

[mooger35]

okay, I'm nearly there. Once I get this final thing I can work with the swich and I should be fine from there. I just really dislike arrays :-)

$query_standings = "SELECT teamid, teamname,wins + losses + ties AS gp, wins,ties,losses,gf,ga, wins * 3 + ties AS pts, SUM(IF(gf='0',0,gf)) / SUM(IF(ga='0',1,ga)) AS gr
FROM standings 
GROUP BY teamname
ORDER BY pts DESC, gr DESC, teamname ASC";
$standings = mysql_query($query_standings, $connectDB) or die(mysql_error());
$arraycount = 1;
while($standings_row = mysql_fetch_row($standings)) { 
$myArray[$arraycount++] = $standings_row[1]; 
} 
//display results
while(list($key,$value) = each($myArray)) { 
echo "$key : $value <BR>"; 
} 
echo "?";

that gives me my list:
1 : Team3
2 : Team4
3 : Team7
4 : Team1
5 : Team2
6 : Team5
7 : Team10
8 : Team9
9 : Team6
10 : Team8

How do I pull $key where $value = "team1"?

Should I have it the other way around where rank is the $value?

[jatar_k]

you could do it in here

while($standings_row = mysql_fetch_row($standings)) {
$myArray[$arraycount++] = $standings_row[1];
if ($standings_row[1] == 'Team1') $therank = $arraycount;
}

[mooger35]

Got it. I did have to change one part of your code jatar. "$therank = $arraycount-1;"

Here's the working code:

$arraycount = 1;
while($standings_row = mysql_fetch_row($standings)) { 
$myArray[$arraycount++] = $standings_row[1]; 
if ($standings_row[1] == 'team1') $therank = $arraycount-1; 
} 
switch (TRUE) {
case ($therank <= 8 AND $therank >= 3): 
 $pos1 = $therank-2;
 $pos2 = $therank-1;
 $pos3 = $therank;
 $pos4 = $therank+1;
 $pos5 = $therank+2;
 break;
case ($therank = 1): 
 $pos1 = $therank;
 $pos2 = $therank+1;
 $pos3 = $therank+2;
 $pos4 = $therank+3;
 $pos5 = $therank+4;
 break;
case ($therank = 2): 
 $pos1 = $therank-1;
 $pos2 = $therank;
 $pos3 = $therank+1;
 $pos4 = $therank+2;
 $pos5 = $therank+3;
 break;
case ($therank = 9): 
 $pos1 = $therank-3;
 $pos2 = $therank-2;
 $pos3 = $therank-1;
 $pos4 = $therank;
 $pos5 = $therank+1;
 break;
case ($therank = 10): 
 $pos1 = $therank-4;
 $pos2 = $therank-3;
 $pos3 = $therank-2;
 $pos4 = $therank-1;
 $pos5 = $therank;
 break;
}
echo $pos1.". ".$myArray[$pos1]."<br>";
echo $pos2.". ".$myArray[$pos2]."<br>";
echo $pos3.". ".$myArray[$pos3]."<br>";
echo $pos4.". ".$myArray[$pos4]."<br>";
echo $pos5.". ".$myArray[$pos5]."<br>";

[jatar_k]

I thought you might have to change that ;)

nice chops on the true switch, I didn't think of that. Just for the heck of it this is how I would write it, can't really test it so might have errors

$arraycount = 1;
while($standings_row = mysql_fetch_row($standings)) {
$myArray[$arraycount++] = $standings_row[1];
if ($standings_row[1] == 'team1') $therank = $arraycount-1;
}
$min = 0;
$max = 0;
switch (TRUE) {
case ($therank <= 7 AND $therank >= 4):
$min = $therank-2;
$max = $therank+2;
break;
case ($therank <= 3):
$min = 1;
$max = 5;
break;
case ($therank >= 8):
$min = 5;
$max = 10;
break;
}
$arraycount = 1;
while (isset($myArray[${'pos' . $arraycount}])) {
if ($arraycount >= $min && $arraycount <= $max) echo ${'pos' . $arraycount}.". ".$myArray[${'pos' . $arraycount}]."<br>";
$arraycount++;
}

[jatar_k]

actually, I just had another thought as I posted that, your true switch made me think of it, this is shorter

$arraycount = 1;
while($standings_row = mysql_fetch_row($standings)) {
$myArray[$arraycount++] = $standings_row[1];
if ($standings_row[1] == 'team1') $therank = $arraycount-1;
}
$min = $therank-2;
$max = $therank+2;
if ($min <= 0) $min = 1;
if ($max > 10) $max = 10;
$arraycount = 1;
while (isset($myArray[${'pos' . $arraycount}])) {
if ($arraycount >= $min && $arraycount <= $max) echo ${'pos' . $arraycount}.". ".$myArray[${'pos' . $arraycount}]."<br>";
$arraycount++;
}

;)

[mooger35]

Thanks for all the help. Now to format it all. :-)

[jatar_k]

good luck with that ;)