[SOLVED] Is a query for this even possible?

[pebcak]

My client is using a single table to track football scores.  It's really odd that he's not using multiple tables but I've been told I am not allow to modify the structure.  He wants me to list the teams along with the number of wins/losses, ordered by wins.  His table:

 

game_id	|home_team	|away_team	|home_results	|away_results
----------------------------------------------------------------------
1	|Central	|Kickapoo	|Win		|Loss
2	|Glendale	|Hillcrest	|Loss		|Win
3	|Joplin		|Aurora		|Loss		|Win

 

Any help would be great!

[Oldiesmann]

While it is possible to pull all the info in a single query, it would be a bit easier to do it in two queries, and you're still going to have to use PHP to sort all the data out...

 

First get all the team names and find out how many games there were...

Then, find the number of wins for each team

Put all the wins in an array, using the team names as the keys

Sort the array in reverse order (so the team with the most wins will be first)

Use a foreach loop to calculate the losses for each team and display the info

 

Basically, something like this:

$query = mysql_query("SELECT home_team FROM table ORDER BY home_team ASC") or die(mysql_error());

$num_games = mysql_num_rows($query);
$team_data = array();

while($teams = mysql_fetch_assoc($query))
{
    $current_team = $teams['home_team'];

    // Find all the wins... This is tricky...
    $query2 = mysql_query("SELECT COUNT(home_result) AS home_wins, COUNT(away_result) AS road_wins FROM table WHERE (home_team = '$current_team' AND home_result = 'Win') OR (away_team = '$current_team' AND away_result = 'Win') or die(mysql_error());

    // Calculate the wins
    $wins = mysql_result($query2, 0, 0) + mysql_result($query2, 0, 1);

    // Now that we have this info...
    $team_data[$current_team] = $wins;
}

arsort($team_data);

foreach($team_data AS $name => $games_won)
{
    // Figure out how many games they lost
    $games_lost = $num_games - $games_won;

    // Put whatever code you want here to output this info...
}

 

There might be a way to do all that in one query, but it's easier to do it in two queries.

[pebcak]

Oldiesmann, thanks for the help!  I get this error...

 

"unexpected $end"

[pebcak]

Any idea on why that error would be happening?

[Illusion]

that might be due to mysql_error() is included in mysql_query()

 

u better to post the code what u have tried, if u added anything to the code given by Oldies.

[pebcak]

I found the problem - closing quote at the end of the mysql_query.

 

However, now it just says:

 

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /path/to/file/file.php on line 24

 

Line 24 is:

 

$wins = mysql_result($query2, 0, 0) + mysql_result($query2, 0, 1);

[roopurt18]

Substitute table in the query with the proper table name and tell me how this does:

SELECT
  a.`home_team` AS `team`,
  SUM(IF(a.`home_results`='Win',1,0)) +
    SUM(IF(b.`away_results`='Win',1,0)) AS `Wins`,
  SUM(IF(a.`home_results`='Loss',1,0)) + 
    SUM(IF(b.`away_results`='Loss',1,0)) AS `Losses`
FROM
  table a
LEFT JOIN table b ON (
  a.home_team=b.away_team
)
GROUP BY
  a.`home_team`
ORDER BY
  `Wins`

[pebcak]

at Oldiesmann & Illusion:

 

I got Oldiesmann code to run.  I didn't see the other "mysql_error".

 

The output is kind of whacky though.  I'm using this:

echo $name . " - " . $wins . " - " . $games_lost . "<br>";

 

and the output is:

 

Mount Vernon - 0 - 70
Sehome - 0 - 70
Ferndale - 0 - 70
Bellingham - 0 - 70
Nooksack Valley - 0 - 72
Mount Baker - 0 - 72
Ridgefield - 0 - 72
Sedro-Woolley - 0 - 72

 

All teams have only played one game thus far and the ones showing "0 - 70" are the ones that have won their game.  For that last column it looks like it's showing total number of games minus games played + 1 or something.  Any thoughts?

 

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

 

at roopurt18:

 

I tried that and got the following error:

 

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table b ON (  a.home_team = b.away_team  )  GROUP BY  a. `home_team`  ORDER BY  ' at line 1"

[roopurt18]

You have to change table b to reflect your table.  The query I gave you uses the same table twice.

[pebcak]

That looks like it just might work!  The output I'm getting seems quirky though:

 

team 	Wins  	Losses
Orcas Island 	0 	0
Marysville-Pilchuck 	0 	3
Shorewood 	0 	0
Squalicum 	0 	0
Steilacoom 	0 	0
Blaine 	0 	0
Lynden Christian 	0 	0
Mariner 	0 	0
Darrington 	0 	0
Wenatchee 	0 	0
Anacortes 	0 	6
Lakewood 	0 	0
Stanwood 	0 	0
Liberty Bell 	0 	1
Everett 	0 	0
Bishop Blanchet 	0 	1
Meridian 	0 	0
La Conner 	0 	4

 

It's odd because all teams have only played one game thus far but are showing they've won/loss more than that.

[roopurt18]

Is it possible for you to use phpMyAdmin to export the table into a .sql file and post that?  That would make it easy for me to recreate it on my end and I could play with it myself.

[pebcak]

Certainly!  Here you go!

 

[attachment deleted by admin]

[roopurt18]

I see why this is being thrown off.  We were assuming that only games that were actually played were present within the table when what you have here is a full schedule.  The games that haven't been played yet are also present.

 

I'll play with it for a few minutes and see what I can do.

[pebcak]

Oh crud.  I should've been more explicit in my description -- sorry!!

[roopurt18]

Due to the table structure I don't think there is any way to do this in a single query.  The reason being that you have two separate columns representing teams that we'd like to combine into a single output column.  Perhaps there is a way to do that, but I don't know what it is.

 

My solution involves inserting into a temporary table and then selecting from there.  I would opt for this solution over one that uses PHP to accomplish this task for a few reasons:

 

1) It's more portable

 

