PHP/MySQL Search, Searching using a LINK table

Hello Everyone,

I am new to PHP/MySQL. That being said I am attempting to convert a site from one language into PHP.

I have created the database/tables and imported the data. The relevent table schema is:


There are multiple levels (currently 3) to all categories. All locations will have a category assign on all 3 levels. Level 2 is a sub to level 1, Level 3 a sub to level 2.

So what we are attempting to do is create < select > menus using the categories. So when the page is first loaded the Category one < select > menu will be populated. Then we use an onChange to refresh the page after a Category 1 is selected and using that Category we want to populate the Category 2 < select > menu with all categories that have a level 2 that correlate with a location that has the selected Category 1.

I was turned on to using the IN operator for MySQL, but have found that certain categories will be associated with a large number of locations (ie. 7,000+) and stuffing that many LocationIDs into the IN() slows the search down tremendously. Just for the sake of completeness we did test this and it works. We do return the results that we want, but the search is extremely SLOW.

Also I do not know how to get the list of locations into the IN() operator. We did a cut and paste deal so we could test the search.

The code we have to populate < select menu > one and to do the search for menu 2.



I know this may sound a bit confussing but please bear with me I am a noob. Just ask and I will attempt to answer it.

To summarize:

1. We show all level 1 categories in a < select > menu.
2. onChange on this menu we submit the selected Category ID.
3. Take submitted CID and get all locationsID from LocationCategoryLink table.
4. Use LocationID to get all Level 2 Categories.

If this is not the ideal way to do this please let me know.

Thanks,
Ron

Your story is a bit unclear. Maybe you can give some example entries in the tables.

Do I understand correctly that there are a number of locations, which each location having three catagories (on each level). So:


The first menu would then show L_A-1, L_B-1 and L_C-1. But what would be shown when L_A-1 is choosen?

And what is the purpuose of the Locations table? It only contains an ID. This way the whole table has no meaning at all...

As far as I would go at this point my first query would be:


Which is basically the same as yours but easier to understand (in my humble opinion). There is no exact need to specify the table name in front of each field name. When you want or need to I would suggest using short aliases.