Populating triple drop down list from database using Ajax and PHP

[Mark_ED]

Hi,

 

I am a newbie in php and needs to populate triple drop down list.

 

Luckily I got the code but its not working.

 

Here's the code I am using, please let me know if I mistook somewhere.

 

Creating tables in database:

 

CREATE TABLE `country` (

  `id` tinyint(4) NOT NULL auto_increment,

  `country` varchar(20) NOT NULL default '',

PRIMARY KEY  (`id`)

) TYPE=MyISAM  ;

 

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  ;

 

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  ;

 

 

All created successfully.

 

 

the index.php file

 

<form method="post" name="form1">

<table border="0" cellpadding="0" cellspacing="0" width="60%"><tbody>

  <tr>

  <td width="150">Country</td>

  <td width="150"><select style="background-color: #ffffa0" name="country" onchange="getState(this.value)"><option>Select Country</option><option value="1">USA</option><option value="2">Canada</option>      </select></td>

  </tr>

<tr>

  <td>State</td>

  <td>

  <p id="statediv">

  <select style="background-color: #ffffa0" name="state"><option>Select Country First</option>      </select></td>

</tr>

<tr>

  <td>City</td>

  <td>

  <p id="citydiv">

  <select style="background-color: #ffffa0" name="city"><option>Select State First</option>      </select></td>

</tr>

</tbody></table>

</form>

 

getState() function of the javascript

 

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);

  }

}

The code of the PHP file findState.php.

 

<? $country=intval($_GET['country']);

$link = mysql_connect('localhost', 'root', ''); //I changed this to  my database, host, user and password.

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

mysql_select_db('db_ajax');

$query="SELECT id,statename FROM state WHERE countryid='$country'";

$result=mysql_query($query);

 

?>

<select name="state" onchange="getCity(<?=$country?>,this.value)">

<option>Select State</option>

  <? while($row=mysql_fetch_array($result)) { ?>

    <option value=<?=$row['id']?>><?=$row['statename']?></option>

  <? } ?>

</select>

 

getCity() function

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"  <--I don't understand this.<--

      {

        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);

  }

}

 

the code of findcity.php,

 

<?php $countryId=intval($_GET['country']);

$stateId=intval($_GET['state']);

$link = mysql_connect('localhost', 'root', ''); //I changed this to  my database, host, user and password.

if (!$link) {

    die('Could not connect: ' . mysql_error());

}

mysql_select_db('db_ajax');

$query="SELECT id,city FROM city WHERE countryid='$countryId' AND stateid='$stateId'";

$result=mysql_query($query);

 

?>

<select name="city">

<option>Select City</option>

  <?php while($row=mysql_fetch_array($result)) { ?>

<option value><?=$row['city']?></option>

<?php } ?>

</select>

 

 

It seems functions isn't working. I select the Country from the combo but it doesn't change the state.

 

 

I've done some manual entries in database according the table structure.

 

Any help would be highly appreciated.