Does this mean I need 3 MySQL tables - Services, Countries and Counties
not necessarily, but you do need some way to relate the data.
this could be done using one table, and three different recordsets depending on how the table is set up
you would need a service type column, a country column and a county/state column:
serviceID - primary key, auto increment
serviceName - varchar, name of service
serviceType - varchar, type of service (Example: Photographer)
serviceCountry - varchar, service country (Example: England)
serviceCounty - varchar, service county (Example: Kent)
dynamic dropdowns works by creating a recordset that creates a parent / child relationship between for your data.
to populate the list full of services, you would create a rsService recordset as:
SELECT DISTINCT serviceType FROM services
now create the services select list on the page, in the properties inspector, click the dynamic button.
set an initial static option using "Select a Service" as the label and leave the value blank.
in the options from recordset section, select the rsService recordset and use the serviceType column for both the label and value.
Now create a rsCountries recordset as:
SELECT serviceType, serviceCountry FROM services GROUP BY serviceCountry
then create a rsCounties recordset as
SELECT serviceCountry, serviceCounty FROM services GROUP BY serviceCounty
next add the countries select list to you page, in the property inspector, click the List values button and add a default "Select a country" option with a blank value.
then add the counties select list to you page, in the property inspector, click the List values button and add a default "Select a county" option with a blank value.
At this point you have the basic starting point for adding the dynamic drop down behaviors which will populate the Country and County lists based on the select made.
first, we need to create the dynamic arrays.
go to Insert -> WebAssist -> Dynamic Dropdowns -> Create Dynamic Array
Select the rsCountries recordset.
Set the Parent ID to serviceType
set the Child ID to serviceCountry
set the Child Label to serviceCountry
this will create an array of available countries for each service type.
add a second dynamic array, go to Insert -> WebAssist -> Dynamic Dropdowns -> Create Dynamic Array
Select the rsCounties recordset.
Set the Parent ID to serviceCountry
set the Child ID to serviceCounty
set the Child Label to serviceCounty
this will create an array of available counties for the selected country.
now we need to use these dynamic arrays on the onChange event of the select lists.
go to Window - > Behaivors (Not to be confused with server behaviors) to open the behaviors panel.
we want to populate the countries when a service is selected:
in design view, select the Services select list, in the behaviors panel click the plus button and select WA Dynamic Dropdowns -> Populate List From Array.
set array source to rsCountries
set the source list to the services list
set the target list to the countries list
set leave top to 1
Now we want to populate the counties list when a country is selected:
in design view, select the Countries select list. in the behaviors panel click the plus button and select WA Dynamic Dropdowns -> Populate List From Array.
set array source to rsCounties
set the source list to the countries list
set the target list to the counties list
set leave top to 1
From this, where and how are the results displayed? I'd like to be able to display them on the same page rather than a new search results page as such.
you would use data assist search to create the search and results pages, dynamic dropdowns would be added to the data assist search page.