PHP/MySql/dynamic dependent SELECT boxes

Anon

I am currently working on an interesting project and looking for some help with it.

To a 'guru' it's probably easy stuff, maybe 10 minutes worth of work.. but I have been working on this stupid thing for weeks now and just can't manage to get it working.

The concept is fairly simple.

3 select boxes. with the 2nd and 3rd contents dependant on the first, and the 3rd dependendant on the 2nd.

For Example, Country, State, City:
The first box contains a list of countries.
If you choose USA in the first box, the second box will only display States in the USA. Choose TEXAS as the STATE and the third box only displays the CITIES in TEXAS.

Using Javascript OnChange function, this is pretty easy and the code is stable and easy to find on the net.

BUT, what if you want the contents of each select box to be dynamically updated from MySQL tables as well?

So that; using an EDITING interface; one could Add new Countries, States and Cities on the fly and the select boxes would still function.

A table called COUNTRY with an ID and COUNTRY_NAME, a table called STATE with ID1, ID2, and STATE_NAME, and a table called CITY with ID1, ID2 and CITY_NAME.

Using the IDs, you can see how one could match City to State and State to Country.

I have seen this done in HTML and JavaScript, but NEVER using MySQL and PHP. (probably some JavaScript is required as well).

I am getting pretty familiar with PHP and MySQl, but JavaScript is a little out of my league at the moment.

I am looking for someone that can help me finish this project relativly fast and has the time, knowledge, and willingness to help.

If you, or anyone you know, could help me with this project over the next few day, I would be very appreciaitve and am more than willing to reciprocate the favor should the need ever arise.

regards,
Matt Johnson - matt@our-town.com
12/02/2001

factory21

You need javascript in order to make something seemless. The chances are that the Javscript implementations you've seen are actually javascript/Database solutions mainly because it's un-practical to transmit every single city in the USA at once, which would be necessary for a pure javascript solution.

The basic steps are re-posting the form every time a select box is changed (javascript), the form/php script detecting the change, and then re-outputting the form with the options set from the database.

it'd probably make things easier if the javascript cleared out dependant values in the first step (e.g, it would clear out city and state values if country changed).

So your requests would look something like:
<user changes country>
GET:userprofile.php?country=USA&state=&city=
<user changes state>
GET:userprofile.php?country=USA&state=CA&city=
<user changes city>
GET:userprofile.php?country=USA&state=CA&city=SF
<user clicks submit>
GET:userprofile.php?country=USA&state=CA&city=SF&submit=1

I've actually seen this done with javascript to the point where the form submit is transparent to the user..

mystic

Sounds much like the working JavaScript code I already have...
The code I have works great, but the data is manually typed into the HTML code, so if you ever need to change it, you have to edit the code..(LOTS more work than it sounds like..mainly because of the number-matching(ID fields) scheme).

You are correct in that it seems impractical to list every city for every state and every country... but it was just a basic example to describe the functionality I am looking for.

Perhaps a more fitting example would be like this:
A large corperation owns several business and wants to be able to look up a group of phone numbers for a single location.

The companies are all over the US. So if you list CITY in the first box, then the facility in the next box, and the FAX#, VOICE#, DATA#, TECH SUPPORT#, EMERGENCY CONTACT#, etc in the last box.

Let's say there are 15 cities. Each of those 15 cities has 30 different locations and each location has 50 to 130 phone numbers.

If it is all in one list, that would be... a long list. 🙂
(I guess a seach engine of some sort would be better here..).

Here is another scenerio.
Here on the PHPBuilder website, there is a list of 'members' listed by location.

When you look at the list, it's pretty long..
now imagine, 2 select boxes. THe first one has locations, the second a list of people in those location..
In some cases, (lik Papua), you would only choose the location, and the second listbox would display only one name since there is apparently only one member from there currently. BUT, if you look at the US, there are over 1000. SO, if you use the 3-tier select box, you could select Country, State, and Person. True you would have alot of states, but 50 something is smaller than 1000 something.. and you could even reduce that by REGION or something..

Anyway, in the above examples, I am just trying to find a way to EASILY locate data based on City and Location, so if there is
a better, or easier way to navigate through thousands of records where they match other records, with having to type anything in (like a search), then that is going to be the end goal I am looking for.

Any suggestion are greatly appreciated.

Thank you very much for your reply Eric.

regards,
Matt Johnson

Anon

I had to do something like that:
-Generate javascript arrays using php

var LIF = new Array;
LIF[0] = "KODJEUE";
dataa["LIF"]=LIF;

var ILP = new Array;
ILP[0] = "Nataïwatch";
ILP[1] = "Kuberka";
dataa["ILP"]=ILP;

Onchange of one select box you must use 3 functions:
- one that erase the select box you want to change

function EraseAll(list) {

list.options.length=0;

}

An other to add items to a list

function Add_to_list(form,texte,valeur) {

var o=new Option(valeur,texte);

form.nom.options[form.nom.options.length]=o;

}

And a last one to add a list of item t a list using you generated arrays:

function gen_select(val,list,form) {

EraseAll(list);

if (val != 0){

for(var e=0;e<dataa[val].length;e++){

Add_to_list(form,dataa[val][e],dataa[val][e]);

}

So in your html code you will ave something like that
<FORM name="theform" method="get" action="">
<SELECT name="destination" onChange="gen_select(theform.elements['destination'].options[theform.elements['destination'].selectedIndex].value,nom,this.form)" class="input">
<OPTION value="LIF">Lifou</OPTION>
<OPTION value="ILP">Ile des pins</OPTION> </SELECT>

and

<SELECT name="nom" class="input">
<OPTION value="KODJEUE">KODJEUE</OPTION> </SELECT>

</form>

ps excuse my poor english as i am french spoken