I have three fields, country, club and members. I select each list from MySQL tables. I want to select a list of countries, ask the suer to select one and display a list of clubs for that country selected. Then, based on the club selected, I want to display a list of members. Clubs are stored in a clubs table - club_id, club_name and members in a members table member_id and name. I can produce the lists of countries, club and members independently but do not know how to link the where clause in the 2nd drop down, clubs to the country and how to refresh the clubs drop down based on the country selected. Similarly do not know how to restrict the list of members to the club selected.

Hi Ian,

I'm not sure if you are experienced with mysql and javascript, but if so:
In your sql query for drop down box B you can use a where clause with a variable.
The variable can then be set to refer to the chosen value of the other drop down box A.

Regards,
Chantal

Chantal, Thanks for your response. I have a lot of experience with Oracle and MySQL but very little Javascript. I can work out the where clause but I do not know how to reference the value selected in drop down list A from within the where clause in drop down B. I am not sure if I need to run a Javascript based on some on click/onchange action or can reference the value in drop down A using something like $_POST from within the code. For example, is this the correct way to reference the value in drop down A (which is Country_SQL)?

//<code>
$db =& JFactory::getDBO();
$country=$_POST;
$db->setQuery("SELECT club_name from rc_clubs
WHERE country = $country order by club_name");
$result = $db->loadObjectList();
foreach ($result as $r)
$items1 .= $r->club_name . "\n";
return $items1;
//</code>

Hi Ian,

to get the country value you can use(note the [0])
But I just realised that this is probably not going to work as you need to refresh the next drop down form field as soon as a selection was made on the first drop down. The sql will probably only retrieve data on form load/show. Sorry for that

Maybe your script can be combined with something like this?
RSForm!Pro Dynamic drop down example

Regards,
Chantal

Chantal,

Thanks for that. I also do not know how to force a SQL query to refresh, esp from Javascript. I would have thought this was a common requirement for many developers. Not sure where to go from here. I have built a page using 3 drop downs linked based on the dynamic drop down example. I just get stuck when I try to turn them into SQL queries. Any more ideas?

Hi Ian,

I'm getting curious now as well so I'll go and try some things in my spare time this week. In the mean time you could create a ticket: I experienced that the support team is very helpful in these kind of cases as well .

Hi Ian / Chantal, I too up very interested in how to accomplish the task your are discussing. I have a similar need when someone is filling in an address and chooses a Prov/State I would like to be able to lookup the Country and fill that in on the form for the user. This is a essentially a linked fields requirement less complex than Ian's therefore you may have solved it.

Lee

I logged a ticket but no joy...

[TECH-6EZO6DCT01] Linking dynamic lists from MySQL

Hello,

This can be achieved with the help of some AJAX custom scripting. Unfortunately we do not have any examples on this topic.

Regards!

Just wanted to tag on to this. This is exactly what I have been looking for as well. Like the other posts, I have no problem with the SQL query, but I just don't know how get the secondary query refreshed upon select, nor do I understand how to tie it in with javascript. Anyone made any progress on solving this? Any response to your ticket submission?

This is exactly what I am looking for to... Any luck?

Any idea? I spent 2 days looking for some solving, but nothing