2) You can use the database to order the rows, which if combined with indexes will be faster than doing so in PHP

 

3)  It let's the DB engine do the work, which let's your PHP code look cleaner

 

Create the temporary table

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_team_records (
  `team` VARCHAR(100),
  `win` TINYINT(1),
  `loss` TINYINT(1)
)

 

Insert home team results

INSERT INTO tmp_team_records (`team`, `win`, `loss`)
SELECT
  `home_team`,
  IF(`home_results`='Win',1,0),
  IF(`home_results`='Loss',1,0)
FROM
  `sports_football`
WHERE
  LENGTH(`home_results`)<>0

 

Insert away team results

INSERT INTO tmp_team_records (`team`, `win`, `loss`)

SELECT

  `away_team`,

  IF(`away_results`='Win',1,0),

  IF(`away_results`='Loss',1,0)

FROM

  `sports_football`

WHERE

  LENGTH(`away_results`)<>0

 

Select the data you want

SELECT
  `team`,
  SUM(`win`) AS `wins`,
  SUM(`loss`) AS `losses`
FROM
  tmp_team_records
GROUP BY
  `team`
ORDER BY
  `wins` DESC, `team`

 

drop the temporary table, this will occur automatically if you don't do it

DROP TEMPORARY TABLE IF EXISTS tmp_team_records

 

That's the best I could do!

 

(edit) I also wanted to point out that while Oldiesmann's solution only uses two queries, it executes one of them for each team, so it could run many times.

 

Also, I think I may have hit another way of possibly doing this with sub-queries.  I'll try that out...

[roopurt18]

Got it.  You were on MySQL 4.1.x if I recall, so I'm not sure if you can do this.  But assuming you can run sub-queries and use the UNION statement, this should get you everything you need using one call to mysql_query().

 

SELECT
  `team`,
  SUM(IF(`result`='Win',1,0)) AS `wins`,
  SUM(IF(`result`='Loss',1,0)) AS `losses`
FROM
  (
    SELECT
      `home_team` AS `team`,
      `home_results` AS `result`
    FROM
      `sports_football`
    WHERE
      LENGTH(`home_results`)<>0

  UNION ALL

    SELECT
      `away_team` AS `team`,
      `away_results` AS `result`
    FROM
      `sports_football`
    WHERE
      LENGTH(`away_results`)<>0
  ) AS `t`
GROUP BY
  `team`
ORDER BY
  `wins` DESC, `team`

[Barand]

SELECT x.name , SUM(x.wins) as wins
FROM
    (SELECT home_team as name, SUM(IF(home_results='Win',1,0)) as wins 
     FROM `sports_football` GROUP BY name
    UNION
    SELECT away_team as name, SUM(IF(away_results='Win',1,0)) as wins 
    FROM `sports_football` GROUP BY name) as X
GROUP BY x.name
ORDER BY wins DESC

[pebcak]

roopurt18:  That works!  Thanks!

 

Barand:  Your code results in a "#1109 - Unknown table 'x' in field list".  It's probably me though.

 

 

[roopurt18]

No problem.  I learned something myself in the process.