?
Solved

Radio Button Query

Posted on 2007-10-09
11
Medium Priority
?
309 Views
Last Modified: 2013-12-24
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>
0
Comment
Question by:usky1
  • 6
  • 5
11 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20042828
>>How do I loop to check all the radio buttons?
do you have multiple radio button arrays?
can you post the form plz
0
 

Author Comment

by:usky1
ID: 20043249
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20044267
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
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 

Author Comment

by:usky1
ID: 20048484
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20048520
this line
AND ProdMatrix.InStock BETWEEN #LowQty# AND HighQty#

missing a #
AND ProdMatrix.InStock BETWEEN #LowQty# AND #HighQty#
0
 

Author Comment

by:usky1
ID: 20048884
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
 
LVL 25

Accepted Solution

by:
James Rodgers earned 2000 total points
ID: 20049037
>>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
 

Author Comment

by:usky1
ID: 20049238
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
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20049405
you could make them session vars and keep them in memory
0
 

Author Comment

by:usky1
ID: 20049556
Thanks for your help and suggestions.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20049735
glad i could help

thanks for the points
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question