Radio Button Query

I have a form that I would like to query from. There  are other fields on the form and depending on if a radio button is selected, and the radio button value if selected.
This is the current query,
The <cfif isDefined("form.action") and "#form.action#" EQ "outstock"> is checking the form for this radio button. How do I loop to check all the radio buttons?
-------------------------------------------------------------------------------------------------
<cfif isDefined("form.action") and "#form.action#" EQ "outstock">
      <cfquery name="GetProds" datasource="GF123">
      SELECT ProdMatrix.ProductID,Cats.Caption,Cats.CategoryID,
            <CFLOOP index="field" list="#editfields#">
                  Products.#Field#,
            </CFLOOP>ProdMatrix.InStock
      FROM Products, ProdMatrix, dbo.Warehouses, Cats
      WHERE ProdMatrix.InStock = 0
            AND Products.ProductID = ProdMatrix.ProductID
            AND      Cats.CategoryID = Products.CategoryID
            <cfif IsDefined("Category_ID")AND Category_ID GT 0>
                  AND Cats.CategoryID = '#form.Category_ID#'
            </cfif>
      </cfquery>
<cfelse>
      <cfquery name="GetProds" datasource="GF123">
      SELECT ProdMatrix.ProductID,Cats.Caption,Cats.CategoryID,
            <CFLOOP index="field" list="#editfields#">
                  Products.#Field#,
            </CFLOOP>ProdMatrix.InStock
      FROM Products, ProdMatrix, dbo.Warehouses, Cats
      WHERE Products.ProductID = ProdMatrix.ProductID
            AND      Cats.CategoryID = Products.CategoryID
            <cfif IsDefined("Category_ID")AND Category_ID GT 0>
                  AND Cats.CategoryID = '#form.Category_ID#'
            </cfif>
      </cfquery>
</cfif>
usky1Asked:
Who is Participating?
 
James RodgersWeb Applications DeveloperCommented:
>>when I go to the next page or back to previous it show all the records that are in the database
when you move pages do you requery the db? if so then
<cfif isDefined("form.action")>
      <cfif form.action EQ "outstock">
            AND ProdMatrix.InStock = 0
      <cfelseif form.action EQ "reorder">
            AND ProdMatrix.ReorderLevel >= #reordervalue#
      <cfelseif form.action EQ "range">
            AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
      </cfif>
</cfif>
is probably not executed
in you next/back code you will need to include the variables used to call teh necessary code blocks
processingPage.cfm?action=#url.action#&lowQty=#url.lowQty#&highQty=#url.highQty#&startRow=#url.startRow#...
etc.
0
 
James RodgersWeb Applications DeveloperCommented:
>>How do I loop to check all the radio buttons?
do you have multiple radio button arrays?
can you post the form plz
0
 
usky1Author Commented:
I am not using a radio button array, Here is the radio part of the form,
---------------------------------------------------------------------------------------------------------
<INPUT TYPE="radio" VALUE="OutStock" name="ACTION">
                  <span class="textblacknormal">Show items that are</span> <span class="textblackbold"><B>out of stock</B>.</span><BR>
                  <INPUT TYPE="radio" VALUE="Reorder"  name="ACTION">
                  <span class="textblacknormal">Show items that are</span> <span class="textblackbold"><B>less than or equal to</B> </span><span class="textblacknormal">the specified</span><span class="textblackbold"> <B>reorder level</B>. <input name="reordervalue" type="text" size="4" maxlength="5" />
                  <BR>
                  <INPUT TYPE="radio" VALUE="Range" name="ACTION">
                  Show items that are within
            <input name="LowQty" type="Text" class="textblacknormal" value="0" size="4" maxlength="8">
            (Low) and
            <input name="HighQty" type="Text" class="textblacknormal" value="1000" size="4" maxlength="8">
            (High) <B>stock on hand</B>.</span>
