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!!!

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 $_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

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.

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.

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.

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

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

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"

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

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