Creating a recent form guide?

[Mr Chris]

Hi Guys,

 

Say I enter some football results in my results database:

 

team_a 1-1 team_c

team a 2-1 team_b

team_c 1-2 team_b

 

results

home_team

home_score

away_team

away_score

form

 

and to work out the form I run this query:

 

   // Calculations for form guide
if($home_score > $away_score) {
$form='home win';
    } else if($home_score < $away_score) {
$form='away win';
    }else {
$form='draw';
    }

 

Say I wanted to work out the form for each team ie team_a, team_b ,team_c how could I do it in a query?  Or can anyone think of a better way of record and calculate each team's form?

 

Thanks

 

Chris

[bibby]

Hi Mr Chris,  the SQL Forum would have been a better place for this post. But, did you know mysql can do IF statements? IF statements take three arguments:  the conditional, what to do if true, what to do in false.

 

# Let's that that right now your query is,
select home_team, home_score, away_team, away_score from `games` where gameid=1;

# You can derive a "form" within your query with an IF statement, in this case as the 5th column.
select 
      home_team, 
      home_score, 
      away_team, 
      away_score,
      if( home_score=away_score , 'draw',
                    if (home_score>away_score, 'home_win' , 'away_win')
        ) as form
from `games` where gameid=1;