Thursday, 7 June 2018

Codeigniter Dynamic Dependent Select Box using Ajax


The Dynamic dependent dropdown list box will auto fill data in the select box based on the parent select box selection. This type of dynamic dependent select box mainly used in selection of country-state-city or category-subcategory. It will load dynamic data in dropdown select boxes without refreshing of page then this type of feature will makes our web application more user-friendly. By using Jquery with Ajax we can simply developed this type of dynamic dependent select box feature without refresh of web page.

In this Codeigniter web tutorial, We will learn how can we make dynamic dependent select box or dropdown in Codeigniter by using Ajax with Jquery and Mysql database. For display dynamic dependent dropdown feature, here we will take example of relational select box of country state city in Codeigniter. Here State select box data will be related to country select box and city select box data will be related to state select box. By the selection of dropdown select box of country and state then dependent select box of state and city data will be get from the mysql database without refresh of page by using Ajax with Jquery in Codeigniter.

Here In this example on page load it will displays countries data in country select box. When we have select country then respective states data will be fetched from mysql table and display under the states dropdown without reloading of page. Same way when user has select state from state dropdown then selected state cities data will be fetched from mysql database and fill under city dropdown.






Make Database Tables


For Store data of country, state and city and these three table has been required to created into mysql database. And in all three tables would be connection between country, state and city table. Here first relationship between country and state table and second relationship between city and state table.

Country Table

Below SQL script will makes country table in Mysql Database.


--
-- Database: `country_state_city`
--

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'USA'),
(2, 'Canada'),
(3, 'Australia'),
(4, 'India');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `country`
--
ALTER TABLE `country`
  ADD PRIMARY KEY (`country_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `country`
--
ALTER TABLE `country`
  MODIFY `country_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


State table

Below SQL script will make state table in your Mysql Database.


--
-- Database: `country_state_city`
--

-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'New York'),
(2, 1, 'Alabama'),
(3, 1, 'California'),
(4, 2, 'Ontario'),
(5, 2, 'British Columbia'),
(6, 3, 'New South Wales'),
(7, 3, 'Queensland'),
(8, 4, 'Karnataka'),
(9, 4, 'Telangana');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `state`
--
ALTER TABLE `state`
  ADD PRIMARY KEY (`state_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `state`
--
ALTER TABLE `state`
  MODIFY `state_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;


City table

Below SQL script will make city table in your Mysql Database.


--
-- Database: `country_state_city`
--

-- --------------------------------------------------------

--
-- Table structure for table `city`
--

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `city`
--

INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'New York city'),
(2, 1, 'Buffalo'),
(3, 1, 'Albany'),
(4, 2, 'Birmingham'),
(5, 2, 'Montgomery'),
(6, 2, 'Huntsville'),
(7, 3, 'Los Angeles'),
(8, 3, 'San Francisco'),
(9, 3, 'San Diego'),
(10, 4, 'Toronto'),
(11, 4, 'Ottawa'),
(12, 5, 'Vancouver'),
(13, 5, 'Victoria'),
(14, 6, 'Sydney'),
(15, 6, 'Newcastle'),
(16, 7, 'City of Brisbane'),
(17, 7, 'Gold Coast'),
(18, 8, 'Bangalore'),
(19, 8, 'Mangalore'),
(20, 9, 'Hydrabad'),
(21, 9, 'Warangal');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `city`
--
ALTER TABLE `city`
  ADD PRIMARY KEY (`city_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `city`
--
ALTER TABLE `city`
  MODIFY `city_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;





Dynamic_dependent.php(Controller)


Here this Dynamic_dependent Contollers has 4 functions like __construct(), index(), fetch_state() and fetch_city().

  • __construct() - This function has been load Dynamic dependent model.
  • index() - This function has retrived country data from mysql database using Dyamic dependent model and pass this data to view file.
  • fetch_state() - This function retrived state data from mysql database based on country id using dynamic dependent model and return data in html format to ajax request.
  • fetch_city() - This function has retrived city data from mysql databse based on state id using dynamic dependent model and return data in html format to ajax request.


<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Dynamic_dependent extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('dynamic_dependent_model');
 }

 function index()
 {
  $data['country'] = $this->dynamic_dependent_model->fetch_country();
  $this->load->view('dynamic_dependent', $data);
 }

 function fetch_state()
 {
  if($this->input->post('country_id'))
  {
   echo $this->dynamic_dependent_model->fetch_state($this->input->post('country_id'));
  }
 }

 function fetch_city()
 {
  if($this->input->post('state_id'))
  {
   echo $this->dynamic_dependent_model->fetch_city($this->input->post('state_id'));
  }
 }
  
}


