Creating ajaxy Chained Selects with jQuery

Date: 30th May 2011 Author: admin 8 Comments
Posted in: AJAX, jQuery, MySQL, PHP |Tags: , , , , ,

practice of using chainedSelects

Creating ajaxy 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.

Plugin itself you always can download here. Ok, here are online demo and downloadable package:

Live Demo
download in package

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 params 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
download in package

Conclusion

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

Enjoyed this Post?

If you enjoyed this article, feel free to share our tutorial with your friends.
    Tweet
   
   

Stay connected with us:

5 Comments

    • تصميم مواقع's Gravatar
    • martin's Gravatar
    • 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?

    • allen's Gravatar
    • 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?

2 Trackbacks

  1. designfloat.com on May 30, 2011 at 4:49 pm
  2. Creating ajaxy Chained Selects with jQuery – Script Tutorials » WB Tips on May 30, 2011 at 5:49 pm
  3. Shared on FAQPAL.com on June 1, 2011 at 2:24 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

*

CAPTCHA Image
Refresh Image

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>