3 Feb 2013

Creating linked Select Dropdowns from MySQL

Getting linked Data from MySQL

We can often get into a tizz over trying to format data output from the database by using SQL queries. If the solution is straightforward, that's fine, but often it's a lot of work for nothing, as we have php as a means to process this data and to change it to anything we may need. I'm firmly of the opinion that you should let MySQL just get the data. So, lets look at an example where we need to extract country, state and city data for our linked select dropdowns. BTW - this is for static data, not Ajax-driven. I sometimes wonder why the server needs to be involved every time a select dropdown is clicked. Typically, this data will have the following format:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
[Country]
country_id [PK, int]
country [varchar]
 
[State]
state_id [PK, int]
state [varchar]
country_id [int]
 
[City]
city_id [PK, int]
city [varchar]
state_id [int]
The simplest way of getting this info would be to run 3 SQL queries, but in order to keep things a little more ordered, lets created a multiple JOIN SQL. This will ensure that we don't have a situation where we get orphaned data in our selects, e.g. countries that have no corresponding states, or states that have no cities.
Here's an example of some SQL (MySQL-flavoured):
?
1
2
3
4
5
6
7
SELECT c.country_id, c.country, s.state_id, s.state, i.city_id, i.city
FROM country AS c
 INNER JOIN state AS s
  ON c.country_id = s.country_id
 INNER JOIN city AS i
  ON s.state_id = i.state_id
ORDER BY c.country, s.state, i.city
This could give us the following type of data if we return an array from the resource:
?
1
2
3
4
5
6
7
8
9
10
11
12
$dbarr = array(
 array(1,'Canada',7,'Ontario',100,'Pembroke'),
 array(1,'Canada',8,'Quebec',101,'Laval'),
 array(1,'Canada',8,'Quebec',102,'Montreal'),
 array(1,'Canada',8,'Quebec',103,'Quebec'),
 array(2,'USA',10,'CA',300,'Los Angeles'),
 array(2,'USA',10,'CA',301,'San Diego'),
 array(2,'USA',11,'NY',302,'Buffalo'),
 array(2,'USA',11,'NY',303,'New York'),
 array(2,'USA',12,'VA',304,'Chesapeake'),
 array(2,'USA',12,'VA',305,'Norfolk')
);
Next, we put the data into a form that can be easily dealt with by javascript (jQuery), namely, json. This is incredibly easy to do in php, with json_encode(). Okay, on with the php bit:

Converting php Data to JSON

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
//Indentify the fields from the output array - so that we can create a well-formed arrays
$keys = array('country'=>array(0,1),'state'=>array(2,3),'city'=>array(4,5));
 
//This just gives some hooks for javascript and the select dropdown 'id' properties
$json_keys = json_encode(array_keys($keys));
 
//Create the arrays
function makeArrays($data,$keys){
 foreach($data as $record){
  $pos = 0;
  foreach($keys 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++;
  }
 }
 return $r;
}
 
//encode the data for use in javascript
$json = json_encode(makeArrays($dbarr,$keys));
So as you can see, pretty minimal stuff so far. The HTML selects need to have the same id properties as the fieldnames in the $json_keys variable ('country', 'state', 'city'):

Our Minimal HTML

?
1
2
3
<select id="country" class="linkedselects"></select>
<select id="state" class="linkedselects"></select>
<select id="city" class="linkedselects"></select>
You'll notice the class 'linkedselects' - this is important for the javascript.

Getting jQuery to Populate the Select Dropdowns

Right, so far so good, now for the jQuery-fuelled javascript:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script>
   //store data in json format from php
   var json = <?php echo $json;?>;
   var controls = <?php echo $json_keys;?>;
 
   //run on page load
   initiateSelects();
  
   //start creating select (cascade)
   function initiateSelects(selectId){
      if(selectId != null){
         start = controls.indexOf(selectId) + 1;
      }else{
         start = 0;
      }
      $.each(controls, function(i,v){
         if(i >= start){
            if(i == 0){
               $('#' + controls[i]).html(makeOptions(controls[i]));
            }else{
               id = $('#' + controls[i-1]).val();
               $('#' + controls[i]).html(makeOptions(controls[i], id));
            }
         }
      });
   }
  
   //change selection in country dropdown
   $('.linkedselects').change(function(){
       selectId = $(this).attr('id');
       initiateSelects(selectId);
   });
  
   //create options for selects
   function makeOptions(level, id){
      output = '';
      if(id == null){
         arr = json[level];
      }else{
         arr = json[level][id];
      }
      $.each(arr, function(i,v){
         output += '<option value="' + i + '">' + v + '</option>';
      });
         return output;
   }
</script>
That seems to be all that there is to it. You may notice the distinct lack of 'country', 'state' and 'city' in the javascript. This is deliberate, as you should be able to use the code with any similarly formatted data. In addition, you should be able to extend it to however many linked select dropdowns that you need. Here's the output:

**UPDATE (03/02/2013)**

Thought I'd expand on the extending the use. The changes required for 4 linked dropdowns - just the last line as the array is created from the DB:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
$dbarr = array(
 array(1,'Canada',7,'Ontario',100,'Pembroke','1000','John'),
 array(1,'Canada',8,'Quebec',101,'Montreal','1001','John1'),
 array(1,'Canada',8,'Quebec',101,'Montreal','1002','John2'),
 array(1,'Canada',8,'Quebec',103,'Quebec','1003','John3'),
 array(2,'USA',10,'CA',300,'Los Angeles','1004','John4'),
 array(2,'USA',10,'CA',301,'San Diego','1005','John5'),
 array(2,'USA',11,'NY',302,'Buffalo','1006','John6'),
 array(2,'USA',11,'NY',303,'New York','1007','John7'),
 array(2,'USA',12,'VA',304,'Chesapeake','1008','John8'),
 array(2,'USA',12,'VA',305,'Norfolk','1009','John9')
);
$keys = array('country'=>array(0,1),'state'=>array(2,3),'city'=>array(4,5),'user'=>array(6,7));
And the HTML - again just add another select dropdown:
?
1
2
3
4
<select id="country" class="linkedselects"></select>
<select id="state" class="linkedselects"></select>
<select id="city" class="linkedselects"></select>
<select id="user" class="linkedselects"></select>
And that's all there is to it. Here's a screenshot:
Posted by at 4 comments:
Labels: , , , ,
Subscribe to: Posts (Atom)