title: PHP/MySQL - Ranking based on last db column
body: <p>I’ve two tables (MySQL): player and scores. In scores table are stored annual scores in this way:</p>
  
  <pre><code>player_id | 2002 | 2003 | 2004
          1 |  5   |  6   |  4
          2 |  3   |  2   |  5
                 Etc.
  </code></pre>
  
  <p>I write the follow code to make a ranking based on last year scores.</p>
  
  <pre><code>$extract = mysql_query("SELECT * FROM players AS p, scores AS s WHERE p.player_id =s.player_id ORDER BY s.2004 desc"); 
  
  while ($row = mysql_fetch_assoc($extract)) { 
      $name = $row['name'];      
      $score = $row['2004']; 
  
      if ($row['2004'] &lt; $row['2003']) { 
       $value = "-"; 
      } 
      else if ($row['2004'] &gt; $row['2003']) { 
       $value = "+"; 
      }     
      else if ($row['2004'] == $row['2003']) { 
       $value = "="; 
      }     
  
  echo "&lt;b&gt;".$name."&lt;/b&gt; | ".$score." ".$value."&lt;br&gt;\n"; 
  }
  </code></pre>
  
  <p>But this code has two big problems:</p>
  
  <p>1) In the query  I have to specify the last year (ORDER BY s.2004), so if I add another column (eg. 2005) into scores table, I have to manually change the code.</p>
  
  <p>2) Same thing for the “$value” var. If I add 2005, the comparison between 2004 and 2003 becomes wrong (it should be between 2005 and 2004).</p>
  
  <p>I know I have to use loops and array but... how?</p>