Codeigniter: How to make Mysql cascade dropdowns
We were on quite a web dev mood here at WCFM today, so we decided to share some Codeigniter goodness!
This can be a really simple and useful trick once you know it ;) With this example you will be able to use CI for:
filling a 'country' dropdown from Mysql
filling a 'cities' dropdown from Mysql depending on the selected country
On this example we will use the following Mysql database tables:
Table Countries
| ID (int, Primary Key) | Country (varchar 50) |
Table Cities
| ID (int, Primary Key) | City (varchar 50) | ID_Country (int, Foreign Key to Countries) |
So, first we start by establishing our model. Since CI practices and architecture aren't the focus of this article, we are going to keep it simple and place all the required queries on one model, but in a real-life project this decision must be taken according to the project's needs/architecture.
Model:
<?php class cities_countries_model extends CI_Model { public function __construct() { $this->load->database(); } //fill your contry dropdown public function getCountries() { $this->db->select('id,Country'); $this->db->from('Countries'); $query = $this->db->get(); foreach($query->result_array() as $row){ $data[$row['id']]=$row['Country']; } return $data; } //fill your cities dropdown depending on the selected city public function getCityByCountry($id_country=string) { $this->db->select('id,city'); $this->db->from('cities'); $this->db->where('id_country',$id_country); $query = $this->db->get(); return $query; } }
So far it's pretty linear, two basic queries on your model to fetch the required data. Next we will define the Controller, here we are going to establish the main mechanics for this 'recipe' to work out.
Controller:
<?php class cascadeDrop extends CI_Controller { public function __construct() { parent::__construct(); $this->load->database(); $this->load->helper('url'); $this->load->helper('form'); $this->load->model('cities_countries_model'); } public function index() { //starts by running the query for the countries dropdown $data['countryDrop'] = $this->cities_countries_model->getCountries(); //loads up the view with the query results $this->load->view('cascadeDrop/cascadeDrop', $data); } //call to fill the second dropdown with the cities public function buildDropCities() { //set selected country id from POST $id_country = $this->input->post('id',TRUE); //run the query for the cities we specified earlier $districtData['districtDrop']=$this->cities_countries_model->getCitiesByCountry($id_country); $output = null; foreach ($cityData['cityDrop']->result() as $row) { //here we build a dropdown item line for each query result $output .= "<option value='".$row->id."'>".$row->City."</option>"; } echo $output; } }
As you can see on the controller, we start by loading CI's database, url and form helpers (to access the DB model we did earlier and use html form features). To keep things (again) simple, we decided to run the query for the first dropdown and to load the dropdown view right on the index, but as you provably know, this can be done on a function or on whatever you want.
Next we define a function to fill the second dropdown with the desired cities. Here we start by fetching a POST variable 'id', that will be sent from the View (we will see this in detail next), and save it's value on a $id_city variable, that in turn is used as argument for the getCitiesByCountry function on the model.
By now we already have a set of results from the DB, so all that is left is attach them to the dropdown. To do this, we iterate over the set of results and build a dropdown item line (<option></option>) for each row, of course those lines are concatenated on a string that is 'printed' on the View through the 'echo' construct.
The final piece of the puzzle is the View where the controls are rendered and where the POST magic happens!
View:
<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>cascade drops example</title> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script> <script type="text/javascript"> $(document).ready(function() { $("#countriesDrp").change(function(){ /*dropdown post */ $.ajax({ url:"<?php echo base_url(); ?>index.php/cascadeDrop/buildDropCities", data: {id: $(this).val()}, type: "POST", success: function(data){ $("#cityDrp").html(data); } }); }); }); </script> </head> <body> <!--country dropdown--> <?php echo form_dropdown('countriesDrp', $countryDrop,'','class="required" id="countriesDrp"'); ?> <br /> <br /> <!--city dropdown--> <select name="cityDrp" id="cityDrp"> <option value="">Select</option> </select> <br /> </body> </html>
On this view we have a couple of key aspects to keep in mind. First you may notice that we used a CI dropdown helper for the country list and regular html <select> for the city list. This was done to better illustrate the mechanics of the whole procedure, after grasping this part it's a lot easier to move for a more 'advanced' concept such as 'communication between views' on CI, in fact that will be subject of our next CI web-cooking recipe ;)
But enough jibba-jabba, onward to the details of the view! The real trick to this process is the ajax post. On the beginning of the view file we have a jquery script that will enable us to do the post, it starts with checking if all elements are ready and loaded, next it listens to the 'selected' event from the country dropdown, which is the trigger for the post.
When the event is raised a ajax 'request' (let's call it like this) is created and on it we specify all sorts of useful stuff:
URL: the destination of the request (on this example is the controller/function)
DATA: the information we intend to add to the request (on this example the selected id value from the dropdown)
TYPE: request type (GET/POST)
Success: what happens if the request reaches it's destination
After we set the type, destination and data for the request, we specify the success action. In this case we tell jquery to look for the second dropdown and inject the request's response (remember that echo on the controller?) on the dropdown's html. And voilá, we get our dropdown populated with the query's results!
But wait!
"What if I want to filter the values from an already filled dropdown??" Piece of cake!
First add a select query to the model to fill the city dropdown.
Next add some code on the controller to:
-Run the query
-Fill the dropdown
...and thats it! Our previous code will just rebuild the city list whenever a value is selected on the country dropdown, thus filtering it.
So this is a quick and easy approach to implement cascade Mysql-populated dropdown boxes on Codeigniter. On our next tutorial we will approach view-to-view communication on CI using cascade dropdowns and other examples, so stay tuned!
From your favourite Martian Overlords,
By the Minion Rui Guedes ( Follow Rui on Twitter )
Good luck and godspeed!