PHP jug: Ajax and Dynamic Multiple Combo Box Pre-Population if MySQL Data Exists

create the databse

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 14, 2008 at 06:39 PM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
--
-- Database: `db_ajax`
--

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

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

CREATE TABLE `city` (
`id` tinyint(4) NOT NULL auto_increment,
`city` varchar(50) default NULL,
`stateid` tinyint(4) default NULL,
`countryid` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

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

INSERT INTO `city` VALUES (1, 'Los Angales', 2, 1);
INSERT INTO `city` VALUES (2, 'New York', 1, 1);
INSERT INTO `city` VALUES (3, 'Toranto', 4, 2);
INSERT INTO `city` VALUES (4, 'Vancovour', 3, 2);

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

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

CREATE TABLE `country` (
`id` tinyint(4) NOT NULL auto_increment,
`country` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

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

INSERT INTO `country` VALUES (1, 'USA');
INSERT INTO `country` VALUES (2, 'Canada');

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

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

CREATE TABLE `state` (
`id` tinyint(4) NOT NULL auto_increment,
`countryid` tinyint(4) NOT NULL,
`statename` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

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

INSERT INTO `state` VALUES (1, 1, 'New York');
INSERT INTO `state` VALUES (2, 1, 'Los Angeles');
INSERT INTO `state` VALUES (3, 2, 'British Columbia');
INSERT INTO `state` VALUES (4, 2, 'Toranto');

first create the page like this

index.php

<html>
<head>
<title>Roshan's Triple Ajax dropdown code</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript" type="text/javascript">
// Roshan's Ajax dropdown code with php
// This notice must stay intact for legal use
// Copyright reserved to Roshan Bhattarai - nepaliboy007@yahoo.com
// If you have any problem contact me at http://roshanbh.com.np
function getXMLHTTP() { //fuction to return the xml http object
  var xmlhttp=false; 
  try{
   xmlhttp=new XMLHttpRequest();
  }
  catch(e) {  
   try{   
    xmlhttp= new ActiveXObject("Microsoft.XMLHTTP");
   }
   catch(e){
    try{
    xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
    }
    catch(e1){
     xmlhttp=false;
    }
   }
  }
    
  return xmlhttp;
    }
 
 function getState(countryId) {  
  
  var strURL="findState.php?country="+countryId;
  var req = getXMLHTTP();
  
  if (req) {
   
   req.onreadystatechange = function() {
    if (req.readyState == 4) {
     // only if "OK"
     if (req.status == 200) {      
      document.getElementById('statediv').innerHTML=req.responseText;      
     } else {
      alert("There was a problem while using XMLHTTP:\n" + req.statusText);
     }
    }    
   }   
   req.open("GET", strURL, true);
   req.send(null);
  }  
 }
 function getCity(countryId,stateId) {  
  var strURL="findCity.php?country="+countryId+"&state="+stateId;
  var req = getXMLHTTP();
  
  if (req) {
   
   req.onreadystatechange = function() {
    if (req.readyState == 4) {
     // only if "OK"
     if (req.status == 200) {      
      document.getElementById('citydiv').innerHTML=req.responseText;      
     } else {
      alert("There was a problem while using XMLHTTP:\n" + req.statusText);
     }
    }    
   }   
   req.open("GET", strURL, true);
   req.send(null);
  }
    
 }
</script>
</head>
<body>
<form method="post" action="" name="form1">
<table width="60%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="150">Country</td>
    <td  width="150"><select name="country" onChange="getState(this.value)">
 <option value="">----</option>
 <option value="1">USA</option>
 <option value="2">Canada</option>
        </select></td>
  </tr>
  <tr style="">
    <td>State</td>
    <td ><div id="statediv"><select name="state" >
 <option>----</option>
        </select></div></td>
  </tr>
  <tr style="">
    <td>City</td>
    <td ><div id="citydiv"><select name="city">
 <option>----</option>
        </select></div></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
</body>
</html>

create the page for getting the state value from the database (from state table)

findState.php

<? $country=intval($_GET['country']);
$link = mysql_connect($hostname_petmatch, $username_petmatch, $password_petmatch); //changet the configuration in required
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db($database_petmatch, $link);
$query="SELECT id,statename FROM state WHERE countryid='$country'";
$result=mysql_query($query);

?>
<select name="state" onchange="getCity(<?=$country?>,this.value)">
<option>----</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value=<?=$row['id']?>><?=$row['statename']?></option>
<? } ?>
</select>

findCity.php

<? $countryId=intval($_GET['country']);
$stateId=intval($_GET['state']);
$link = mysql_connect($hostname_petmatch, $username_petmatch, $password_petmatch); //changet the configuration in required
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db($database_petmatch, $link);
$query="SELECT id,city FROM city WHERE countryid='$countryId' AND stateid='$stateId'";
$result=mysql_query($query);

?>
<select name="city">
<option>----</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value><?=$row['city']?></option>
<? } ?>
</select>

 create the page for getting the city value from the database (from citytable)

 Note:- this code for only those who using windows server,

if you are using linux add php and use echo

for expamle-

for windows<?

 =$country?

for linux

<?php

 echo $country

?>

  refernce

http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_26226569.html

PHP jug

Thursday, 3 May 2012

Ajax and Dynamic Multiple Combo Box Pre-Population if MySQL Data Exists

No comments:

Post a Comment

About Me