--------------------------------------------------------------------------------------------------
I have also been working on the query to try to get it to function with the radio selection, but have not been successful yet,
---------------------------------------------------------------------------------------------------
<cfquery name="GetProds" datasource="GF123">
      SELECT ProdMatrix.ProductID,Cats.Caption,Cats.CategoryID,
            <CFLOOP index="field" list="#editfields#">
                  Products.#Field#,
            </CFLOOP>ProdMatrix.InStock
      FROM Products, ProdMatrix, Warehouses, Cats
      WHERE Products.ProductID = ProdMatrix.ProductID
            AND      Cats.CategoryID = Products.CategoryID
            <cfif IsDefined("Category_ID")AND Category_ID GT 0>
                  AND Cats.CategoryID = '#form.Category_ID#'
            </cfif>
            <cfif isDefined("action") and "#action#" EQ "outstock">
                  AND ProdMatrix.InStock = 0
            <cfelseif isDefined("action") and "#action#" EQ "reorder">
                  AND ProdMatrix.ReorderLevel >= #reordervalue#
            <cfelseif isDefined("action") and "#action#" EQ "range">
                  AND ProdMatrix.InStock BETWEEN #LowQty# AND HighQty#
            </cfif>
      </cfquery>


0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James RodgersWeb Applications DeveloperCommented:
the query will never return satifactory results, you have 4 tables in your from clause a but onl;y three are linked in your where
your from clause should be more like
FROM Products LEFT JOIN  ProdMatrix ON Products.ProductID = ProdMatrix.ProductID LEFT JOIN CATS on Products.CAT_ID= CATS.CAT_ID LEFT JOIN WAREHOUSES ON table.field = Warehouses.ID

and try this as teh where clause
WHERE 1=1
<cfif IsDefined("Category_ID")AND Category_ID GT 0>
AND Cats.CategoryID = '#form.Category_ID#'
</cfif>
<cfif isDefined("form.action")>
      <cfif form.action EQ "outstock">
            AND ProdMatrix.InStock = 0
      <cfelseif form.action EQ "reorder">
            AND ProdMatrix.ReorderLevel >= #reordervalue#
      <cfelseif form.action EQ "range">
            AND ProdMatrix.InStock BETWEEN #LowQty# AND HighQty#
      </cfif>
</cfif>
0
 
usky1Author Commented:
For some reason I am getting the following error with the if statements,
-----------------------------------------------------------------
Context validation error for tag cfif.  
The start tag must have a matching end tag. An explicit end tag can be provided by adding </cfif>. If the body of the tag is empty you can use the shortcut <cfif .../>.
-------------------------------------------------------------------------
I broke out the statements and the error went away but I can't figure out why it is there.

<cfif isDefined("form.action")>
      <cfif form.action EQ "outstock">
            AND ProdMatrix.InStock = 0
      <cfelseif form.action EQ "reorder">
            AND ProdMatrix.ReorderLevel >= #reordervalue#
      <cfelseif form.action EQ "range">
            AND ProdMatrix.InStock BETWEEN #LowQty# AND HighQty#
      </cfif>
</cfif>

0
 
James RodgersWeb Applications DeveloperCommented:
this line
AND ProdMatrix.InStock BETWEEN #LowQty# AND HighQty#

missing a #
AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
0
 
usky1Author Commented:
The obvious! Thanks.
I raised the point value because I have another question related to this. Everything is function properly except with the if statement,
<cfelseif form.action EQ "range">
 AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
The problem is that the records are properly dsiplayed on the first page( MaxRows_GetProds=15) but when I go to the next page or back to previous it show all the records that are in the database. The other two values, outstock & reorder function perfect.
0
 
usky1Author Commented:
That worked. Is there a way to hide everything after the .cfm? Because when I am not using the high or low qty's it still shows and I rather not show anything after.
0
 
James RodgersWeb Applications DeveloperCommented:
you could make them session vars and keep them in memory
0
 
usky1Author Commented:
Thanks for your help and suggestions.
0
 
James RodgersWeb Applications DeveloperCommented:
glad i could help

thanks for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.