My database is comprised of 500 businesses and 280 categories. Each business can choose to belong to as many as 3 categories.
Based on the category selection that a visitor makes, I want to generate a query that will then produce a list of businesses that are in that category.
I have been able to do this on individual PHP pages that are specific to certain categories (i.e. Accommodations or Dining). But this list is so long, I don't really want to create a separate page for each category.
The code that successfully creates the list menu (I know it's DreamweaverMX code - I'll fix it later) is:
Thanks, Steveo, but perhaps I didn't make my issue clear.
My list is coming up great. All 280 or so of the categories appear with no problem.
But because the list is generated with a query, what I don't know how to do is query within the query.
For example, one of my categories is 175 - Restaurants. When you are viewing the page and click on the down arrow that generates the list, when someone chooses "Restaurants", how do I get that selection to generate the query (generally speaking)
while (<your statements here>) {
echo '<option value='.$row['cat_id1'].'>'.$row['cat_name'].'</option>';
}
...a loop echoing the above.
What we do see, is that the above cat_name (ie. Restaurants) is whats being seen, and whats being sendt when posting the form in fact it's cat_id (here; 175).
So after $_POST'ing the form you would recieve the information you need to deal with a 'query within a query'.
You are not thinking about dealing with on-the-fly changes are you? As in that the users selects Restaurants, and something else happens somewhere else?
...as in "JAM, you didn't answer my question right" or "I'll be back with a debriefing"?
because I don't know enough yet to know the answer!
In all honesty, I think I may have a different question, but first I need to analyze my tables and the results that I'm looking for so that I can structure the query appropriately.
I'm missing something that I can't seem to "logic out" to write the correct query. And I may be making this more complicated than it needs to be.
This is for a group of about 500 members. Each member can select 3 of approximately 280 categories in which to be included.
Here is the background:
2 Tables
Members
long list of fields that don't matter, plus:
cat_id1
cat_id2
cat_id3
Category1 (only 2 fields)
cat_id1
cat_desc1
Category2
cat_id2
cat_desc2
Category3
cat_id3
cat_desc3
The 3 category tables are identical (I may not need to do this, but thought I did at first).
What I'm trying to do:
::Search page - query pulls up list of categories
::Based on the category that the visitor selects on the search page, result should show a list of members that have selected the chosen category as one of their 3
I can generate the list, but I can't get the results right. I have tried several things that don't work. Do I even need the 3 category tables?
Then, from what i understand, you want to show all the other members that are a member of the selected category. To do this you of course need the users input, once youve got that you can just do:
SELECT * FROM members WHERE cat_1 = '$id' OR cat_2 = '$id' OR cat_3 = '$id';
Where $is is the cat_id of what the user selected.
You could also clean up the database by in members have only one field for the cats and just seperate the cat_id by a comma or pipe |. And then in the sql query use "WHERE cat LIKE '%|$id|%'" -for that you would of course need the field to be like "|id1|id2|id3|".
then which variable do I use in the results page query for the $id? I thought my query results were defined as $rsCategories, but in desperation I also tried using $row_Categories. The code for my results page is:
Btw, try to stick to the orignal post. I get the feeling that we now are drifting away abit on the original subject and if so, do make a new post with the new question.
Not flaming you, but it makes it easier to follow and understand (especially for the beginners trying to learn).