Tricky MySQL query and PHP logic issue in building a table

[whizard]

Hey everyone

I've been working on this for a couple of days, no success.

I have a database with the following tables:

Challenges:
id|name|year

Teams:
id|chal_id|team_num|some other non-relevant fields to this problem

Competitions:
id|chal_id|team_id|name|some other non-relevant fields to this problem

Awards:
id|comp_id|team_id|name

What I want do with this data is create an HTML table with a structure like this:


|year|1st team number from this year|first competiton for this team |first award won by this team at this competition |
| | |____________________________|second award won by this team at this competition|
| | |second competition for this team|first award won by this team at this competition |
|


There can be any number of teams, competitions, and awards for each year. Awards are the last column in the table, which means that each <td> needs to be given a rowspan equal to the number of awards it is related to.

So, if in a certain year, there were 2 teams that both had two tournaments and each of them won two awards at each tournament, the year td would need a rowspan of 8, each team td would need a rowspan of 4, and each competition td would need a rowspan of 2.

I've been messing around with loops and queries for a while, but I feel like I'm just groping in the dark.

Perhaps my database structure is no good? I can change it, if anyone has suggestions. This is the first time I've really tried to use a database extensively, so I am certainly still learning and open to any suggestions.

Thanks for reading
Dan

[Fumigator]

Can you clarify your table relationships? You have "teams", "challenges", "competitions", and "awards", but you are storing chal_id in the teams table, so if a particular team is related to more than one challenge then you need more than one row to define a team. Perhaps a team can only be assigned to one challenge at a time, and that's fine.

The same question for the competitions table... you'll need more than one row to define one competition if there is more than one team assigned to that competition.

I'm probably missing crucial details but it seems more logical to not store the challenge_id and team_id foreign keys in the competitions table, but rather use a "cross-reference" table (a that connects teams to competitions). As for challenges, how does that work-- does a team challenge another team without knowing which competition the challenge is for, or is a challenge always initiated under the umbrella of a particular compeition? If that is the case then you should store the competition id in the challenges table rather than the other way around.

[whizard]

Sorry, I can see how that is confusing.

A 'challenge' is synonymous with 'season'. Only one challenge per team.

I do have more than one row for each competition, since the information I am storing is how well each team did at each tournament.

Thanks for replying!
Dan

[Fumigator]

Ah ok that makes sense then. Though, I would still probably make the competitions table a header-type table and drop the results of the competition into another table... The rule of thumb is if you see the same data repeated in several rows then it's a good candidate to break into its own table-- in this case the data that describes a competition will be repeated several times (such as name, location, date, time, and what-not).

If you can provide some sample data then that would help us get the query right... I might have time today to play around with the query.

p.s. the rowspans can be determined before you echo the table tags by either running "count" queries or by counting the data in the array you built from your main query.

[whizard]

Thanks for a ll the tips!

I had tried building an array, but I was getting some error about not being able to use an array offset, which I looked up and it seemed to be a PHP 5 bug... hmm.

(Attached is a sql dump of the DB)

Thanks
Dan