Refreshing ColdFusion Page with Selected Value

Category Drop DownHi,
 I have a CFM page where it lists items from the inventory table.
 Recently I added a drop-down menu (please see the screenshot) where the user can pick the category. Once a category is selected, I am hoping to display only the items in selected category. However my code below displays all items regardless of what I select from the drop-down menu.
  When I display the contents of "SORTFIELD" variable, it shows the correct Category ID value whenever I select different ones. However my page still displays all the items in the table.

Thanks.


<CFparam NAME="SORTFIELD" Default=0>  <!--- Define the variable value to zero for the first time loading --->
<cfoutput>Current Sortfield: #SORTFIELD#</cfoutput> <!--- Display the value of Category ID selected from  the drop down --->

            <CFQUERY NAME="GetCategory" datasource="DSN">
                  SELECT      *                      FROM         Category              ORDER BY Category
            </CFQUERY>

            <CFQUERY NAME="GetDesigner" datasource="DSN">
                  SELECT      *                      FROM         Designer              ORDER BY DesignerName
            </CFQUERY>


            <CFQUERY NAME="GetFeaturedItems" datasource="DSN">
                    SELECT      Inventory.* ,  Category.Category, Designer.DesignerName
                     FROM         Inventory, Category, Designer
                   WHERE   Inventory.Status = 'A'
                       <CFIF #SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #SORTFIELD# <!--- Display only the items in selected Category from drop down --->
                       <CFELSE>
                             AND Inventory.CategoryID = Category.ID  <!--- First time loading. Display all items from Inventory table --->
                       </CFIF>
                             AND Inventory.DesignerID = Designer.ID
                </CFQUERY>

                  <FORM ACTION="Inventory.cfm" METHOD="post">
                  Select Category:
                  <SELECT NAME="SORTFIELD" onChange="this.form.submit()">
                  <OPTION VALUE="" selected>Select One
                  <CFOUTPUT query="GetCategory">
                  <OPTION VALUE="#ID#">#Category#</OPTION>
                  </CFOUTPUT>
                  </SELECT>
LVL 1
sgleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
This may not solve the problem, but if you have a categoryID, then you are not joining the category table; when there is no join defined, you are merging every record of inventory with every record of category.

            <CFQUERY NAME="GetFeaturedItems" datasource="DSN">
                    SELECT      Inventory.* ,  Category.Category, Designer.DesignerName
                     FROM        Inventory,  Category,  Designer
                   WHERE   Inventory.Status = 'A'
                       <CFIF #SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #SORTFIELD# 
                       </CFIF>
                             AND Inventory.CategoryID = Category.ID  <!--- need to join category regardless ---->
                             AND Inventory.DesignerID = Designer.ID
                </CFQUERY>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdemariaCommented:
Another problem that I see is that you are not scoping your variable names.

When you SELECT tag posts the form onChange, then the SORTFIELD will come over using the FORM scope.   Since you do not identify the scope, it defaults to the VARIABLES scope.   That means the variable SORTFIELD on your screen could be either FORM.SORTFIELD or VARIABLES.SORTFIELD because you do not specify.    I would try addding FORM.  before every occurence of SORTFIELD.
0
sgleeAuthor Commented:
@gdemaria
I added Form. in front of every occurrence of SORTFIELD, but did not make any difference.
However, upon further testing, I realized that I was selecting categories that are not in any records in Inventory; therefore it returned nothing every time.
So I opened Inventory table, looked at category IDs in the records and selected those category IDs from the drop-down menu.
The result is that yes it displays items that belongs the category, however it repeats to display the same item so many times.
I think my query is wrong as you indicated in ID: 41006872.
What is wrong with this query?
      <CFIF #Form.SORTFIELD# GT 0>
                AND Inventory.CategoryID = #Form.SORTFIELD#
    <CFELSE>
                AND Inventory.CategoryID = Category.ID
     </CFIF>
                AND Inventory.DesignerID = Designer.ID
            ORDER BY Inventory.ID Desc
      </CFQUERY>

and How do I fix it?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Ryan ChongCommented:
>>The result is that yes it displays items that belongs the category, however it repeats to display the same item so many times.

I guess first of all, you shoudn't put the condition AND Inventory.CategoryID = Category.ID in the <CFELSE> tag....

hence, you should try something like this instead.

<CFQUERY NAME="GetFeaturedItems" datasource="DSN">
                    SELECT      Inventory.* ,  Category.Category, Designer.DesignerName
                     FROM         Inventory, Category, Designer
                    WHERE Inventory.CategoryID = Category.ID and Inventory.DesignerID = Designer.ID
			and    Inventory.Status = 'A' 
                        <CFIF #FORM.SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #FORM.SORTFIELD# <!--- Display only the items in selected Category from drop down --->
                       </CFIF>
                </CFQUERY>

Open in new window

0
gdemariaCommented:
slgee,
Please look again at my first post, I took the join clause out of your CFELSE statement..

                       <CFIF #SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #SORTFIELD#
                       </CFIF>
                            AND Inventory.CategoryID = Category.ID  <!--- need to join category regardless ---->
                            AND Inventory.DesignerID = Designer.ID


   <CFQUERY NAME="GetFeaturedItems" datasource="DSN">
                    SELECT      Inventory.* ,  Category.Category, Designer.DesignerName
                     FROM        Inventory,  Category,  Designer
                   WHERE   Inventory.Status = 'A'
                       <CFIF #SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #SORTFIELD# 
                       </CFIF>
                             AND Inventory.CategoryID = Category.ID  <!--- need to join category regardless ---->
                             AND Inventory.DesignerID = Designer.ID
                </CFQUERY>

Open in new window

0
gdemariaCommented:
The reason it is a problem is that without the join to catagories, every record of your inventory table is joining with every record of your category table.   If you have 200 inventory items and 10 category items, you will return 2,000 records.   Joining the two will give you one category for each inventory item and the result will be 200 records which is what you want, one record for each inventory item.
0
sgleeAuthor Commented:
@Ryan
"you shoudn't put the condition AND Inventory.CategoryID = Category.ID in the <CFELSE> tag." ---> That worked.

Thank you.
0
sgleeAuthor Commented:
@gdemaria
The following code in ID: 41013829 worked as well!

Thank you!
                     <CFIF #SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #SORTFIELD#
                       </CFIF>
                             AND Inventory.CategoryID = Category.ID  <!--- need to join category regardless ---->
                             AND Inventory.DesignerID = Designer.ID
                </CFQUERY>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

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.