where?

[RyanG24]

Hi i have this query below which works fine, i have 2 drop down boxes which when selected i need it to change the query to only show records where the column season and TeamID matches the drop down values

i have tried this with no luck
AND
Season = <CFIF isdefined ("FORM.Years")>'#FORM.Years#'<CFELSE>
(Select Max(Season) From matchresults)
</CFIF> AND
TeamID = <cfif isdefined("Form.TeamName") and Form.TeamName NEQ "">#Form.TeamName#</cfif>


so is a drop down box is selected the form will display accordingly but i am getting funny results if nothing is selected




<cfquery name="GetResults" datasource="Cricket">
SELECT *
FROM matchresults MR, TeamNames TN, SeasonTable ST
WHERE MR.TeamID = TN.ID AND MR.Season = ST.SeasonID
</cfquery>

[Guest]

For openers, FORM.years will always be defined, so your CFIF will always be true. What is the value of FORM.years if it is not selected? Null? What you may want is <CFIF Len(FORM.Years) IS NOT 0>'#FORM.years#' ...

[RyanG24]

ok i have sorted it just need to do this now

i have this query below, which works but i need to add a else clause to the SESSION.year5

what i need the esle clause to do is
<CFELSE SESSION.Year5 = MAX GetResults.MR.Season>

not sure how to do this any ideas?

thanks


<cfparam name="SESSION.TeamName5" type="string" default="">
<cfparam name="SESSION.Year5" type="string" default="">

<cfif isdefined ("FORM.TeamName")>
<cfset SESSION.TeamName5 = FORM.TeamName>
<cfelse>
<cfset SESSION.TeamName5 = 1>
</cfif>

<cfif isdefined ("FORM.Years")>
<cfset SESSION.Year5 = FORM.Years>
</cfif>


<cfquery name="GetResults" datasource="Cricket">
SELECT *
FROM matchresults MR

INNER JOIN
TeamNames TN
ON MR.TeamID = TN.ID

INNER JOIN
SeasonTable ST
ON MR.Season = ST.SeasonID

WHERE
0=0
<CFIF SESSION.TeamName5 IS NOT "">
AND
(MR.TeamID = '#SESSION.TeamName5#')</CFIF>
<CFIF SESSION.Year5 IS NOT "">
AND
(MR.Season = '#SESSION.Year5#')</CFIF>
</cfquery>