Thursday, 14 March 2013

Using ajax and php to change the value of one select box based on the value of the second selectbox

Hello Friends

Welcome to this next post.....


In this post we will be learning how to change the value of a select box based on the value of the other select box..

What does this means ...... ???

Suppose we ask user to select a Country ... based on the country we need to show the valid cities in the select box below it .... ...

So lets start with it ......

STEP 1 : creating the test database
  • Copy the sql import given below.
  • Create a database named "test"
  • Run this code as sql query .
This should create two tables namely country and cities in your database


 
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 14, 2013 at 05:20 PM
-- Server version: 5.5.24-log
-- PHP Version: 5.4.3
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
--
-- Database: `test`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `cities`
--
 
DROP TABLE IF EXISTS `cities`;
CREATE TABLE IF NOT EXISTS `cities` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `city_name` varchar(255) NOT NULL,
  PRIMARY KEY (`city_id`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
 
--
-- Dumping data for table `cities`
--
 
INSERT INTO `cities` (`city_id`, `country_id`, `city_name`) VALUES
(1, 1, 'city1Country1'),
(2, 1, 'city1Country1'),
(3, 2, 'city1country2'),
(4, 2, 'city2country2'),
(5, 2, 'city3country2'),
(6, 3, 'city1Country3');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `country`
--
 
DROP TABLE IF EXISTS `country`;
CREATE TABLE IF NOT EXISTS `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
--
-- Dumping data for table `country`
--
 
INSERT INTO `country` (`country_id`, `name`) VALUES
(1, 'country1'),
(2, 'country2'),
(3, 'country3');
 
--
-- Constraints for dumped tables
--
 
--
-- Constraints for table `cities`
--
ALTER TABLE `cities`
  ADD CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`);




STEP2 : Create html form with countries drop down populated from database .. 

This code gives you the basic idea how to populate the selectbox from the data in database.
Copy the code below save it as as php file(.php) and run.


<?php

#---- connect to database (connect)-----
$connect = mysql_connect('localhost','root','');
mysql_select_db('test',$connect);
#-----------(/end connect)---------


#--- select the countries from database-----

$query   = "SELECT * ".
                  "FROM ".
                  "country";

$resource       = mysql_query($query) or die(mysql_error());
$optionString   = ''; // this will contains the options to be populated in countries select box
while($row = mysql_fetch_assoc($resource))
{
   $optionString .= "<option value='{$row['country_id']}'>{$row['name']}</option>";   
}


#--------------------------------------------


?>

<html>
<head>
</head>
<body>
  <form type='post'>
<label for="country">Countries</label>
  <select name='country'>
  <?php echo $optionString;?>
  </select>
  </form>
</body>
<html>










STEP3: Create one select box for cities and populate it based on the country This will be your final code ... that you have to run

Now database is created and you have a basic idea how to populate the select box using data from database.
Its time to fulfill our actual motive.

We have two scripts with us  ..

                                       1.testForm.php


This is the main script...in which we have two select boxes ...

When we change the value in the country text box it will populate the Cities drop down which contains the cities that are there in the selected country.

To accomplish this we will be using jquery ajax.Ajax will be called  when the value in the value in the country dropdown changes.

The url for ajax will be the page that gives us the corresponding cities (script named as populateCities.php)


 <?php

#---- connect to database (connect)-----
$connect = mysql_connect('localhost','yourUser','yourUserPassword');
mysql_select_db('test',$connect);
#-----------(/end connect)---------


#--- select the countries from database-----

$query      = "SELECT * ".
                  "FROM ".
                  "country";

$resource       = mysql_query($query) or die(mysql_error());
$optionString   = ''; // this will contains the options to be populated in countries select box
while($row = mysql_fetch_assoc($resource))
{
   $optionString .= "<option value='{$row['country_id']}'>{$row['name']}</option>";   
}


#--------------------------------------------


?>

<html>
<head>

<script src='http://code.jquery.com/jquery-latest.min.js'></script>

<script type='text/javascript'>
$(document).ready(function(){


$("#country").change(function(){

 var data = $("#testFrm").serialize();
alert(data);
   $.ajax({

       url  : 'cityPopulate.php',
       type : 'POST',
       data : data,
       success:function(data){
                $('#cities').html(data)
               }
    })


   });

});

</script>


</head>
<body>
  <form type='post' id='testFrm'>
  <table>
  <tr>
   <td> Country</td>
   <td> <select  id ='country' name='country'>
<option value='0'>--- SELECT COUNTRY------</option>
  <?php echo $optionString;?>
  </select></td>
  </tr>
  <tr>
   <td>CITIES</td>
 <td>
    <select id='cities'> </select>
 </td>
  </tr>
  </table>
  </form>
</body>
<html>

                             2.populateCities.php


This is the magic script ...This script will get the country_id as a POST request from testForm.php and will fetch the corresponding cities .

  
<?php

#---- connect to database (connect)-----

$connect = mysql_connect('localhost','yourUser','yourUserPassword');

mysql_select_db('test',$connect);
#-----------(/end connect)---------

#-- get the post value posted using ajax--
$country_id = $_POST['country'];
#------------------------------------------


#--- select the countries from database-----

$query      = "SELECT * ".
                  "FROM cities WHERE country_id = $country_id";
                  

$resource       = mysql_query($query) or die(mysql_error());
$optionString   = ''; // this will contains the options to be populated in countries select box
while($row = mysql_fetch_assoc($resource))
{
   $optionString .= "<option value='{$row['city_id']}'>{$row['city_name']}</option>";    
}


#--------------------------------------------


// echo the result to be showm in city selectbox

echo $optionString;


?>


YOU ARE DONE....!!!!

How was it .... ? Hope you must have learned something new and interesting .....







Posted by at
Labels:

7 comments:

  1. Raymond3 September 2013 at 22:26

    Thanks I found this to be very helpful!

    ReplyDelete
    Replies
    1. PHP Blogger3 September 2013 at 22:35

      Thanks Raymond . Glad it helped you.

      Delete
  2. Unknown18 October 2013 at 00:14

    thank u i want single page php code

    ReplyDelete
  3. PHP blogger18 October 2013 at 00:17

    This comment has been removed by the author.

    ReplyDelete
  4. Unknown23 January 2014 at 12:21

    I put it together as you said... All I get is the alert. The drop down does not change. Any idea?

    ReplyDelete
  5. sirajcomp20 April 2014 at 23:18

    Its working ...Awesome!!!

    ReplyDelete

Subscribe to: Post Comments (Atom)