Dynamic_dependent_model.php(Model)


This Dynamic dependent model has four functions like fetch_country(), fetch_state($country_id) and fetch_city($state_id).

  • fetch_country() - This function fetch data from Mysql database and return result data in PHP object.
  • fetch_state($country_id) - This function fetch data from Mysql database based on value of country id and return back data in html format.
  • fetch_city($state_id) - This function fetch city data from Mysql databse based on value of state id and return back data city data in html format.


<?php
class Dynamic_dependent_model extends CI_Model
{
 function fetch_country()
 {
  $this->db->order_by("country_name", "ASC");
  $query = $this->db->get("country");
  return $query->result();
 }

 function fetch_state($country_id)
 {
  $this->db->where('country_id', $country_id);
  $this->db->order_by('state_name', 'ASC');
  $query = $this->db->get('state');
  $output = '<option value="">Select State</option>';
  foreach($query->result() as $row)
  {
   $output .= '<option value="'.$row->state_id.'">'.$row->state_name.'</option>';
  }
  return $output;
 }

 function fetch_city($state_id)
 {
  $this->db->where('state_id', $state_id);
  $this->db->order_by('city_name', 'ASC');
  $query = $this->db->get('city');
  $output = '<option value="">Select City</option>';
  foreach($query->result() as $row)
  {
   $output .= '<option value="'.$row->city_id.'">'.$row->city_name.'</option>';
  }
  return $output;
 }
}

?>


dynamic_dependent.php(View)


This output file of Dynamic Dependent select box and it will display country state and city dropdown on web page. In this file we have use Javascript library jquery and Bootstrap library. In this file you can find that how country dropdown has been fill with data which it has get from controller and fill into country select box. Here State and City dropdown has been blank because it is dynamic and it will fill with data when we have select country select box.

In Jquery code you can find when we have select country from country dropdown, then selected country id has been pass as data to controller and fetch_state() method of controller will fetch state data based on selected country id from mysql databse by using model and that model return state data in html format which has send by controller to ajax request and filled into state dynamic dropdown.

Same way in state dropdown also, when we have select state from state select box, then in ajax request it has pass state id as data to fetch_city() controller method and that method will fetch city data based on value of state id from mysql database by using model and that model return city data in html format and after this controller has return that data to ajax request and it will fill city dynamic dropdown with data.


<html>
<head>
    <title>Codeigniter Dynamic Dependent Select Box using Ajax</title>
    
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 <style>
 .box
 {
  width:100%;
  max-width: 650px;
  margin:0 auto;
 }
 </style>
</head>
<body>
 <div class="container box">
  <br />
  <br />
  <h3 align="center">Codeigniter Dynamic Dependent Select Box using Ajax</h3>
  <br />
  <div class="form-group">
   <select name="country" id="country" class="form-control input-lg">
    <option value="">Select Country</option>
    <?php
    foreach($country as $row)
    {
     echo '<option value="'.$row->country_id.'">'.$row->country_name.'</option>';
    }
    ?>
   </select>
  </div>
  <br />
  <div class="form-group">
   <select name="state" id="state" class="form-control input-lg">
    <option value="">Select State</option>
   </select>
  </div>
  <br />
  <div class="form-group">
   <select name="city" id="city" class="form-control input-lg">
    <option value="">Select City</option>
   </select>
  </div>
 </div>
</body>
</html>
<script>
$(document).ready(function(){
 $('#country').change(function(){
  var country_id = $('#country').val();
  if(country_id != '')
  {
   $.ajax({
    url:"<?php echo base_url(); ?>dynamic_dependent/fetch_state",
    method:"POST",
    data:{country_id:country_id},
    success:function(data)
    {
     $('#state').html(data);
     $('#city').html('<option value="">Select City</option>');
    }
   });
  }
  else
  {
   $('#state').html('<option value="">Select State</option>');
   $('#city').html('<option value="">Select City</option>');
  }
 });

 $('#state').change(function(){
  var state_id = $('#state').val();
  if(state_id != '')
  {
   $.ajax({
    url:"<?php echo base_url(); ?>dynamic_dependent/fetch_city",
    method:"POST",
    data:{state_id:state_id},
    success:function(data)
    {
     $('#city').html(data);
    }
   });
  }
  else
  {
   $('#city').html('<option value="">Select City</option>');
  }
 });
 
});
</script>


Lastly, in this post we have make dynamic dependent dropdown in Codeigniter using country, state and city example. By using this type of example we can easily create relational dropdown whichever type of dynamic data.

1 comment:

  1. I paste same code but it is not working please help me

    ReplyDelete