List box to filter data?

[flannerz]

Hi

 

I'm not too sure how I can use a listbox/combo box (whatever you want to call it!) to filter out results. Here is the scenario:

 

• It is going to be used to display fixtures/results for around 4 of 5 competitions.
  
• There are various rounds that make up each competition
  
• Obviously there are going to be teams that play in the fixtures
  

 

This is what I want:

 

• All data to be displayed in a table. So basically all fixtures and results for every competition/round/team
  
• Have three list boxes above the table where users can filter the results either by competition, round or team.
  

 

I have no problem with getting the data into the database and pulling it all back into one table. However could someone instruct me with the code I will require to A) Populate the list boxes with the competitions/rounds/teams and B) Filters the results correctly when a user tries to use the filter.

 

I'm sure it's not too difficult to do but I would really appreciate the code I would need or a link to place that will instruct me how to do it.

 

Many thanks

[flannerz]

Sorry to bump this topic but does anyone have a solution to this?

 

Thanks

[Barand]

what's your database structure ?

[flannerz]

Currently I just have the one table that is storing all of the data. I thought that I might be able to filter it out using WHERE clauses?

[aschk]

Yes possibly, but until we actually know the layout, the SQL you are using, whether you've normalised your data, and any other superfluous information I highly doubt we can "guess" what it is actually doing, and thus what you require from it...

[flannerz]

The table consists of the following fields:

 

id

date

time

competition

round

hometeam

score

awayteam

attendance

report

 

What I want is for all of the fixtures/results from all competitions to be displayed initially. However I want a drop down which will be populated with the competitions and also one for the teams. I then want to be able to filter by either competition, round or team.

 

Is this possible with this structure or not?

 

Thanks

[aschk]

Right,

 

A simple WHERE clause should allow you to filter based on "team" or "round" or "competition" on that table. However it seems apparent that you should segregate your "teams" and perhaps your "rounds/competitions" also.

 

Example tables + data might look like this:

teams
-----------
id | name
=========
1 | glasgow
2 | edinburgh

competitions
------------
id | name
=========
1 | league cup
2 | fa cup
3 | league

results
------------
id | datetime            | competition_id | round | hometeam_id | awayteam_id | score | attendance | report
=========================================================================================================
1  | 2008-01-01 12:23:42 | 1              | 2     | 1           | 2           | 4-3   | 12312      | blah blah blah
2  | 2007-12-01 1:05:55  | 3              | NULL  | 2           | 1           | 1-1   | 9872       | more blah blah

 

The only thing that stands out to me from the above is the difference in competition between knockout tournaments (i.e. fa/league cup) and normal league standings. Obviously normal leagues don't have "rounds" which means the rounds field could be NULL. There may be a better way of modelling this, but after 5 minutes this is what came  off the top of my head.

 

So, moving onto the queries for the information:

1) Query for ALL information:

SELECT * 
FROM results r
JOIN teams h ON h.id = r.hometeam_id
JOIN teams a ON a.id = r.awayteam_id
JOIN competitions c ON c.id = r.competition_id

 

2) Query (filter) based on competition (e.g. fa cup).

SELECT * 
FROM results r
JOIN teams h ON h.id = r.hometeam_id
JOIN teams a ON a.id = r.awayteam_id
JOIN competitions c ON c.id = r.competition_id
WHERE c.id = 2

 

3) Query (filter) based on team (e.g. glasgow). Bear in mind they can be the home OR away team

SELECT * 
FROM results r
JOIN teams h ON h.id = r.hometeam_id
JOIN teams a ON a.id = r.awayteam_id
JOIN competitions c ON c.id = r.competition_id
WHERE h.id = 1 OR a.id = 1

 

4) Query based on round (e.g. round 2)

SELECT * 
FROM results r
JOIN teams h ON h.id = r.hometeam_id
JOIN teams a ON a.id = r.awayteam_id
JOIN competitions c ON c.id = r.competition_id
WHERE r.round = 2

 

5) Query for dropdown box (for all the teams)

SELECT id, name
FROM teams t

 

6) Query for dropdown box (for all competitions)

SELECT id, name
FROM competitions c

 

7) Query for dropdown box (for all rounds)

SELECT DISTINCT(round)
FROM results r

[flannerz]

Many thanks for your help, you have answered most of my questions.

 

One final question to bring it all together, what exactly would I need to put behind each of the boxes? (I mean the PHP code to process it).

 

Thanks

[aschk]

I've got to leave something for you to do