Creating ajaxy Chained Selects with jQuery

Creating ajaxy Chained Selects with jQuery

14 108805
practice of using chainedSelects

AJAX chained selects with jQuery. Today we will talk about creating dependent (chained) selects for your projects. This can be any related elements at your website. As example dependent fields to select your profession, mobile phone, or location (which can include Country, State, Region etc). One important note – we will need all this working using AJAX technology (to make functionality fast and smooth). And, jQuery library provide us with that plugin – jquery.chainedSelects.js. I will show you how to create chained fields using this plugin.

Live Demo

[sociallocker]

download in package

[/sociallocker]


Ok, download the example files and lets start coding !


Step 1. SQL

We will keep our data in database (tables with countries, states and regions). I don`t will show you FULL sql here (its pretty big), but I will show you structures and few records per table. Full SQL will available in package (check for sql.sql file inside package). As you can see – I selected prefix ‘s85’ for tables – this is number of our current tutorial. You always can use your own table names if you wish.


-- countries
CREATE TABLE `s85_countries` (
  `country_code` varchar(2) NOT NULL,
  `country_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`country_code`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
INSERT INTO `s85_countries` (`country_code`, `country_name`) VALUES
('AR', 'Argentina'),
('AU', 'Australia'),
('BR', 'Brazil');
-- states
CREATE TABLE `s85_states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` varchar(3) NOT NULL,
  `state_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
INSERT INTO `s85_states` (`id`, `country_code`, `state_name`) VALUES
(1, 'US', 'alabama'),
(2, 'US', 'alaska'),
(3, 'US', 'arizona');
-- regions
CREATE TABLE `s85_regions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `region_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
INSERT INTO `s85_regions` (`id`, `state_id`, `region_name`) VALUES
(1, 1, 'auburn'),
(2, 1, 'birmingham'),
(3, 1, 'columbus, GA');

Step 2. PHP

index.php

This is our main PHP file, I using this file to generate our FORM with necessary selects for country, state and region fields. And, this file also contain code for ajax calls of states (of selected country) and regions (of selected state).

<?
require_once('classes/CMySQL.php');
if (version_compare(phpversion(), "5.3.0", ">=")  == 1)
  error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
else
  error_reporting(E_ALL & ~E_NOTICE);
// return states of country
if ($_GET['action'] == 'get_states') {
    $sCountryName = $GLOBALS['MySQL']->escape($_GET['_value']);
    $aRet[] = array(0 => 'any');
    $aStates = $GLOBALS['MySQL']->getPairs("SELECT * FROM `s85_states` WHERE `country_code`='{$sCountryName}'", 'id', 'state_name');
    foreach ($aStates as $iStateId => $sStateName) {
        $aRet[] = array($iStateId => $sStateName);
    }
    echo json_encode($aRet);
    exit;
}
if ($_GET['action'] == 'get_regions') {
    $iStateID = (int)$_GET['_value'];
    $aRet[] = array(0 => 'any');
    $aRegions = $GLOBALS['MySQL']->getPairs("SELECT * FROM `s85_regions` WHERE `state_id`='{$iStateID}'", 'id', 'region_name');
    foreach ($aRegions as $iRegionId => $sRegionName) {
        $aRet[] = array($iRegionId => $sRegionName);
    }
    echo json_encode($aRet);
    exit;
}
$sCountryOptions = '';
$aCountries = $GLOBALS['MySQL']->getPairs("SELECT * FROM `s85_countries`", 'country_code', 'country_name');
foreach ($aCountries as $sCode => $sName) {
    $sCountryOptions .= '<option value="'.$sCode.'">'.$sName.'</option>';
}
$sCountries = '<select name="country" id="country">'.$sCountryOptions.'</select>';
echo <<<EOF
<link href="css/style.css" rel="stylesheet" type="text/css" />
<script src="js/jquery.js" type="text/javascript"></script>
<script src="js/jquery.chainedSelects.js" type="text/javascript"></script>
<script src="js/main.js" type="text/javascript"></script>
<div class="example">
    <form action="" method="post">
        <div><label for="country">Country</label>{$sCountries}</div>
        <div><label for="state">State</label>
            <select name="state" id="state""><option value="0">any</option></select>
        </div>
        <div><label for="region">Region</label>
            <select name="region" id="region""><option value="0">any</option></select>
        </div>
        <div><input name="submit" type="submit" value="Send this info" /></div>
    </form>
</div>
EOF;
if ($_POST) {
    // draw debug information
    echo '<pre>';
    print_r($_POST);
    echo '</pre>';
}
?>

Make attention, I using my own class for work with database – ‘classes/CMySQL.php’. This file already available in package. Commonly – you can use same, or similar class for your own projects. This just give some comfort with working with database. One thing what you will need to do – configure database connection parameters inside that file (in top of its code).

Step 3. JS

js/jquery.js and js/jquery.chainedSelects.js

This is just ordinary jQuery library with our new plugin – chainedSelects. Both files available in package.

js/main.js

$(function() {
  $('select[name=country]').chainSelect('#state','index.php?action=get_states', {
    before:function (target) {
      $(target).css('visibility','hidden');
    },
    after:function (target) {
      $(target).css('visibility','visible');
    }
  });
  $('#state').chainSelect('#region','index.php?action=get_regions', {
    before:function (target) {
      $(target).css('visibility','hidden');
    },
    after:function (target) {
      $(target).css('visibility','visible');
    }
  });
});

Syntax of chainedSelects using easy, we can point which URL will called when we changing selects, also we can perform some extra actions before request, and after.

Step 4. CSS

css/style.css

body {
    background:#eee;
    margin:0;
    padding:0;
}
.example {
    background:#FFF;
    width:400px;
    border:1px #000 solid;
    margin:20px auto;
    padding:15px;
    -moz-border-radius:3px;
    -webkit-border-radius:3px;
}
.example form {
    background:#EEE;
    border:1px #DDD solid;
    padding:10px;
    -moz-border-radius:3px;
    -webkit-border-radius:3px;
}
.example form div {
    margin:10px;
}
.example form div label {
    display:block;
    width:100px;
    float:left;
}
.example form div select {
    width:200px;
}

Live Demo

Conclusion

In result, now we have pretty nice dependent selectors where we can choose our location. Happy coding. Good luck in your projects!

SIMILAR ARTICLES

Design Patterns in PHP

0 104190

14 COMMENTS

  1. yes, great tool, i included it in my page but the same error occurs like on the demo page (live demo) above. the second select box never appears. what could be the reason?

    • Hello martin,
      thanks for your report, my problem was related with json_encode function (I don’t have json at current server), possible your problems the same?

  2. Hi, I’m new to coding, and I would really like to get this to work for a form I’m creating. I’m have issues with the second drop down. You said it might have to deal with the json_encode function. How do i fix that?

    • Hello Allen,
      Yes, I mentioned json_encode. Basically, JSON is PHP library, and as of PHP 5.2.0, the JSON extension is bundled and compiled into PHP by default. But if your PHP version is below 5.2.0, you can install that JSON separately. As I know, JSON is a part of PECL. Try to read about installation of PECL extensions.

  3. Hello,
    cannot select STATE for default COUNTRY. I think that is necessary to fulfill array of states with default country.

    • Hi Prince,
      And, what’s a problem? You always can keep add your info in a single SQL table. Also you can have multiple chained fields (selectors). Once you have selected some value from one level – you should perform a necessary search to find suitable values for linked field.

  4. In your live demo, first “Country” is “Argentina” without states in “State”. Try to add states for Argentina. I added states to Argentina, but they don’t showed under “State” on first access to the page. After that, if I select other country and get back to Argentina I can select states under “State”. Try to add states for Argentina in your live demo and see what happens. Maybe I’m wrong.

    • Hi Sasa,
      In this case you can easily add a trigger to ‘change’ event for our first element:
      $(‘select[name=country]’).trigger(“change”);

  5. Hey, great code!! after a good week or more of searching, code that finally works as advertised… I also had problems with the second dropdown working when I realized something. I was copy and pasting the database table info query from the page, and not using the included sql.sql file. As stated at the top, it is limited info. Use the sql.sql file to get larger and more functional tables. Before I did that, it would fill in the first dropdown, but none of the others. well of course. No data in the tables. That solved my problems. Again, thanks a lot, you have helped me over a very large hurdle in my coding.

    • Hi Ian,
      Basically, you can combine all these three tables into the single table. This shouldn’t be a problem. If you use one table (of your specific data), then you just need to prepare certain SQL queries to get the necessary data from your table.

Leave a Reply to prince Cancel reply