Ajaxed Linked Dropdowns (Select Fields) for Volatile Data

Updated diafol 3 1K Views

// EDIT 2014-04-07 //
New version (1.0.3) posted at the bottom

Demo Page: http://demos.diafol.org/ajax-linked-dropdowns.php

***********************************************

Hello All. Been playing around with more linked dropdowns (select form fields), following the code snippet posted for static-ish data here. The static-ish version is of limited use (or of no use!) if data in the DB is updated regularly. So, here goes...

No data is loaded by PHP initially, everything is provided through an Ajax call, however, that could be easily modified if required. I've used jQuery once again, as my plain javascript skills just aren't up to the job, and have produced a plugin for ease of use, which should also be extensible for those with a want for such things.

The example shows the old chestnut once again, "Country | State | City" linked dropdowns, but the class allows any number of linked dropdowns.

The snippet comes in 4 parts:

  • A php class file containing 2 classes: diaFolatile and diafolItem (classes/diafolatile.class.php)
  • A jQuery plugin file (js/diafolatile.js)
  • An ajax php page with specific setup for the example (includes/diadem.php)
  • A plain example usage page (demo.php)

I'll kick off with the demo page (demo.php) as that's the easiest:

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Linked Dropdowns for Volatile Data</title>

</head>
<body>
<form>
    <select id="country" class="diafolDrop"></select>
    <select id="state" class="diafolDrop"></select>
    <select id="city"></select>
</form>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="js/diafolatile.js"></script>
<script>
 $('.diafolDrop').linkedDrops({
    'url' : 'includes/diadem.php'
 });
</script>

As you can see, no php involved! Include your linked dropdowns, give them an 'id' and a shared classname - it can be anything, but I've used 'diafolDrop' in this example. I haven't given the last dropdown the shared classname as I don't want it to fire when it is changed - but if you want to link it to a datatable for example, then you may want to enable it.

Then the all-important link to the jQuery library and the diafolatile plugin.
The setup method for the linked dropdown currently just takes one parameter - the url of the ajax file. You could of course hard-code this into the defaults object in the plugin and then not supply any parameters.

OK so far?

On with the ajax includes/diadem.php file:

<?php
require '../class/diafolatile.class.php'; //path from this include file

$mysqli = new mysqli("localhost","root","","daniweb");
$drops = new diaFolatile($mysqli);

$drops->addItem(new diaFolItem('country',array('country_id','country'),array('value','label'),'countries', 'value', 'country'));
$drops->addItem(new diaFolItem('state',array('state_id','state'),array('value','label'),'states', 'value', 'state', 'WHERE country_id = ?'));
$drops->addItem(new diaFolItem('city',array('city_id','city'),array('value','label'),'cities', 'value', 'city', 'WHERE state_id = ?'));

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

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

echo $json;

?>

Include the php class and set up the database object. I'm using mysqli here, but you could easily modify the class to use PDO or whatever else you use. Anyway, create an instance of diaFolatile and pass the database object to it.

Ok, now to tell diaFolatile which linked dropdowns you wish to use and any accompanying data pertaining to those dropdowns.

You'll notice that each dropdown is added by creating a diafolItem object and each can take 8 parameters. I know that's a lot, but it should allow some degree of flexibility. Obviously you can change the class however you like and pass a full SQL prepared query, but that's another story.

The parameters are:

  1. $id - the id value of the dropdown in the form
  2. $fields - an array of fields required from your DB - typically the id and the text for the option tag
  3. $outputKeys - an array of keys which will feature in the json object and picked up by the jQuery plugin
  4. $table - the DB table from which this data is taken
  5. $outputKeyPK - the outputKeys item that corresponds to the Primary Key
  6. $order - an 'ORDER BY' clause. Just list the items from $fields. Typically ordered alphabetically - it can take DESC, etc.
  7. $whereClause - Not required for the first dropdown in the cascade. Uses simple mysqli placeholder (?).
  8. $fkType - OPTIONAL - DEFAULT = 'i' (integer). For binding parameters to the $whereClause. Other options are 's' (string) and 'd' (double).

Admittedly a little long-winded. Suggestions for simplification without affecting flexibility would be very welcome. That's pretty much it, The rest is just passing any $_GET variables from the Ajax call to the main method (getData()).

