MargateSteve Posted January 9, 2011 Share Posted January 9, 2011 I currently have a query that compiles a League Standings Table (the full code is below) and generate the ranking with @rownum := @rownum+1 AS rank This works fine for the main standings page but I want to use the row numbers elsewhere. Each team has it's own page which uses 'team.team_id' as its 'recordID'. On these pages I would like to show that particular teams ranking in the standings. The two options I see would be to run the query filtering by 'team.team_id' but that would only return the one record so ranking would always be '1' or run the whole query and then somehow find which ranking relates to 'team.team_id' (something that may be possible with VIEWS but the database is running on MySQL4) but I cannot figure out how to get around this. For example, if the standings were RankTeam 1Rovers 2United 3City 4Rangers 5Town 6Athletic 7Wanderers 8Hotspur On the 'Rangers' page I would want it to show 'RANKING: 4' and on the 'Athletic' page it would show 'RANKING: 6'. On top of this I would want to show a small version of the rankings with one team above and one team below (it would actually be two teams but I will keep it simple until I understand it!) so one again given the two examples above I would get RANGERS PAGE RankTeam 3City 4Rangers 5Town ATHLETIC PAGE RankTeam 5Town 6Athletic 7Wanderers The query is $i = 1; $ht = "g.home_team = t.team_id"; $at = "g.away_team = t.team_id"; $hw = "g.home_goals > g.away_goals"; $aw = "g.home_goals < g.away_goals"; $d = "g.home_goals = g.away_goals"; $hg ="g.home_goals"; $ag ="g.away_goals"; $table = mysql_query("SELECT t.team_name as Tm, @rownum := @rownum+1 AS rank , (sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS P , (sum(CASE WHEN (".$ht." AND ".$hw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$ht.") AND ".$d." THEN 1 ELSE 0 END)) AS HP , (sum(CASE WHEN (".$at." AND ".$aw.") THEN 3 ELSE 0 END) + sum(CASE WHEN (".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS AP , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." ) THEN 1 ELSE 0 END) as HGP , sum(CASE WHEN ".$at." THEN 1 ELSE 0 END) as AGP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW , sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$d.") THEN 1 ELSE 0 END) AS HD , sum(CASE WHEN (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS AD , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , sum(CASE WHEN (".$ht." AND ".$aw.") THEN 1 ELSE 0 END) AS HL , sum(CASE WHEN (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS AL , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$hg." END) as HGF , SUM(CASE WHEN (".$at.") THEN ".$ag." END) as AGF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , SUM(CASE WHEN (".$ht.") THEN ".$ag." END) as HGA , SUM(CASE WHEN (".$at.") THEN ".$hg." END) as AGA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD , (SUM(CASE WHEN (".$ht.") THEN ".$hg." END) - SUM(CASE WHEN (".$ht.") THEN ".$ag." END)) as HGD , (SUM(CASE WHEN (".$at.") THEN ".$ag." END) - SUM(CASE WHEN (".$at.") THEN ".$hg." END)) as AGD from teams t left join all_games g on t.team_id in (g.home_team,g.away_team) WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc The html is <table width="" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX> <tr> <td></td><td></td> <td colspan="9" align="center" bgcolor="#00FF99">ALL</td> <td colspan="9" align="center" >Home</td> <td colspan="9" align="center">Away</td> </tr> <tr> <td class="hdcell" >POS</td> <td class="hdcell" >Team</td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td width="30" class="hdcell"></td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> <td></td> <td width="30" class="hdcell">P</td> <td width="30" class="hdcell">W</td> <td width="30" class="hdcell">D</td> <td width="30" class="hdcell">L</td> <td width="30" class="hdcell">F</td> <td width="30" class="hdcell">A</td> <td width="30" class="hdcell">GD</td> <td width="30" class="hdcell">Pts</td> </tr> <?php while ($row_table = mysql_fetch_assoc($table)){ echo '<tr> <td style="text-align:left" width="30">'.$i.'</td>'; echo '<td style="text-align:left">'.$row_table['Tm'].'</td> <td style="text-align:left">'.$row_table['GP'].'</td> <td style="text-align:left">'.$row_table['W'].'</td> <td style="text-align:left"> '.$row_table['D'].'</td> <td style="text-align:left"> '.$row_table['L']. '</td> <td style="text-align:left"> '.$row_table['GF']. '</td> <td style="text-align:left"> '.$row_table['GA']. '</td> <td style="text-align:left"> '.$row_table['GD']. '</td> <td style="text-align:left"> '.$row_table['P']. '</td> <td style="text-align:left"></td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['HGP'].'</td> <td style="text-align:left">'.$row_table['HW'].'</td> <td style="text-align:left">'.$row_table['HD'].'</td> <td style="text-align:left"> '.$row_table['HL']. '</td> <td style="text-align:left"> '.$row_table['HGF']. '</td> <td style="text-align:left"> '.$row_table['HGA']. '</td> <td style="text-align:left"> '.$row_table['HGD']. '</td> <td style="text-align:left"> '.$row_table['HP']. '</td> <td style="text-align:left"></td> <td style="text-align:left">'.$row_table['AGP'].'</td> <td style="text-align:left">'.$row_table['AW'].'</td> <td style="text-align:left">'.$row_table['AD'].'</td> <td style="text-align:left"> '.$row_table['AL']. '</td> <td style="text-align:left"> '.$row_table['AGF']. '</td> <td style="text-align:left"> '.$row_table['AGA']. '</td> <td style="text-align:left"> '.$row_table['AGD']. '</td> <td style="text-align:left"> '.$row_table['AP']. '</td> </tr>'; $i++; } ?> </table> As always, thanks in advance for any tips or suggestions, even if they are telling me to scrap what I have so far and start again!! Steve PS. Can someone explain why @rownum := @rownum+1 AS rank causes $i to increment by one? I found the code while searching and do not understand why $i is necessary unless it is a reserved reference. The way the code looks, I should be able to output the ranking using 'rank' but that does not work. I have no problem with how it does things, it would just be nice to understand! Quote Link to comment Share on other sites More sharing options...
btherl Posted January 10, 2011 Share Posted January 10, 2011 This is what causes $i to increment by 1: $i++; If you want rank from the sql, you should use $row_table['rank']. There are actually two independent things there which happen to have the same values - $i and $row_table['rank']. If I was doing the task you want to do, I would read all the rankings into a php array and then deal with it there. Doing it in SQL will probably end up too complicated. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 10, 2011 Author Share Posted January 10, 2011 Sometimes my own stupidity and forgetfulness amazes even me! I now remember, when setting the code up, trying to work out whether $i++; should go after the echo for that field or after the table row! However, if I try to use $row_table['rank'] no numbers appear at all. Back to original point though (I do have a habit of sidetracking my own threads!) when you say 'read them into an array' does that mean write a static array or create an array from table data. If it is the first it would not work as the data changes constantly and if it was the second I would not know where to start!! I have spent the last couple of hours searching for reading data into arrays and also how to set a row number as array and all sorts of things I found online that seemed to do what I am looking for but they were not. It does seem a simple thing to do to show the row number where team_id = xx and then use that as a parameter but I am all out of search terms! Steve Quote Link to comment Share on other sites More sharing options...
btherl Posted January 10, 2011 Share Posted January 10, 2011 It's the second. You could do it like this (assuming your sql result is ordered by rank ascending, which I think it is): $rankings_arr = array(); $i = 1; while ($row_table = mysql_fetch_assoc($table)){ $rankings_arr[$i] = $row_table; $i++; } foreach ($rankings_arr as $row_table) { # $row_table['GP'], etc etc can be used in this loop. } for ($i = 2; $i <= 4; $i++) { $row_table = $rankings_arr[$i]; # $row_table['GP'] etc can be used here, but only rankings 2 to 4 will be processed. } Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 10, 2011 Author Share Posted January 10, 2011 That works almost perfectly and result is exactly how you suggested. All that is left to do is instead of for ($i = 2; $i <= 4; $i++) I need to get it to find t.team_id and assign that row number to $i instead of the fixed values of 2 and 4. So in my example in the first post if I wanted to just show the rank of 'City' $i would be 3 and if it was 'Athletic' $i would be 6, but I need to get these dynamically for each teams individual page. Once I have got that part, as I would want to also show the two teams above and below in the rankings I would have to try to call $i -2, $i -1, $i, $ +1, $i + 2. On the Athletic page this would show ranks 4,5,6,7,8 as 'Athletic's' rank is 6. I really hope I am not confusing things with how I am trying to explain it. A good example of what I am trying to achieve can be found at http://www.footballwebpages.co.uk/margate. This is the page for 'Margate' at a different site. On the right hand side of the content area there is 'The League Table' and this shows something similar to what I am trying to achieve, although it does show three teams above and three below instead of the two that I want. Although there are only 7 rows being output there, the ranking is still retained from the full standings so Margate's ranking is 13, although in the displayed table it is row 4. In my database, Margate's 'team_id' is 42 so in this instant I would need to find the row number that relates to 'team_id' 42 and show that row, the two rows above and the two below. Thanks for all the help so far. I am so much closer to getting the right end result than I ever would have been! Steve Quote Link to comment Share on other sites More sharing options...
btherl Posted January 11, 2011 Share Posted January 11, 2011 Nope that all makes sense. You can break it down into two actions, "Find the current team's rank" and "Find rankings near current team". $current_team = "Mernda Mice"; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tm'] == $current_team) { $current_team_rank = $rank; break; } } When you use foreach like that, $rank is the index and $row_table is the value. Once you have the rank for the current team, you can do a loop something like this: $start_rank = max(1, $current_team_rank - 2); $end_rank = min(count($rankings_arr), $current_team_rank + 2); for ($i = $start_rank; $i <= $end_rank; $i++) { # In here use either $rankings_arr[$i] directly, or set $row_table = $rankings_arr[$i] and then use $row_table } Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 11, 2011 Author Share Posted January 11, 2011 An initial look suggests that is going to be spot on. I will give it a try straight after work. Thanks Steve Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 11, 2011 Author Share Posted January 11, 2011 Massive, massive thanks. That gives just the result I was after. Just one more tiny favour if I can. As the data pulled to the pages is to be call from the teams id number, is it possible to use that as the filter? I have tried changing $current_team = "Margate"; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tm'] == $current_team) { $current_team_rank = $rank; break; to $current_team = 42; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['t.team_id'] == $current_team) { $current_team_rank = $rank; break; but that only returns two rows and not the correct ones. I have also tried a few permutations to that to no avail. Obviously in all reality the $current_team would = $d (or similar) as that would be the RecordID for the page, but for testing I would like to just put a manual team_id in $current_team. If you can solve this a well I promise that will be my very last question on the subject! Thanks again Steve Quote Link to comment Share on other sites More sharing options...
btherl Posted January 12, 2011 Share Posted January 12, 2011 In your SQL you do this to get the value that can be fetched as $row_table['Tm']: t.team_name as Tm So if you want team_id to be available, you can add t.team_id as Tid to your query, and then you can use $row_table['Tid']. Anything that you haven't listed explicitly after the "SELECT" will not be available at all, as the database never sent it back to php. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 12, 2011 Author Share Posted January 12, 2011 I did try it that way but all that was returned were rows 1 and 2. Maybe there was a typo in what I put so will give it another go this evening. Steve Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 12, 2011 Author Share Posted January 12, 2011 I have had a chance to try it on the bus on the way to work and get the same result. I have changed the first part of the query to $table = mysql_query("SELECT *, t.team_name as Tm, t.team_id as Tid, @rownum := @rownum+1 AS rank And changed your code to $current_team = 1; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tid '] == $current_team) { $current_team_rank = $rank; break; } } $start_rank = max(1, $current_team_rank - 2); $end_rank = min(count($rankings_arr), $current_team_rank + 2); for ($i = $start_rank; $i <= $end_rank; ) { $row_table = $rankings_arr[$i] ; (apologies for not using code blocks but my phone won't let me use the formatting buttons) The end result is at http://www.margate-fc.com/content/test/tablemini.php The numbers in brackets are team_id's. It does seem to me that the reason it is showing rows 1 and 2 is because it does not understand what the query is asking so there is no matching record and therefore no 2 records before it. It seems to be showing the first two rows as the two rows after. Steve Quote Link to comment Share on other sites More sharing options...
btherl Posted January 12, 2011 Share Posted January 12, 2011 That's a reasonable hypothesis.. If $current_team_rank = 0, then it would only display ranks 1 and 2. Have you tried displaying the value of $current_team_rank? If it does turn out to be 0, try displaying every value being compared like this: $current_team = 1; print "About to find current team...<br>"; foreach ($rankings_arr as $rank => $row_table) { print "Comparing {$row_table['Tid']} with $current_team<br>"; if ($row_table['Tid '] == $current_team) { $current_team_rank = $rank; print "Match!<br>"; break; } } print "Finished finding current team, got $current_team_rank<br>"; The output of that should be enough to tell you why it's getting a rank of 0. Ohh... I was looking at the post preview (of this post) and noticed you have a stray space: $row_table['Tid']; $row_table['Tid ']; See the difference there? It showed up when I copy and pasted your code and put it in the forum php tags. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 12, 2011 Author Share Posted January 12, 2011 Note to self - never mess around with code via your phone, on the bus, first thing in the morning! It is amazing what problems a space can be and with that out it worked perfectly! I have made a little tweak to account for the teams in the first two positions having no rows before them and the teams in last two positions having no rows after them and this is the final code $current_team = 16; foreach ($rankings_arr as $rank => $row_table) { if ($row_table['Tid'] == $current_team) { $current_team_rank = $rank; break; } } $start_rank = max(1, $current_team_rank - 2); $end_rank = min(count($rankings_arr), $current_team_rank + 2); $sr = $start_rank; $er = $end_rank; if ($rank <= 3) { $sr = 1;$er = 5; } elseif ($rank >= 21) { $sr = 18;$er = 22; } else { $sr = $start_rank; $er = $end_rank; } for ($i = $sr; $i <= $er; ) { $row_table = $rankings_arr[$i] ; (the final '}' comes after '</tr>') Massive thanks for that, the help here always amazes me. If there was a 'Thank' button on here it would be getting a click! Problem fully solved and a very happy person here! Until the next time......... Thanks again Steve Quote Link to comment Share on other sites More sharing options...
shedcade Posted March 11, 2011 Share Posted March 11, 2011 I have a very similar implementation of this code (thank you MargateSteve for your help!) How could i go about making the current teams line bold and/or itallic? Thanks in advance Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted March 11, 2011 Author Share Posted March 11, 2011 The teams ID is passed to the page as 'Tid' and then the extra formatting is done on the row where the team ID matches 'Tid'. if ( $id == $row_smtableall['Tid']) echo '<tr class="tm">'; else echo '<tr>'; I am in the process of setting up a site, inviting people to help develop an Open Source solution for people to run their own football stats sites. If you are interested, even in a idea suggesting category, let me know and I will send you a link when it is up and running. Steve Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.