close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

database search dynamic menu

Thread began 5/14/2010 2:41 pm by basim | Last modified 7/20/2010 10:04 am by Jason Byrnes | 1650 views | 11 replies |

basim

database search dynamic menu

OK im extremely new to databases and dataassist. I'm trying to create a search function that searches a database using 2 drop-down menus and a free text field. l set up my database tried the wizard the first time and wasn't very successful. j0Food_Search.php. The menus wouldn't populate but I was pleased to see upon doing a null search the search returned all results and also typing text in the description field would return matching results! So I figured I was half way there! 3 days later I still can't figure out how to get the menus to work.
1. Display dynamically (choose a class then have the brand filter based on selection, but be able to choose a brand without choosing a category if the user wants to)
2. Most importantly I want it to work! I haven't been able to get a search by brand or class to work. Only the description field returns a result...

I'm obviously missing something very basic here cause this is a very common application.

I've gotten this far by using the dataassist wizard and then following the instructions for dynamic Dropdowns. j0Food_Search.php
The menus work dynamically but the "brand" menu only populates when a "class" is picked. Also the search still doesn't work (other than null or using description). Im guessing there is a disconnect with the table in the menu fields.


Please help!!!

Sign in to reply to this post

troyd

Don't know if I can help, but I just completed one of these.

It looks like you have the dynamic dropdowns working. First thing that I figured out the hard way was that the recordset in your search behavior must contain the values equal to the values used in the dropdowns. Whether they are ID numbers or words. There needs to be a comparison to return the records. So if you are using 1 table for your "Class" and 1 table for your "Brand" and yet another table for your "Food Items", you will need to create a JOIN.

Here I would be using a 4th relational table that relates the brands with the foods. The foods table would have a brands column in it.
Something like;
SELECT *
FROM tblFoods
LEFT JOIN relFoods_Brands ON tblFoods.ID = relFoods_Brands.Parent_ID
LEFT JOIN tblBrands ON tblFoods.BrandID = tblBrands.BrandsID LEFT JOIN tblClass ON relFoods_Brands.Parent_ClassID = tblClass.ClassID


I think. (Fake names obviously. But you get the idea. BTW, I use "tbl" in front of all my table names accept relational tables, I use "rel". Just my way of seeing which is which.)

One thing to note, if you get an error that a column is "ambiguous", you will need to designate the table for the columns where the * is at. Similar to the way they are in JOIN...ON part where there is a "." between the table name and the column name.

In my search, I am using 2 List types and comparing them to the two URL variables passed by the dropdowns post.

Also, I noticed that after a search, you loose everything in your dropdowns. I fixed this by clicking the dynamic button for the first (parent) dropdown and using the same URL variable as the "Select value equal to:" box. In your case it might be

php:
<?php $_GET['S_class'?>

This worked for my needs anyway.

Still learning JOINS and relational tables so hopefully someone will jump in if I am steering you in the wrong direction. I just know that it's Friday and you will most likely want to work on this over the weekend. Been there a lot.

TroyD

Sign in to reply to this post

Jason ByrnesWebAssist

What try aid is correct.

from what I can see your menus are passing the ID values for the brand and Class, but yur results table is showing the text for the brand and class.

For the search to work correctly, the column that the drop down list is comparing to must be the Foreign Key ID column


To populate the brand list initially, you must create a recordset to return all brands and make the the brand list dynamic using that recordset. Dynamic Dropdowns will over write the initial list with the correct brands for the selected class.

Sign in to reply to this post

basim

OK. I will see if I can get this to what I can do

It all makes sense except for this:

SELECT *
FROM tblFoods
LEFT JOIN relFoods_Brands ON tblFoods.ID = relFoods_Brands.Parent_ID
LEFT JOIN tblBrands ON tblFoods.BrandID = tblBrands.BrandsID LEFT JOIN tblClass ON relFoods_Brands.Parent_ClassID = tblClass.ClassID

I have no idea where thats supposed to go or what it does. Like I said im completely new to databases.

Sign in to reply to this post

Jason ByrnesWebAssist

you may or may not need to use the join query, the join query is used to return information from two tables when you have relational data.

Sign in to reply to this post

troyd

That would be the SQL for your main recordset if you have relational tables. And the one that will be searched by the dropdowns and return products or items based on the filter.

What do you have for tables now?

If your main product table's recordset looks something like this;
SELECT *
FROM tblFoods

which is just super basic, but you get the idea.
Then it's returning everything "*" from the table called "tblFoods". But if this table has no reference to what brand or class it's in, then the search has no way of returning just those records. So you create a JOIN that relates all the tables involved into a joined recordset.

In the example SQL, it basically says;

SELECT * (Return all records)

FROM tblFoods (from the tblFoods table)

LEFT JOIN relFoods_Brands ON tblFoods.ID = relFoods_Brands.Parent_ID (join into this recordset the "relational" table called "relFoods_Brands" where the "ID" of the first table "tblFoods" matches the "Parent_ID" column of the "relFoods_Brands" table)

LEFT JOIN tblBrands ON tblFoods.BrandID = tblBrands.BrandsID (also join into this recordset the table called "tblBrands" where the "BrandID" column of the first table "tblFoods" matches the "BrandID" of the "tblBrands" table column)

LEFT JOIN tblClass ON relFoods_Brands.Parent_ClassID = tblClass.ClassID (finally, join into this recordset the table called "tblClass" where the "ClassID" column of the table "tblClass" matches the "Parent_ClassID" column of the relational table "relFoods_Brands)

I might have missed something, but I think this is correct. Don't get discouraged if it looks confusing. I still get very confused when writing these and I click the "Test" button a lot until I narrow it down to what I am shooting for.

TroyD

Sign in to reply to this post

basim

almost there!

OK after a long vacation I came back to this project. After reviewing Jason's comments I discovered that I was indexing the names of the products but passing the ID's. So I fixed that and its kinda working.
002jfood_Search.php?midframe=75
*
If I select flour pizza as the category and then select Bellissimo as the brand I get results for all kinds of stuff from diff brands and categories but If I select one of the other brands I get just a match to those brands. This happens across the board. Any ideas why? Thanks for all the help so far!
*
B

Sign in to reply to this post

Jason ByrnesWebAssist

most likely the separator for the brand and category search is set to OR

WHERE brand ="Bellissimo" OR category = "flour pizza"


make sure that separator is set to AND:
WHERE brand ="Bellissimo" AND category = "flour pizza"

Sign in to reply to this post

basim

They are all set to and. I'm guessing since the url thats returned has & in between each of the criteria. see below.

revtivity.com/002jfood_Results.php?S_CategoryID=9&S_BrandID=7&S_Description=&Search.x=68&Search.y=6&Search=Search

Sign in to reply to this post

Jason ByrnesWebAssist

send a copy of the 002jfood_Results.php page so i can examine the code.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...