The plugin and class are included in the snippet. I won't bother posting the files above again.

As mentioned, this should allow any number of linked dropdowns. One thing to mention is that if there are childless dropdowns - an error is reported in the json data. Ideally this wouldn't happen, but the alternative as I saw it was to produce fully INNER-JOINed SQL queries for each dropdown. Any ideas about this would also be appreciated.

I'll leave it to others to lump in exceptions / error handling - this is just a quickie.

ZIP FILE: http://www.daniweb.com/images/attachments/0/ba44dabe7070059afe525be5c3d5d598.zip

diafolatile.zip (4.64 KB)
pritaeas commented: Nice. +14
cereal commented: huge work! +13 
//===================================================================================//
//===================================================================================//
//========================= PHP CLASS: 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) $stmt->bind_param($item->fkType, $value);
			
			$stmt->execute();
			
			$result = $stmt->get_result();
			$records = $result->fetch_all(MYSQLI_ASSOC);
			
			if(!count($records))
			{
				$output[$item->id]['diafolError'] = 1000;
				break;	
			}
			$output[$item->id] = $records;
			$value = $records[0][$item->pkField];
		}
	
		return $output;
	}
}

class diaFolItem
{
	public $id;
	public $pkField;
	public $fkType;
	public $sql;
	
	private $fields = array();
	private $outputKeys = array();
	private $table;
	private $order;
	private $fkField;
	
	//dropdown id, fields ,outputKeys AS, table, PK, sort order , whereClause=NULL, FKtype='i'
	/**
      * 
      * Constructor - create the diafolItem object
      *
      * @param string $id form dropdown id
      * @param mixed[] $fields db table fields to select
      * @param mixed[] $outputKeys keys to label data in output
      * @param string $table DB table from where the data is selected
      * @param string $outputKeyPK the key in $outputKeys correlating to the Primary Key in $fields
      * @param string $order an 'ORDER BY' clause - without the 'order by' - accepts DESC etc
      * @param string[] $whereClause OPTIONAL (REQUIRED for every dropdown except the first one) a mysqli 'WHERE' clause using simple parameter placeholders (?)
      * @param mixed[] $fkType OPTIONAL DEFAULT = 'i' takes mysqli data types (i,s,d)
      */
	public function __construct($id, $fields, $outputKeys, $table, $outputKeyPK, $order, $whereClause=NULL, $fkType='i')
	{
		$this->id = $id;
		$this->fields = (array) $fields;
		$this->outputKeys = (array) $outputKeys;
		$this->table = $table;
		$this->pkField = $outputKeyPK;
		$this->order = $order;
		$this->whereClause = $whereClause;
		$this->fkType = $fkType;

		$this->sql = $this->createSQL();
	}

  	 /**
       * 
       * Create an Prepared Statement
       *
       * @return string - A prepared statement
       */
	private function createSQL()
	{
		$f = array_combine($this->outputKeys, $this->fields);
		$fieldArray = array();
		foreach($f as $k=>$v) $fieldArray[] = $v . ' AS ' . $k;
		$sql = "SELECT DISTINCT " . implode(',', $fieldArray) . ' FROM ' . $this->table;
		if($this->whereClause) $sql .= " " . $this->whereClause;
		$sql .= " ORDER BY " . $this->order;
		return $sql; 	
	}
}

//===================================================================================//
//===================================================================================//
//=========================== jQuery PLUGIN: 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){
			optionData ='';
			$.each(v, function(i2,v2)
			{
				optionData += "<option value='" + v2.value + "'>" + v2.label + "</option>"; 
			});
			$('#' + i).html(optionData);
				
		});
	  });
	}
   initiateSelects();
  };
}(jQuery));
Member Avatar for diafol
diafol

Revison 2

OK, thought about it some more and the diafolItem constructor seems far too laborious. Also, linked dropdowns usually end with a data table of some description. So here's some modification:

DEMO PAGE (demo.php)
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Linked Dropdowns for Volatile Data</title>
<style>
table{
    border-collapse:collapse;
    border: 1px solid black;    
}
tbody tr:nth-child(odd) {
   background-color: #ccc;
}
th{
    color: white;
    background-color:black; 
}

