p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Help!!! With MySql query


Message #1 by WH Micro <wh_micro@y...> on Mon, 22 Apr 2002 21:49:41 -0700 (PDT)
It's me again! I've being desperately working on this
problem for a couple a days but, I have given up. I
need your help.

I have three table:

schedule_table
week_no  |  home  | away
--------------------------
    1    |    1   |   2
    1    |    3   |   4
    1    |    5   |   6
......
    2    |    6   |   1
    2    |    5   |   2
    2    |    4   |   3
......
--------------------------

Every row is a game.

team_names table
team_id  |  name
---------------------
   1     |  one
   2     |  two
   3     |  three
   4     |  four
.....
---------------------

results_table
week_no  |  team_id  |  score
------------------------------
   1     |    1      |  12
   1     |    2      |   3
   1     |    3      |   5
 .....
   2     |    3      |   11
   2     |    4      |   1
   2     |    5      |   4
....
------------------------------

This is the output I'm looking to get:

game 1
one   12
two   10

game 2
three  4
three  9

and so on ....

I've tried everything I know. I don't know how to get
the score according to each game for each week.

Please help! I'm stuck.

I would REALLY apreciate this one.

=====
www.whmicro.com

__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/
Message #2 by "Nikolai Devereaux" <yomama@u...> on Mon, 22 Apr 2002 22:18:20 -0700
I'll send my reply to beginning_php, not php_howto, since this list seems to
be read by more people

Your tables don't make sense to me.  No game has its own ID, so I don't
understand how you're coming up with "Game 1" and "Game 2" for your desired
output.

Plus, and this is just nit picking probably, but for game 2, your expected
output says that Team Three played Team Three in week 2.  The first team 3
scored 4 points, and the second team 3 scored 9.  No where in your
results_table do you have team 3 with either number of points.

Anyway, here's what I would do:

teams
-----
team_id
team_name


schedule
--------
game_id    week_no   home_team_id   away_team_id

results
-------
game_id
home_score
away_score


There's probably some slicker way of writing the SQL with joins and stuff,
but I would write something like this:


SELECT res.game_id    AS game_id,
       ht.name        AS home_name,
       res.home_score AS home_score,
       at.name        AS away_name,
       res.away_score AS away_score
  FROM results res,
       teams    ht,
       teams    at,
       schedule sch
 WHERE res.game_id = sch.game_id
   AND ht.team_id  = sch.home_team_id
   AND at.team_id  = sch.away_team_id


again, this is off the top of my head and is untested.  But the gist of it
is that for every game ID in results, you're getting the scores for the home
and away teams.  You use the game_id from the results table to find the home
team (ht) and away team (at) ids for that game in the schedule table, and
using THOSE id's to get the names from the teams table.


take care,

nik

Message #3 by WH Micro <wh_micro@y...> on Wed, 24 Apr 2002 10:31:59 -0700 (PDT)
I fixed this problem with this query:

SELECT vis.t_name as away_name,
a.score as away_score,
ht.t_name as home_name,
hs.score as home_score
FROM
weekly_results hs, weekly_results a,
teams vis, teams ht,
week_schedule w
WHERE 
hs.week_no = w.week_no
and  ht.t_id = w.home
and  vis.t_id = w.away
and hs.team_id = ht.t_id
and a.team_id = vis.t_id
and hs.week_no = $week
and a.week_no = $week
ORDER BY vis.t_name


Thanks a lot for all your inputs and great ideas!

=====
www.whmicro.com

__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/

  Return to Index