Dynamic Drop down menu in php-mysql-ajax
here as you select country, respective states is filled with state combo box without refreshing the page,
similarly as you select state, respective cities is filled with city combo box without refreshing the page,
Do as per following
1) Create database and table
2) Make your base file(main file)...here index.php
3) create getState.php ..note this is file is excuted when drop down menu changed (for Country)
4) create getCity.php ..note this is file is excuted when drop down menu changed (for state)
Do step as follows
1)
CREATE TABLE `city` (
`city_id` varchar(10) NOT NULL default '',
`state_id` varchar(6) NOT NULL default '',
`city_name` varchar(50) NOT NULL default '',
PRIMARY KEY (`city_id`),
KEY `state_id` (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `city` VALUES ('1', '1', 'Mumbai');
INSERT INTO `city` VALUES ('2', '1', 'Nagpur');
INSERT INTO `city` VALUES ('3', '2', 'Banglore');
INSERT INTO `city` VALUES ('4', '2', 'Manglore');
CREATE TABLE `country` (
`Country_Id` char(3) NOT NULL default '',
`name` varchar(60) NOT NULL default '',
`continent_code` varchar(30) NOT NULL default '',
`tel_code` varchar(5) NOT NULL default '',
PRIMARY KEY (`Country_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `country` VALUES ('1', 'India', '91', '985');
INSERT INTO `country` VALUES ('2', 'Sri Lanka', '34', '454');
CREATE TABLE `state` (
`state_id` varchar(6) NOT NULL default '',
`country_id` char(3) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
PRIMARY KEY (`state_id`),
KEY `Country_Id` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `state` VALUES ('1', '1', 'Maharashtra');
INSERT INTO `state` VALUES ('2', '1', 'Karnatak');
--
-- Constraints for table `city`
--
ALTER TABLE `city`
ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`state_id`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `state`
--
ALTER TABLE `state`
ADD CONSTRAINT `state_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`Country_Id`) ON DELETE CASCADE ON UPDATE CASCADE;
2) Your index file or your actual base file (where u want all your drop down combo box)
<?php
$link = mysql_connect("localhost","root","");
$dbSelected = mysql_select_db('globe');
if($_POST)
{
echo '<pre>';
print_r($_POST);
echo '<pre>';
exit;
}
?>
<script src="shCore.js" type="text/javascript"></script>
<script src="shAutoloader.js" type="text/javascript"></script>
<script type="text/javascript">
SyntaxHighlighter.autoloader(
'js jscript javascript /js/shBrushJScript.js',
'applescript /js/shBrushAppleScript.js'
);
SyntaxHighlighter.all();
</script>
<script>
var xmlhttp;
function ajaxFunction(url,myReadyStateFunc)
{
if (window.XMLHttpRequest)
{
// For IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{
// For IE5, IE6
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange= myReadyStateFunc; // myReadyStateFunc = function
xmlhttp.open("GET",url,true);
xmlhttp.send();
}
function getState(x)
{
// in second argument of ajaxFunction we are passing whole function (onreadystatechange function).
// Goto getState.php code
ajaxFunction('getState.php?cid='+x, function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
alert("vishal"+xmlhttp.responseText);
var s = xmlhttp.responseText; // s = "1,2,3,|state1,state2,state3,"
s=s.split("|"); // s = ["1,2,3,", "state1,state2,state3,"]
sid = s[0].split(","); // sid = [1,2,3,]
sval = s[1].split(","); // sval = [state1, state2, state3,]
st = document.getElementById('state')
st.length=0
for(i=0;i<sid.length-1;i++)
{
st[i] = new Option(sval[i],sid[i])
}
getCity(-1) // emptying the city.
}
});
}
function getCity(x)
{
// in second argument of ajaxFunction we are passing whole function.
// Goto getCity.php code
ajaxFunction("getCity.php?sid="+x, function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
var c = xmlhttp.responseText;
c=c.split("|");
cid = c[0].split(",");
cval = c[1].split(",");
ct = document.getElementById('city')
ct.length=0
for(i=0;i<cid.length-1;i++)
{
ct[i] = new Option(cval[i],cid[i])
}
}
});
}
</script>
<style>
select{width:270px;size:10}
</style>
<table>
<form name="myForm" method="post" action="index.php">
<tr>
<td>Country </td>
<td>: <select name="country" id="country" onchange="getState(this.value)">
<option value="-1">Select Country</option>
<?php
$cntry = mysql_query("SELECT `Country_Id`, `name` FROM country ORDER BY `name` ASC");
while($row = mysql_fetch_assoc($cntry))
{
echo '<option value="'.$row['Country_Id'].'">'.$row['name'].'</option>';
}
?>
</select>
</td>
</tr>
<tr>
<td>State </td>
<td>: <select name="state" id="state" onchange="getCity(this.value)">
<option value="-1">Select State</option>
</select>
</td>
</tr>
<tr>
<td>City </td>
<td>: <select name="city" id="city" >
<option value="-1">Select City</option>
</select>
<input type="submit" value="submit">
</td>
</tr></form>
</table>
3) create getState.php file and copy this code and save it...
$link = mysql_connect("localhost","root","");
$dbSelected = mysql_select_db('globe');
$id="-1,";
$sname="Select State,";
$cid = $_GET['cid'];
if($cid=="-1")
{
$response = $id."|".$sname;
echo $response;
}
else
{
$result = mysql_query("SELECT `state_id`, `name` FROM `state` WHERE `country_id` = '$cid' ORDER BY `name` ASC");
while($row = mysql_fetch_assoc($result))
{
$id .=$row['state_id'].",";
$sname .=$row['name'].",";
}
$response = $id."|".$sname;
echo $response;
}
?>
4) create getCity.php file and copy this code and save it...
$link = mysql_connect("localhost","root","");
$dbSelected = mysql_select_db('globe');
$id="-1,";
$cname="Select City,";
$sid = $_GET['sid'];
if($sid=="-1")
{
$response = $id."|".$cname;
echo $response;
}
else
{
$result = mysql_query("SELECT `city_id`, `city_name` FROM `city` WHERE `state_id` = ".$sid." ORDER BY `city_name` ASC");
while($row = mysql_fetch_assoc($result))
{
$id .=$row['city_id'].",";
$cname .=$row['city_name'].",";
}
$response = $id."|".$cname;
echo $response;
}
?>
No comments:
Post a Comment