Diafol's Demos

Back To Index

<demo>Linked Ajax Dropdowns with Dynamic Table</demo>

A demo for linked dropdowns. The example uses 3 dropdowns and a data table, but you can use this PHP class and jQuery plugin to cascade many more items. The data is limited - as this is just a demo. It could be used with tricked-up select widgets like ddSlick or msDropDown.

Member Id Member

Markup Example

Here we set up 3 linked select form fields (dropdowns) and a data table: 'country', 'state', 'city' and 'member'. All except the last one in the cascade should have a common classname in order to trigger an action in the jQuery plugin. The example uses 'diafolDrop', but it could be anything. The html id for 'member' is placed in the <tbody> tag as that is where updatable data will be placed. It could however be placed into the <table> tag, but that would overwrite everything inside that tag, so we'd lose our headings.

<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 jQuery plugin currently takes two parameters: 'url' and 'controls'. The 'url' is simply the url of the Ajax file. The 'controls' parameter refers to the JSON keys provided by the Ajax page for each item in our cascade. This will become clear if you see the SQL statements below.

Ajax File

In order to make this as flexible as possible, the database object is passed as a parameter to the diaFolatile object. The diaFolatile object currently accepts PDO or MySQLi (with or without native driver [mysqlnd] installed).

Each item in the cascade needs its own SQL query, INNER JOINED to the end of the cascade in order to avoid "childless" dropdowns or datatables, e.g. Spain is in the countries table, but it does not have any related states, cities or members - so for this reason it should not appear in the country dropdown.

There are other ways to do this - for example, we could create one big recordset array and hack away at it to create discrete data arrays for cascade items, but I found this to be a long-winded option, which also threw up issues with sorting.

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

//Use PDO or MySQLi
$db = new PDO("mysql:host=localhost;dbname=mydb","root","");
//$db = new mysqli("localhost","root","","mydb");

$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 
			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);	
?>

The diaFolatile object has just the two methods:

The diafolItem object takes the following parameters:

  1. The html Id
  2. The SQL Statement(mysql flavoured)
  3. The Primary Key used in the SQL Statement
  4. An optional boolean value (true, false), defaulted to false: Whether there's a WHERE clause containing a simple placeholder (?)

jQuery Plugin

Here's the code so far.

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

PHP Classes

<?php
class diaFolatile
{
	private $item = 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;
	}
}
?>

Download and Summary

Zip File: diafolatile.zip (6Kb)

File Description
ajax-linked-dropdown.php Just a html template with references to js libraries and plugins
js/diafolatile.js The jQuery plugin
class/diafolatile.class.php PHP classes, diaFolatile and diafolItem
includes/diadem.php Specific php file called by Ajax
sql/diafolatile.sql Limited data for the demo
css/main.css Minimal styling