th, td{
    border: 1px solid black;
}
</style> 
</head>
<body>
<h3>Country-State-City-Members Demo</h3>
<table>
    <thead>
        <tr>
            <th><label for="country">Country:</label> <select id="country" class="diafolDrop"></select></th>
            <th><label for="state">Region:</label> <select id="state" class="diafolDrop"></select></th>
            <th><label for="city">City/Town:</label> <select id="city"  class="diafolDrop"></select></th>
            <th>Member Id</th>
            <th>Member</th>
        </tr>
    </thead>
    <tbody id="member">
    </tbody>
</table>


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

The only changes really is an added option 'controls' for the linkedDrops method. This simply gives the order that items should be inserted in html options or tables/tbodys. Each object is the htmlid: array of keys in order to be used.

Making the diafolItem constructor simpler did mean making the SQL statements a bit more complicated. In order to ensure that the were no childless dropdowns, a full INNER JOIN through the data cascade is necessary - if anybody has a better idea, please share.

DIADEM PAGE (includes/diadem.php)
<?php
require '../class/diafolatile.class.php';

$mysqli = new mysqli("localhost","root","","daniweb");

$drops = new diaFolatile($mysqli);

$countrySQL =   'SELECT DISTINCT countries.country_id AS value, countries.country AS label 
                    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 
                 ORDER BY countries.country';

$stateSQL =     'SELECT DISTINCT states.state_id AS value, states.state AS label 
                    FROM states 
                        INNER JOIN cities ON cities.state_id = states.state_id 
                            INNER JOIN members ON members.city_id = cities.city_id 
                 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);   
?>

You'll notice that the last SQL statement, $memberSQL which should be the simplest is in fact the most long-winded. It doesn't have to be - I just made it like that in order to create a "header dropdown" in the output table.

The number of mandatory parameters for diafolItem is down to 3.

  • Param1 = html id
  • Param2 = SQL for selecting data
  • Param3 = Primary Key field in statement
  • Param4 = Whether Statement has a WHERE clause (true for all except first in dropdown cascade), default = false
  • Param5 = MySQLi datatype for binding parameter in WHERE clause, default='i'
CLASSES FILE (class/diafolatile.class.php)

The diafolItem class is now a lot simpler - it's just this:

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

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

One could argue that a separate class is not required and that a diaFolatile method could be used to import the data. But anyway...

No changes required to the original diaFolatile class :)

JQUERY PLUGIN FILE (js/diafolatile.js)

The biggest change is to the jQuery plugin, with the added option and more flexible output - for dropdowns and for tabular data.

(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));

As previously mentioned, any improvements are most welcome. Here's a screen shot:

8124acf1cf320848af5b3957334a196d

5661158bd7d7aa059c59c5d39b950d0e

ZIP:

diafolatile.zip (4.39 KB)
Edited by diafol
Member Avatar for diafol
diafol

Revison 3

I encountered a problem when I uploaded it to a demo site. It became apparent that the mysqlnd driver was not installed. This has been flagged by a number of site owners, so I decided to enable PDO, mysqli without mysqlnd and mysqli with mysqlnd.

So now you can do this:

$db = new PDO("mysql:host=localhost;dbname=daniweb","root","");
$drops = new diaFolatile($db);

Or this:

$db = new mysqli("localhost","root","","daniweb");
$drops = new diaFolatile($db);

No changes to anything other than the diaFolatile class, which needed a couple of extra private methods and two small changes to the public getData method:

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);
        //First change here
        if($value) $this->bind($stmt,$value);

        $stmt->execute();

        //Second change here
        $records = $this->fetchAllArray($stmt);

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

    return $output;
}

The additional methods:

/**
  * 
  * 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;
        }
    }
}

Demo Page: http://demos.diafol.org/ajax-linked-dropdowns.php

Archive (Version 1.0.3):

diafolatile.zip (5.92 KB)
Edited by diafol
Member Avatar for malatamil
malatamil 9 Junior Poster

in this first dropdown is working fine if i click any country then in the second dropdown state is no changed

Member Avatar for diafol
diafol

Sounds like a problem with your Ajax. Without code, it's difficult to know what's going on.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview