2 Replies Latest reply on Jun 19, 2006 8:43 PM by RyanG24

    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>
        • 1. Re: where?
          jdeline Level 1
          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#' ...
          • 2. Re: where?
            RyanG24 Level 1
            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>