Querying a team's record from a table

[Mateobus]

Hello, I have a sports website, and I would like to create a query that computes a team's record based on querying a table of games. The function will be given a team's id and year and will query the table which is set up as follow:

 

homeid | awayid | home_score | away_score | year

-----------------------------------------------------------

 

i can get the wins like such: select count(*) as wins from gamesm where (homeid='958' && home_score>away_score && year='2006') || (awayid='958' && away_score>home_score && year='2006')

 

ideally i would do this as a single query. it is pretty important that this is efficient because it will get used a lot. But how would i do something like this (though i know its not right):

 

select count(*) as wins from gamesm where (homeid='958' && home_score>away_score && year='2006') || (awayid='958' && away_score>home_score && year='2006'), count(*) as losses from gamesm where (homeid='958' && home_score<away_score && year='2006') || (awayid='958' && away_score<home_score && year='2006'), count(*) as ties from gamesm where (homeid='958' || awayid='958') && home_score=away_score

 

I know that doesnt work, but is there a way of doing something like that. If not, I would probably need some sort of stored procedure, correct?

[Barand]

you could try someting like this

 

<?php
include 'db2.php';   // database connection

function team_stats($team, $yr)  {

        $sql = "SELECT home_team, away_team, home_score, away_score
                FROM gamesm
                WHERE $team IN (home_team, away_team)
                AND year='$yr' ";
                
        $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
        $stats = array(
            'won'   => 0,
            'drawn' => 0,
            'lost'  => 0
        );
        while (list($ht, $at, $hs, $as) = mysql_fetch_row($res)) {
            switch (true) {
                case $ht==$team:
                    switch (true) {
                        case $hs > $as:
                            $stats['won']++;
                            break;
                        case $hs < $as:
                            $stats['lost']++;
                            break;
                        case $hs == $as:
                            $stats['drawn']++;
                            break;
                    }
                    break;
                
                case $at==$team:
                    switch (true) {
                        case $hs > $as:
                            $stats['lost']++;
                            break;
                        case $hs < $as:
                            $stats['won']++;
                            break;
                        case $hs == $as:
                            $stats['drawn']++;
                            break;
                    }
                    break;

            }
        }
        return $stats;
}


//check stats
$results = team_stats(958);
echo '<pre>', print_r($results, true), '</pre>';
?>