Using a generated row number in another query

MargateSteve · 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!

btherl · 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.

MargateSteve · 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

btherl · 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.
}

MargateSteve · 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

btherl · 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
}

MargateSteve · 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

MargateSteve · 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

btherl · 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.

MargateSteve · 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

MargateSteve · 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

btherl · 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.

MargateSteve · 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

shedcade · 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

MargateSteve · 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

Sign In

Using a generated row number in another query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information