How to change select options depending on another input field

[flynismo]

Hey guys,

Here is what I am trying to do:

I would like for a visitor to enter a zip code, and when zip code is entered, a select field would be populated listing all the cities for the corresponding zip.

I already have everything set up and working other than that. Here is the select field that needs the zip code passed to it:

<select name="city" id="city">
    <option><?= $location->cities( **zip_code_needs_to_go_here** ); ?></option>
</select>

When I run the script and hard code a zip, works fine, but my JS / AJAX knowledge is very limited, so I need help passing the zip code from a seperate input field to the method above. Thank you for any help!!

[diafol]

http://www.daniweb.com/web-development/php/code/476623/ajaxed-linked-dropdowns-select-fields-for-volatile-data

http://www.daniweb.com/web-development/php/code/475238/linked-dropdowns-select-fields-for-static-ish-data

The second may be more useful as you probably wouldn't need to update your cities table very often.

[flynismo]

Thank you diafol, I will check those links out and let you know the results!

[flynismo]

Thank you diafol, I will check those links out and let you know the results!

[malatamil]

thanks for this is what i expect.
http://demos.diafol.org/ajax-linked-dropdowns.php

in localhost its working fine. but in server i got one problem the second dropdownbox is not working properly its showing last country_id values, if i click any country then that value is not chage. what can do for that ??

$stateSQL ='SELECT DISTINCT states.state_id AS value, states.state AS label 
                    FROM states 
                 WHERE states.country_id = ?
                 ORDER BY states.state';

in that WHERE states.country_id = ?, what is ? means. sorry if i ask silly question.

[diafol]

You need to be using PDO or mysqli as it's a prepared query. The '?' is a placeholder for a parameter.

Without seeing your code, it's difficult to know what's going on.

Also - start your own thread or add to the code snippet thread:

http://www.daniweb.com/web-development/php/code/476623/ajaxed-linked-dropdowns-select-fields-for-volatile-data

Otherwise this will hijack flynismo's.

[malatamil]

used mysqli

[malatamil]

my html form like

<tr><td><div align="left">City:</div></td><td><select id="country" class="diafolDrop" name="city"></select></td></tr>
        <tr><td><div align="left">Place:</div></td><td><select id="state" class="diafolDrop" name="place" ></select></td></tr>


<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="includes/diafolatile.js"></script>
<script>
 $('.diafolDrop').linkedDrops({
    'url' : 'includes/diadem.php',
    'controls': {"country":["value","label"],"state":["value","label"]}
 });
</script>

diadem.php

<?php
require '../includes/diafolatile.class.php';

//Choose either PDO or mysqli (mysqli without mysqlnd driver also supported)

//$db = new PDO("mysql:host=localhost;dbname=daniweb","root","");
$db = new mysqli("host","user","pass","namshimoga");

$drops = new diaFolatile($db);

$countrySQL ='SELECT DISTINCT countries.country_id AS value, countries.country AS label 
                    FROM countries 
                        INNER JOIN states ON states.country_id = countries.country_id 
                 ORDER BY countries.country';

$stateSQL ='SELECT DISTINCT states.state_id AS value, states.state AS label 
                    FROM states 
                 WHERE states.country_id = ?
                 ORDER BY states.state';

/*$citySQL =        'SELECT DISTINCT cities.city_id AS value, cities.city AS label 
                    FROM cities 
                        INNER JOIN members ON members.city_id = cities.city_id 
                 WHERE cities.state_id = ?
                 ORDER BY cities.city';*/

/*$memberSQL =  'SELECT DISTINCT countries.country, states.state, cities.city, members.member_id AS id, members.name AS nm 
                    FROM countries
                        INNER JOIN states ON states.country_id = countries.country_id 
                                INNER JOIN cities ON cities.state_id = states.state_id 
                                    INNER JOIN members ON members.city_id = cities.city_id 
                 WHERE members.city_id = ? 
                 ORDER BY nm';*/

$drops->addItem(new diaFolItem('country', $countrySQL, 'value'));
$drops->addItem(new diaFolItem('state', $stateSQL, 'value', true));
//$drops->addItem(new diaFolItem('city', $citySQL, 'value', true));
//$drops->addItem(new diaFolItem('member', $memberSQL, 'id', true));

$id = (isset($_GET['id'])) ? $_GET['id'] : NULL;
$value = (isset($_GET['value'])) ? $_GET['value'] : NULL;

$php_array = $drops->getData($id, $value);
echo json_encode($php_array);   

?>

diafolatile.class.php

<?php

class diaFolatile
{
    private $item = array();
    private $itemIds = array();
    private $db;

    /**
       * 
       * Constructor
       *
       * @param mysqli object $db
       */
    public function __construct($db)
    {
        $this->db = $db;
    }

    /**
       * 
       * Add a dropdown (diafolItem) item and create a list of ids
       *
       * @param diafolItem $diafolItem
       */

    public function addItem(diafolItem $diafolItem)
    {
        $this->items[] = $diafolItem;
        $this->itemIds[] = $diafolItem->id;
    }

