Dynamic Drp down in search form question

Hi guys,

I have a situation I can't seem to work around:

- I have a table of 4900 employers or businesses.
- What I need to be able to do is have a search form with three elements
- Text box for entering name of employer, menu for province/state, menu for city and of course a bitton.

What I need it to do is when a province is selected from the province menu I need only the cities from that province shown.

Now the menus are populated from the table itself. For example I have the province menu items pulled directly from the database grouped by the province field, and the city menu pulled from the same table grouped by city name.

I can't seem to get it in my head on how to do this or even if its possible?

Brad

Yes, this can be done. Since the data is all in the same table, you cant use the numeric ID column to form the relation ship between the State/Province and City, instead you will need to use the State/Provence itself.


The Parent recordset will return only the state/Province column.

use this Recordset to populate the parent list, set both the label and the Value to use the State/Province column.

The child recordset will return the State/Province Column and the City Column.

use this recordset in the Create Dynamic Array behavior.

Set the Parent ID to the State/Province Column, and set the Child ID and Child Label to the City column.

Thanks Jason,

A couple issues doing it that way (Besides still not getting it to work at all with templates)

Can you have two fields grouped individually in a sql recordset? Seems I can group either the province or the city but not both.

If I group the city one I get 4900 items in my province drop menu - about 3500 of one and about 1000 of another and a few others scattered in there.

If I group the province I get hundreds of listings in the drop down for the same city.

So what I tried to do was to create a second table with just the province codes to use in the parent list. Will that work? If so how, as it doesn't really state clearly in the docs what fields should be selected in the extension UI.

Thanks,
Brad

No, you cant group by more than 1 table in a recordset, you need to use two separate recordsets.

the first recordset will return only the province column:
SELECT DISTINCT province FROM tbleName


the second one will return the province and city, you want to have the province repeated for each city since it is being used for the linking, so distinct will work here as well:
SELECT DISTINCT province, city FROM tblName


working with templates in Dreamweaver can be troublesome, there is a workaround I have posted in the following thread:
showthread.php?t=4509


If your still having problems, post a link where I can seethe page and I will be able to help troubleshoot.

"the second one will return the province and city, you want to have the province repeated for each city since it is being used for the linking,"

Why do I want the second one to do the province too? I have a search form (using this within a search form) with a text field for entering the employer name, a drop for province and a drop for city. Would I not just want the city drop to just display cities that are in the selected province or state?

I tried the workaround you posted yesterday. Couldn't get it to work. But then again, I can't get it to work outside a template yet either ;-)

I am sure that if I can ever get it to work outside a template I should be okay (maybe).

Thanks,
Brad

the province needs to be repeated in the city list because it is link being used to create a relation ship.

the principle of dynamic dropdowns is to to filter related data.

For this to work you need to create a relationship between the value being posted from the parent list (the province) and the child recordset.

By listing the province for each city in the recordset, you have a piece of data that can be used to create the relation ship.

think of the province as the glue.

If you need further assistance, please post a copy of the page and a link where i can examine the page to troubleshoot.

"By listing the province for each city in the recordset, you have a piece of data that can be used to create the relation ship."

Ah, okay I misunderstood you. Yes, I know it has to be in that recordset to establish the relationship. I thought you meant it had to be displayed in the second drop as well.

So a correct procedure would be to create a recordset with just provinces and states for the first drop and then a recodset with provinces and cities for the second drop? Because thats the way I am trying to get it to work, but can't for some reason.

Brad

Yes, that is correct.

If you need further assistance, please post a copy of the page and a link where i can examine the page to troubleshoot.