Linking Dropdown boxes

gustava32 · #1 Oct 28th, 2006, 18:57

Hi All,

I am pulling my hair out trying to link to drop downs together. Both dropdowns are populated from a mySQL database. I know I need to use AJAX to do this but i cannot work it out.

Here is my code!

Code: Select all

<tr>
        <td class="label">Site:</td>
        <td>
            <select name="Site" onchange="getSite(this.form.Site)>
            <?php
                $sql = "SELECT * FROM tblSites";
                if($result = $Connector->query($sql))
                {
                    while ($row = $Connector->fetchArray($result))
                    {
                        $id = $row['SiteID'];
                        $site = $row['SiteName'];
                        echo "<option value=$id>$site</option>";
                    }
                }
            ?>
            </select>
        </td>
    </tr>
    
    <tr>
        <td class="label">Department:</td>
        <td>
            <select name="Department">
            <?php
                    $site = $_GET['site'];
                    $sql = "SELECT * from tblDepartments WHERE SiteID = $site";
                    if($result = $Connector->query($sql))
                    {
                        while ($row = $Connector->fetchArray($result)){
                            $id = $row['DepartmentID'];
                            $department = $row['DepartmentName'];
                            $siteid = $row['SiteID'];
                            
                            $sitelookup = "SELECT a.SiteName, b.Country from tblSites a, tblCountries b WHERE a.SiteID = $siteid AND a.CountryID = b.CountryID";
                            if($result2 = $Connector->query($sitelookup))
                            {
                                while ($row = $Connector->fetchArray($result2))
                                {
                                    $site = $row['SiteName'];
                                    $country = $row['Country'];
                                }
                            }
                            echo "<option value=$id>$department ($site, $country)</option>";
                        }
                    }  
            ?>
         </select>
        </td>
    </tr>

I now need to work out how to update the second list with the value sent from the first box.

Can anyone help me?

ukgeoff · #2 Oct 28th, 2006, 20:09

This is obviously only part of your code as the form elements are not all here.

You don't necessarily need to get into AJAX if you don't want to.

All the php code in your page is going to get parsed each time the page is loaded.

If you have the 'action' attribute of the form element set to '' then each time the form is submitted, the page will reload.

What you have to control is which bits of information are 'sticky', that is held over from the previous parsing of the form and which bits of information are new based on certain values passed when the form was submitted.

gustava32 · #3 Oct 28th, 2006, 20:38

i dont want to submit all the time because it submits to a database so i only want to make a submission once. I want it to work like on the insurance sites where you select manufacturer and then the next drop down will be filled with only cars from that manufacturer!

ukgeoff · #4 Oct 28th, 2006, 22:13

How complex and how many options are involved?

gustava32 · #5 Oct 28th, 2006, 23:09

Well my page is for an Equipment Database for work.

So there is a field for "Site" as you have seen, there are only 4 sites in total. Then each site has several departments, so there is a department field. Both of these fields are filled in from two separate tables in my database!

The query runs on site ID which is the value that will be passed through from the site options when selected. I simply need to know how to get the value that is passed through, i have managed to get as far as displaying it in an alert, but i need to extract the site number and then pass that through to the other drop down so that the query is correct!

ukgeoff · #6 Oct 29th, 2006, 15:39

Is it viable to have the possible variation loaded into the page but hidden and selected as required or is there too much data?

gustava32 · #7 Oct 29th, 2006, 16:57

The problem is that we are expanding so while there isnt much data at the moment, within the next year there maybe!

masonbarge · #8 Oct 29th, 2006, 18:23

You don't need Ajax. The code would be complicated-looking but should be conceptually simple -- you could do it by nesting "if" clauses, maybe in "switch" format for better transparency.

This might be easier to keep track of by using a different pages and putting the "if" handlers in the action section of the form, since you'll have to reload anyway, depending on how fast your pages load. Keep it light on graphics or preload. Subsequent pages should load in less than a second if you keep the heavy html in the includes.

Of course, Ajax is a lot slicker. As far as Ajax goes, this would be comparatively simple. But remember, some visitors will have javascript disabled and you might want a php backup anyway.

Another thought is to use css and unhide the subsequent menus. That would have the advantage of being cool. I wouldn't be able to resist the temptation of restyling the page a little depending on the choice, though.

gustava32 · #9 Oct 30th, 2006, 16:57

So your suggesting that i just use PHP and dont bother with AJAX?
As far as i understood you could not do it in PHP as you have to submit the page to retrieve the second SQL query and when you submit the page you will then be writing to the database. Am i missing something here? is it possible to submit a PHP page without actually doing the final submit?

ukgeoff · #10 Oct 30th, 2006, 22:25

Yes. You control the value of the submit button so that it only has a valid value when the submit button itself is pressed.

You check for this when the page is parsed and only write to the database if its valid.

gustava32 · #11 Oct 30th, 2006, 22:43

OK i think i get it!

So if i set the value of the Submit button to something like "Process" and then i submit the form after selecting the first dropdown it will not post to the database because i will have an if statement that will only become active when process is pressed!

Am i correct?

masonbarge · #12 Oct 31st, 2006, 13:39

What you're talking about is easier than it sounds. You seem to be locked into thinking that you are going to have one form with a lot of submit buttons doing different things. The easiest way to do this, however, is to have three (or however many) forms. All the first two do is pass variable values to the final one.

I am actually working on a complex three-page form. It's fully functional but not styled -- in IE the elements don't even have initial alignment so use Firefox if you can:
http://www.dhreport.com/articles/foo...h_nutrient.php
Check "yes" to the last question if you want to see the second page.

This actually performs like the one you want, only the second version of the second form is just a check button rather than a different version of form #2.
If you check "yes" on page one, the second page stores the variables from the first page and adds other variables.

Try drafting this with a different page for each form, a single form on each page. When you have that working it will be a lot clearer how to structure it.

As far as AJAX, I'd do the php first, because it is (by its nature) 100% functional cross-browser. I'd never say "don't bother with AJAX' -- it's just too cool.

gustava32 · #13 Nov 1st, 2006, 16:10

OK i finally got it working using javascript to submit the form on change and using Sticky Forms

Thanks for all your help

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need Help with this Dropdown Content	Maska	JavaScript Forum	7	Sep 21st, 2007 16:44
menu, dropdown	everland	Flash & Multimedia Forum	2	Aug 21st, 2007 14:58
Dropdown lists	psrujan	JavaScript Forum	1	Jun 30th, 2007 22:37
Dropdown Problem	leewad	JavaScript Forum	1	Jul 27th, 2006 21:51
Dropdown menu	Jorgerb	Flash & Multimedia Forum	6	May 17th, 2005 08:29

		Webforumz.com > Main Forums > Program Your Website > PHP Forum
Linking Dropdown boxes