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:

[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):

 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:

$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

//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

<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:

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:

$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:

And that's all there is to it. Here's a screenshot:

Bind Events to Dynamically-Added Elements with jQuery's .on()

This post follows one from another - Adding Form Controls Dynamically with jQuery. AT the end of that post, I mentioned that dynamically added form elements can often not have pre-defined events assigned to them as these elements are added after page load. Here's an example of adding a few textboxes to a form along with an associated button for incrementing the textbox value. This is just a trivial example, just to show the issues that can arise.

<form id="frm_numbers" method="post" action="anotherpage.php">
    <label for="tb_addinputs">No. Inputs to Add: </label> 
    <input id="tb_addinputs" type="number" min="1" max="6" value="1" /> 
    <button id="btn_addinputs">Add Inputs</button><br />
    <button id="btn_sync" style="display:none;">Sync All To First</button><br />
    <input type="submit" value="Upload" name="submit" id="submit" />
</form>

Here's some of the accompanying javascript, which all works fine:

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script>
$(function(){
    //Extend native from http://bit.ly/11MheU1
    String.prototype.repeat = function(num) {
        return new Array(isNaN(num)? 1 : ++num).join(this);
    }
        
    //Add specific number of controls and show sync button
    $('#btn_addinputs').click(function(e){
        e.preventDefault();
        addNewTextInputs();
        $('#btn_sync').show();
    });
    
    //general function to add text inputs    
    function addNewTextInputs(){
        var str = '<label>MyNumber</label> ';
        str += '<input class="tb_numbers" type="number" value="0" />';
        str += '<button class="btn_addten">+10</button><br />';
        var numToAdd = parseInt($('#tb_addinputs').val());
        $('#btn_sync').before(str.repeat(numToAdd));
    }

    //sync button click to make all text inputs equal to the first one
    $('#btn_sync').click(function(e){
        e.preventDefault();    
        if($('.tb_numbers').length > 1)$('.tb_numbers').val($('.tb_numbers').eq(0).val());
    });
});

So, if we add 5 inputs, we get this:

OK, so far so good. If we manually change the inputs and press 'sync', again everything works fine.

Now the problem arises when we try to attach a .click event to the 'Add Ten' buttons, so that they can add 10 to the value in the associated number input. If the button was present on page load, then we could do this:

$('.btn_addten').click(function(e){
    e.preventDefault();
    num = parseInt($(this).prev().val()) + 10;
    $(this).prev().val(num);        
});

Unfortunately, this won't work, so we have to resort to using the .on() method, which is no hardship:

$("#frm_numbers").on("click", "btn_addten", function(e){
    e.preventDefault();
    num = parseInt($(this).prev().val()) + 10;
    $(this).prev().val(num);
});

Magically this does the business - click the 'Add Ten' button and the associated number input increments by 10.
OK, you can argue that this isn't the most inspiring of scripts, but at least it shows how you can bind an event to a dynamically-added element.

Adding Form Controls Dynamically with jQuery

Let's have a look at an example, where we use a link to add file upload controls to our form.
Initially, we can create our html form thus:

<form id="uploadform" method="post" action="anotherpage.php" enctype="multipart/form-data">
    <p><a href="#" id="addupload">Add another upload control</a></p>
    <label>Upload an image:</label> <input type="file" name="file[]" class="imageupload" /> <br />
    <input type="submit" value="Upload" name="submit" id="submit" />
</form>

The resulting form should look like this - note no CSS/styling so it looks horrible! Also using <br /> tags in forms isn't the best way to do it, but this is only an example.

If we now add a very simple piece of jQuery-javascript, we should be able to add controls easily:

<form id="uploadform" method="post" action="anotherpage.php" enctype="multipart/form-data">
    <p><a href="#" id="addupload">Add another upload control</a></p>
    <label>Upload an image:</label> <input type="file" name="file[]" class="imageupload" /><br />
    <input type="submit" value="Upload" name="submit" id="submit" />
</form>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script>
$(function(){
    $('#addupload').click(function(){
        var addControl = '<label>Upload an image:</label>';
        addControl += ' <input type="file" name="file[]" class="imageupload" /><br />';
        $('#submit').before(addControl); 
    });
});
</script>

You'll notice that the submit button (#submit) acts as a hook so we can use the .before() method.
If you click the link twice and browse for files, you should see something like the following:

NB: this is all well and good if you just need basic form functionality, but if you have javascript running off these controls, then the newly added controls will not function as expected. This is because they were created after the page was fully loaded. Don't fret, there is a way around this limitation. I will create another post looking at the .on() method.