Linked Dropdowns (Select Fields) for Static-ish Data

diafol 5 971 Views

Hello All. Been playing around with some linked dropdowns. It seems to be a recurring theme here on DW, so I thought I'd offer this up as a possible solution or for discussion, to see how it could be improved.

The premise for this set of linked dropdowns is that it is not dependent on php or Ajax following page load, that is to say, all the data is initially dumped into json format from the database and then everything is left to javascript. I've used jQuery to produce a plugin, but the same functionality is possible with just plain js.

Because no calls are made to the server following page load, this is not suitable for volatile data, as any changes on the server (database) would not be seen in the dropdowns until the page was refreshed.

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

I'll just run through a few line of code in the example page and explain what's going on, just in case it's not too clear.

$sql = "SELECT c.country_id, c.country, s.state_id, s.state, t.city_id, t.city 
            FROM countries AS c 
                INNER JOIN states AS s ON c.country_id = s.country_id 
                INNER JOIN cities AS t ON s.state_id = t.state_id 
            ORDER BY c.country, s.state, t.city";

This is a pretty typical joined SQL statement. The tables have this type of related structure.

FIELD      TYPE              
--------   ----------------  
country_id INT(3) UNSIGNED           
country    VARCHAR(30)

FIELD      TYPE              
--------   ----------------  
state_id   INT(5) UNSIGNED           
state      VARCHAR(30)                
country_id INT(3)           

FIELD     TYPE              
--------  ----------------  
city_id   INT(5) UNSIGNED           
city      VARCHAR(30)                
state_id  INT(6)

Anyway, the next line:

$dropdowns = array("country"=>array(0,1),"state"=>array(2,3),"city"=>array(4,5));

Sets the key to the dropdown id attribute as in <select id="country"> and <select id="state">, etc.
The numbers in the arrays refer to the positions of the option item values and option item labels, respectively in the SQL statement. For example,

SELECT c.country_id, c.country, s.state_id, s.state, t.city_id, t.city

The country_id field is at Position 0 and the country field is at Position 1. We wish to make the country_id field the value for the options in the country dropdown and the country field as the labels to be displayed, as in:

<option value="1">UK</option>
<option value="2">Ukraine</option>
<option value="3">USA</option>

The same goes for the other items in the $dropdowns array.

The form:

<form>
    <select id="country" class="myLinkedDrops">
    </select>
    <select id="state" class="myLinkedDrops">
    </select>
    <select id="city" class="myLinkedDrops">
    </select>
</form>

should have dropdowns that have the 'id's listed in the $dropdowns array. In addition, give the dropdowns a class with a shared name - it could be anything, I've used "myLinkedDrops".

In order to get everything working, you need to include 3 things in a specific order:

1) The jQuery library
2) The linkedDrops jQuery plugin
3) Attach the method to the dropdowns and set options with data from the php object, $drops

Well, that should be it. As I mentioned, it should work with any number of linked dropdowns. One thing I haven't done is prepare this for more than one set of linked dropdowns. I'll leave that for a rainy day.

Comments as to how to improve this would be most welcome - especially on the jQuery plugin - it's the first one I've put online!

Apologies for the weird indenting below - editor cacked it up.

cereal commented: good solution! +13
JorgeM commented: great job! fantastic... +12 
//========================================================================//
//==================  PHP Class: linkeddrops.class.php  ==================//
//========================================================================//

<?php
class linkDrops
{
	private $db;
	private $dropdowns;
	public $json_keys;
	public $data;

	public function __construct($db, $sql, $dropdowns)
	{
        $this->db = $db;
        $this->dropdowns = $dropdowns;
        $this->json_keys = json_encode(array_keys($dropdowns));
        $this->runSQL($sql);
	}

	private function runSQL($sql)
	{
        $res = $this->db->query($sql) or die(mysqli_error($this->db));
        $data = $res->fetch_all(MYSQLI_NUM);
        $this->makeArrays($data);
	}
	
	private function makeArrays($data)
	{
		foreach($data as $record)
		{
            $pos = 0;
	  		foreach($this->dropdowns as $k=>$v)
			{
	   			if($pos == 0)
				{
                    $r[$k][$record[$v[0]]] = $record[$v[1]]; 
			    }else{
                    $r[$k][$prev][$record[$v[0]]] = $record[$v[1]];
	   			}
                $prev = $record[$v[0]];
                $pos++;
	  		}
	 	}
        $this->data = json_encode($r);
	}
}
?>

//========================================================================//
//===================  jQuery plugin: linkeddrops.js  ====================//
//========================================================================//

(function($){
  $.fn.linkedDrops = function(options) {
	var defaults = {
  		//leave for adding functionality
	};
	var options = $.extend({}, defaults, options);
	
	$(this).change(function(){
       selectId = $(this).attr('id');
       initiateSelects(selectId);
    });
	
   function initiateSelects(selectId){
      if(selectId != null){
         start = options.controls.indexOf(selectId) + 1;
      }else{
         start = 0; 
      }
      $.each(options.controls, function(i,v){
         if(i >= start){
            if(i == 0){
               $('#' + options.controls[i]).html(makeOptions(options.controls[i]));
            }else{
               id = $('#' + options.controls[i-1]).val();
               $('#' + options.controls[i]).html(makeOptions(options.controls[i], id));
            }
         }
      });
   }
  
   //create options for selects
   function makeOptions(level, id){
      output = '';
      if(id == null){
         arr = options.json[level];
      }else{
         arr = options.json[level][id];
      }
      $.each(arr, function(i,v){
         output += '<option value="' + i + '">' + v + '</option>'; 
      });
      return output;
   }

   initiateSelects();
  };
}(jQuery));

//========================================================================//
//======================  Example Page: trial.php  =======================//
//========================================================================//

<?php
require("linkdrop.class.php");

$db = new MySQLi("localhost","root","","daniweb");

$sql = "SELECT c.country_id, c.country, s.state_id, s.state, t.city_id, t.city 
			FROM countries AS c 
				INNER JOIN states AS s ON c.country_id = s.country_id 
				INNER JOIN cities AS t ON s.state_id = t.state_id 
			ORDER BY c.country, s.state, t.city";

//FORMAT: select field 'id'=>array(position of value for option in SQL, position of label for option in SQL)
$dropdowns = array("country"=>array(0,1),"state"=>array(2,3),"city"=>array(4,5));

//CREATE THE linkDrop Object
$drops = new linkDrops($db,$sql,$dropdowns);
?>

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>LinkDrop Experiment</title>
</head>
<body>
<form>
    <select id="country" class="myLinkedDrops">
    </select>
    <select id="state" class="myLinkedDrops">
    </select>
    <select id="city" class="myLinkedDrops">
    </select>
</form>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="linkeddrops.js"></script>
<script>
 $('.myLinkedDrops').linkedDrops({
  'controls' : <?php echo $drops->json_keys;?>,
  'json' : <?php echo $drops->data;?>
 });
</script>
</body>
</html>
Member Avatar for Ryan_12
Ryan_12 10 Newbie Poster

Thanks for the share! I actually like this

Is it okay if I use it and modify this in one of my websites?

Cheers!!

Member Avatar for Dani
Dani 4,329 The Queen of DaniWeb

I can't speak for diafol, but I think that's the point if our code snippet library. :)

Member Avatar for diafol
diafol

Absolutely! Use it any way you want. BTW, if you do develop it further, let us know - post a link in this thread :)

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