    /**
       * 
       * Retrieve Data from DB
       *
       * @param string $id OPTIONAL - the form dropdown id (not required for first ajax call)
       * @param mixed $value OPTIONAL - typically the PK value (not required for first ajax call)
       * @return mixed[] - an array of data
       */
    public function getData($id = NULL, $value=NULL)
    {
        if($id && in_array($id, $this->itemIds))
        {
            $firstId = array_search($id, $this->itemIds) + 1;
        }else{
            $firstId = 0;   
        }

        $chainLength = count($this->itemIds);

        $output = array();
        $usedItems = array();
        for($i=$firstId;$i<$chainLength;$i++)
        {
            $item = $this->items[$i]; 

            $sql = $item->sql;
            $stmt = $this->db->prepare($sql);

            if($value) $this->bind($stmt,$value);

            $stmt->execute();

            $records = $this->fetchAllArray($stmt);

            if(!count($records))
            {
                $output[$item->id]['diafolError'] = 1000;
                break;  
            }
            $output[$item->id] = $records;
            $value = $records[0][$item->pkField];
        }

        return $output;
    }
    /**
       * 
       * Bind parameter to PDO or MySQLi Prepared Statement
       *
       * @param object $stmt PDOStatement or mysqli_stmt
       * @param mixed $value Value to be bound into prepared statement
       * @return mixed
       */
    private function bind($stmt, $value)
    {
        if(get_class($stmt) == 'PDOStatement')
        {
            return $stmt->bindParam(1, $value);
        }elseif(get_class($stmt) == 'mysqli_stmt'){
            $type = (is_int($value)) ? 'i' : 's';
            return $stmt->bind_param($type, $value);
        }
    }
    /**
       * 
       * Fetch All Results From PDO or MySQLi Prepared Statement
       * Takes into account lack of mysqlnd driver for mysqli
       *
       * @param object $stmt PDOStatement or mysqli_stmt
       * @return mixed[] - an array of all data from prepared statement
       */
    private function fetchAllArray($stmt)
    {
        if(get_class($stmt) == 'PDOStatement')
        {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }elseif(get_class($stmt) == 'mysqli_stmt'){
            if (function_exists('mysqli_get_client_stats'))
            {
                $result = $stmt->get_result();
                return $result->fetch_all(MYSQLI_ASSOC);
            }else{
                //For servers without mysqlnd driver
                $result = array();
                $fieldList = array();

                $meta = $stmt->result_metadata(); 
                while ($field = $meta->fetch_field()) 
                { 
                    $params[] = &$row[$field->name]; 
                } 

                call_user_func_array(array($stmt, 'bind_result'), $params); 

                while ($stmt->fetch()) { 
                    foreach($row as $key => $val) 
                    { 
                        $c[$key] = $val; 
                    } 
                    $result[] = $c; 
                } 
                return $result;
            }
        }
    }
}

class diafolItem
{
    public $id;
    public $pkField;
    public $where;
    public $sql;

    public function __construct($id, $sql, $PK, $where=false)
    {
        $this->id = $id;
        $this->pkField = $PK;
        $this->where = $where;
        $this->sql = $sql;
    }
}

diafolatile.js

(function($){
  $.fn.linkedDrops = function(options) {
    var defaults = {
        //leave for adding functionality
    };
    var options = $.extend({}, defaults, options);

    $(this).change(function(){
       selectId = $(this).attr('id');
       selectValue = $(this).val(); 
       initiateSelects(selectId, selectValue);
    });

   function initiateSelects(selectId, selectValue){
      var url = options.url;
      if(!selectId)
      {
          id = null;
          value = null;
      }else{
          id = selectId;
          value = selectValue;
      }
      $.getJSON(url,{id: id, value: value}, function(returnData)
      {
        $.each(returnData, function(i,v){

            switch($('#' + i)[0].tagName)
            {
                case 'SELECT':
                    insertData = createOptions(i,v);
                    break;
                case 'TBODY':
                case 'TABLE':
                    insertData = createRows(i,v);
                    break;  
            }
            $('#' + i).html(insertData);
        });
      });

    }

    function createOptions(i,v)
    {
        optionData ='';
        $.each(v, function(i2,v2)
        {
            optionData += "<option value='" + v2[options.controls[i][0]] + "'>" + v2[options.controls[i][1]] + "</option>"; 
        });
        return optionData;      
    }

    function createRows(i,v)
    {
        rowData ='';
        $.each(v, function(i2,v2)
        {
            rowData +="<tr>";
            for(j=0;j<Object.keys(v2).length;j++)
            {
                rowData += "<td>" + v2[options.controls[i][j]] + "</td>"; 
            }
            rowData += "</tr>"; 
        });
        return rowData;     
    }
   initiateSelects();
  };
}(jQuery));

[iamthwee]

Please don't hijack threads.

Another example, courtesy of StackO oooops...
http://jsfiddle.net/GWact/

[diafol]

Awaiting response from flynismo or any other suggestions from contributors.

Question for flynismo: will there be multiple cities for a single zip code? I thought it would be the other way around - multiple zips for a city?