Friday, 23 August 2013

Combine Data from Two Tables-Help Needed

Combine Data from Two Tables-Help Needed

I have developed a web site with a mysql backend, but am not satisfied
with how I am getting one set of data and do not know how to get another
dataset.
The page in question is at:
http://whistclub.org/test/ajax.php?vichill/results/1
The results are shown through October so there is some data to use.
I am pulling the results from two mysql tables (see below), but the code I
have used is too ugly for me to tolerate. Yeah, it works, but I have some
standards! Think calls to the database for each team. I think joins should
be used, but I can't get it to work.
Ideally (I think) the result array would convert the teamIDs to team names
and use names for the winner and loser VP columns.
The standings table on the page is not working at all--what you see is
hand coded just to show what is needed.
The tables are:
teams:
id--autoincremented teamID--Asssigned to each team as part of the game
teamName
games:
id--autoincremented gameID--I suspect this is not needed since it
duplicates id. teamA teamB date winner IMPmargin winnersVPs losersVPS
The game is bridge and IMP and VP are scores. VPs are derived from IMPs
and the loser often gets a few VPs. See the webpage for the details, but I
do not think that is relevant to my issue.
Here is some data from the games table:
id gameID teamA teamB date winner IMPmargin winnersVPs losersVPs 1 1 11 18
2013-09-25 18 12 20 10 2 2 12 17 2013-09-25 12 22 22 8 3 3 13 16
2013-09-25 13 20 21 9 4 4 14 15 2013-09-25 14 0 15 15 5 5 19 99 2013-09-25
NULL NULL NULL NULL
Lousy formatting!
Team 99 is a dummy for a bye week for a team.
If the tables are designed too badly, let me know how to make them work
better.
Hopefully, that is enough for someone to point me in the right direction.
If more is needed, let me know.
Thanks
Bill

No comments:

Post a Comment