Populating City,state from Drop Down Menu

[patrioticcow]

hello. i have 3 tables "city" containing id, city, state_id and "states" containing id, states and "table" containing a city field (INT 11)

what i want to do is to select a state then select a city based on the "state_id" and the result to be stored into the "city" field inside the "table" table.

the code will look like this:

$city = isset($_REQUEST['city']) ? addslashes($_REQUEST['city']) : 0;
    $state = isset($_REQUEST['state']) ? addslashes($_REQUEST['state']) : '';
 
<table>
    <tr>
    <td>Search by Departure County
              <select name="state" id="state" onChange="return getCity(this.value);">
                <option value="">Select State</option>
                {section name=res loop=$resultS}
                  <option value="{$resultS[res].id}" {if $state == $resultS[res].id} selected {/if}>{$resultS[res].state}</option>
                {/section}
              </select>
             </td>
                       <td id="city_td">
              {if isset($cityC)}   
              <select name="city" id="city" onchange='return getCityResult(this.value);'>
                <option value="">Select County</option>
                {section name=res loop=$cityC}
                  <option value="{$cityC[res].id}" {if $city == $cityC[res].id} selected {/if}>{$cityC[res].city}</option>
                {/section}
              </select>
              {else}
              <select name="city" id="city">
                <option value="">Select County</option>
              </select>
              {/if}
             </td>
                     </tr>
                    </table>
 

i can populate the state menu like this:

/**State**/
  $sqlS = 'SELECT * FROM states ORDER BY state ASC';
    $qryResult = $db->query($sqlS);
 
    if(DB::isError($qryResult))
    {
        die($qryResult->getMessage());
    }
    $i = 0;
    $resultS = array();
    while($row = $qryResult->fetchRow(DB_FETCHMODE_ASSOC))
    {
        $tmp = array(
                    'id' => $row['id'],
                    'state' => $row['state']
          );
        $resultS[$i++] = $tmp;
    }
    //$smarty->assign('archive', 1);
  $smarty->assign('resultS', $resultS);
  //print_r($resultS);
/**State**/
 

but i don't know how to get the values into the CITY drop down menu and then insert data into the CITY field inside the "table" table.

thanks a lot

[dlite922]

Welcome back patrioticcow!

You can do this 2 ways:

1. Upon the state select box's onchange() event submit the form and redisplay the page now with the city populated and so on so forth for however many dependent dropdowns you have.

2. Use AJAX to do the same without submitting the form and thus refreshing the page. A bit nicer but heavier in the JavaScript coding. Use a JS library like jQuery to make your life easier.



Dan