Webmaster Forum / ColdFusion / Advanced Techniques / July 2006

Hi,

I am trying to build s select list of team names for a sporting club website.
The select list should include teams if the currently logged in user is a
coach, team manager, assistant coach, player or a parent of a player.

When I have only one group of conditions in the where clause it works fine.
But that only accounts for one of the above roles. As soon as I ad an OR and
another set of criteria my CF7 server's swsoc.exe goes into an permanent loop
(well, at least 10 minutes, I reboot after that :-)

For example, this works fine ...

<!--- select teams I coach, assistant coach, manage, am a parent of a player,
or am a player --->
<cfquery name="teams" Datasource="#Application.Datasource#">
    SELECT distinct teams.uid_team, team_name, uid_coach, uid_team_manager,
uid_coordinator, team_asst
        from teams, player_agegroups, players
            where
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
players.uid_invoice_to=#session.userid#)                
        Order by team_name
</cfquery>

But as soon as I add another condition cf7 goes into a loop...

<!--- select teams I coach, assistant coach, manage, am a parent of a player,
or am a player --->
<cfquery name="teams" Datasource="#Application.Datasource#">
    SELECT distinct teams.uid_team, team_name, uid_coach, uid_team_manager,
uid_coordinator, team_asst
        from teams, player_agegroups, players
            where
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
players.uid_invoice_to=#session.userid#) or
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
players.uid_user=#session.userid#) or
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
teams_uid_coach=#session.userid#) or
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
teams.team_asst=#session.userid#) or
                (player_agegroups.uid_team=teams.uid_team and
players.uid_user=player_agegroups.uid_user
                    and teams.uid_season=#session.season# and
teams.uid_team_manager=#session.userid#)                   
        Order by team_name
</cfquery>

Any combination hangs CF7, whether it be two, three or all conditions, and any
combination of conditions..

The teams table stores team name, coach, team manager, assistant coach and
coordinator. Players table stores the player records, and the parent
(uid_invoice_to), is part of the player record. player agegroups has the teams,
with a record for each player in a team. The whole system is based around
seasons, hence the #session.season# variable.

What should happen is I get a very short list of teams. When I select a team
from the select list I then go and grab all the player records from the
player_agegroups table joined to the users table to get their names, date of
birth etc...

Can anyone see what I am doing wrong?

thanks
Tanya