Jump to content

[SOLVED] Is a query for this even possible?


pebcak

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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`

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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`

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.