Link to home
Start Free TrialLog in
Avatar of cookmyster
cookmysterFlag for Canada

asked on

Populating DropDowns from A Database

Okay here is my question... I have four drop down boxes that will be populated from a database.   But I want them to change based on what one dropdown is showing... ex  

Drop down 1 -  Region  (north america, south america)
Drop down 2 - Country (US, CAN)
Drop Down 3 - SubRegion (MidWest, West, SouthEast)
Drop Down 4 - States (New York, Maine, California)

So when I change the country drop down to US, it should only list the US States....  I was told that I could use javascript and arrays???  but I am not really sure... any ideas out there... here is the code I have so far...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<!--- This gets the function from the lookup table --->
<cfx_ingres name="functions" db="dbmsu99::gis" sql="select pcl_value from pc_lookup where pcl_type = 'function'">
<!--- This populates the regiongroup dropdown box --->
<cfx_ingres name="getregiongroup" db="dbmsu99::gis" sql="select pcl_value from pc_lookup where pcl_type = 'regiongroup'">
<!--- This populates the country dropdown box --->
<cfx_ingres name="getcountry" db="dbmsu99::gis" sql="select pcl_value from pc_lookup where pcl_type = 'country'">
<!--- This populates the subregion dropdown box --->
<cfx_ingres name="getsubregion" db="dbmsu99::gis" sql="select pcl_value from pc_lookup where pcl_type = 'region'">
<!--- This populates the state dropdown box --->
<cfx_ingres name="getstate" db="dbmsu99::gis" sql="select pcl_value from pc_lookup where pcl_type = 'state'">

<html>
<head>
      <title></title>

<!--- This calls the style sheet --->
<cfinclude template="template/style.css">

</head>

<body>
<table border="0" cellpadding="0" cellspacing="0" width="1000">
      <tr>
               <cfoutput>
            <td colspan="#functions.recordcount#" bgcolor="##42637a" height="22" width="1000" nowrap>
                  <b>&nbsp;<font size="3" color="##ffcc99">#region# Regional Processing Center</font></b>
            </td>
            </cfoutput>
      </tr>
      <tr>
            <cfoutput>
            <td bgcolor="##133e47" colspan="#functions.recordcount#">
                  <b>&nbsp;<font color="##ffffff" size="3">#state#</font></b>
            </td>
            </cfoutput>
      </tr>
      <tr>
            <td valign="top">
                  <table border="0" cellpadding="0" cellspacing="3">
                        <tr>
                              <td height="160">
                                    <cfoutput><IMG src="images/states/#state#.gif"></cfoutput>
                              </td>
                        </tr>
                        <tr>
                              <td>
                                    Sort By:
                              </td>
                        </tr>
                        <form name="form">
                        <tr>
                              <td>
                                    <select name="selregiongroup" style="HEIGHT: 15px; WIDTH: 122px">
                                          <cfoutput query="getregiongroup">
                                                <option selected>#pcl_value#</option>
                                          </cfoutput>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td>
                                    <select name="country" style="HEIGHT: 22px; WIDTH: 122px">
                                          <cfoutput><option selected>#country#<option></cfoutput>
                                          <cfoutput query="getcountry">
                                                <option value="#pcl_value#">#pcl_value#</option>
                                          </cfoutput>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td>
                                    <select name="selsubregion" style="HEIGHT: 22px; WIDTH: 122px">
                                          <cfoutput><option selected>#region#</option></cfoutput>
                                          <cfoutput query="getsubregion">
                                                <option>#pcl_value#</option>
                                          </cfoutput>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td>
                                    <select name="state">
                                    <cfoutput><option selected>#state#</option></cfoutput>
                                          <cfoutput query="getstate">
                                                <option value="#pcl_value#">#pcl_value#</option>
                                          </cfoutput>
                                    </select>
                              </td>
                        </tr>
                        </form>
                  </table>
            </td>
            <td>
                  <table border="0" cellpadding="0" cellspacing="0" width="1000">
                        <cfoutput query="functions">
                        <tr>
                              <td bgcolor="##ffcc99" colspan="3">
                                    <b>#functions.pcl_value#</b>
                              </td>
                        </tr>
                                                
                        <CFX_INGRES sql="select pcd_pmfkey, pcd_function from pc_details where pcd_regiongroup   = '#regiongroup#' and pcd_country = '#country#' and pcd_region = '#region#' and pcd_state = '#state#' and pcd_function = '#Functions.pcl_value#' order by pcd_function" db="dbmsu99::gis" name="pcdinfo">
                        <cfloop query="pcdinfo">
                        <!--- To check if there is no info available --->
                        <cfif pcdinfo.pcd_pmfkey is "" or pcdinfo.pcd_pmfkey is 0>
                        <tr BGCOLOR=###IIF(pcdinfo.currentrow MOD 2, DE ('dceaf3'), DE ('bbcbd5'))#>
                                <td colspan="#functions.recordcount#" align="middle">
                                    No Information Is Available At This Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;No Information Is Available At This Time
                              </td>
                        </tr>
                        <cfelse>
      
                        <tr BGCOLOR=###IIF(pcdinfo.currentrow MOD 2, DE ('dceaf3'), DE ('bbcbd5'))#>
                        <!--- This gets the employess name --->
                        <CFX_INGRES sql="select first, last from employee where pmf_key   = '#pcd_pmfkey#'" db="cainfo::cainfo" name="empinfo">
                        <cfloop query="empinfo">
                              <td>
                                    #first# #last#
                              </td>
                        </cfloop>
                        <!--- This gets the employess phone number --->
                        <CFX_INGRES sql="select * from phone_exts where pmf_key   = '#pcd_pmfkey#'" db="cainfo::cainfo" name="phoneinfo">
                        <cfloop query="phoneinfo">
                              <td>
                                    #phoneinfo.phone#
                              </td>
                        </cfloop>
                        <!--- This gets the employees email --->
                              <td>
                                    <A href="mailto:#pcdinfo.pcd_pmfkey#@cai.com"><cfloop query="empinfo">#empinfo.first#.#empinfo.last#@ca.com</CFLOOP></a>
                              </td>
                        </tr>
                        </cfif>
                        </cfloop>
                        <!--- Leaves a blank space between functions --->
                        <tr>
                              <td>
                                    &nbsp;
                              </td>
                        </tr>
                        </cfoutput>
                  </table>
            </td>
      </tr>
</table>
      
</body>
</html>
Avatar of hansam
hansam

Well If you have only three dropdowns you can goto:
http://devex.allaire.com/developer/gallery/SearchResults.cfm?keywords=ThreeSelectsRelated+&search=search
And that is a custom tag that would just that, but not with 4
Refer to :
http://developer.irt.org/script/form.htm#4

for the JS code to create dynamic drop down menus...

CJ
ASKER CERTIFIED SOLUTION
Avatar of ccress
ccress

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i can give u this code in asp
if u want
jimmy
Avatar of cookmyster

ASKER

I did it using <cfif isdefined> and lots of them
i have got a custom tag threeselectsrelated.cfm which will solve ur problem
mail me at jimmy_282@yahoo.com
i will send u the tag.

jimmy
cookmyster: Any progress?
cookmyster: Any progress?
Dont you guys hate it when they offer up 100 points and then just dont come back after getting theyre answer



Doug
I hate that more than anyone also.... I apologize for this... that project I was working for this question was put on hold, I am sorry about that... but I should be on by the end of the week and the points will be awarded to the right person.. again.. I am sorry for this...
Rich
Thx for help ccress... sorry for the delay...