ajax based nested drop down and searching

In this example, we present two uses of ajax. In first use, we created an ajax based nested drop downs which are also databased enabled. In second use, we create a keyword list suggestion in ajax (as we see in google search box).

For this example, we used the following database:

ajaxexample.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


--
-- Database: `labtask`
--

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

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

CREATE TABLE `city` (
  `ctid` int(11) NOT NULL,
  `city` varchar(50) NOT NULL,
  `country` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `city` (`ctid`, `city`, `country`) VALUES
(1, 'Abbottabad', 1),
(2, 'Peshawar', 1),
(3, 'New York', 2),
(4, 'Fargo', 2),
(7, 'Quebec', 3),
(8, 'Parachinar', 1);

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

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

CREATE TABLE `country` (
  `cid` int(11) NOT NULL,
  `countryname` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `country` (`cid`, `countryname`) VALUES
(3, 'Canada'),
(1, 'Pakistan'),
(2, 'USA');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `city`
--
ALTER TABLE `city`
  ADD PRIMARY KEY (`ctid`),
  ADD KEY `country` (`country`);

--
-- Indexes for table `country`
--
ALTER TABLE `country`
  ADD PRIMARY KEY (`cid`),
  ADD UNIQUE KEY `country` (`countryname`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `city`
--
ALTER TABLE `city`
  MODIFY `ctid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `country`
--
ALTER TABLE `country`
  MODIFY `cid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `city`
--
ALTER TABLE `city`
  ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country`) REFERENCES `country` (`cid`);

The first file creates a nested ajax / database enabled drop down:

default.php

<html>
<head>
<title></title>

<script type="text/javascript" src="ajax_javascript.js"></script>
</head>
<body>
<?php 
include("db/opendb.php");
include("include/functions.php"); ?>

<form id="frm" name="frm" method="post" action="">
<table border="1">
<tr>
  <td>Country</td>
  <td><select name="ddlcountry" id="ddlcountry" onChange="GetCities(this.value);">
  <option>-SELECT-</option>
  <?php echo DropDown("SELECT * FROM country"); ?>
  </select></td>
</tr>
<tr>
  <td>City</td>
  <td>
  <div id="div1">
  <select name="ddlcity" id="ddlcity">
  </select>
  </div>
  </td>
</tr>

<tr>
  <td colspan="2">
  <label id="label1"></label>
  </td>
</tr>

</table>

</form>
</body>
</html>

The second file creates an ajax enabled search box

ajaxsearch.php

<html>
<head>
<title></title>

<script type="text/javascript" src="ajax_javascript.js"></script>
<script>
function enterSelection(text)
{
document.getElementById("txtcity").value = text;
document.getElementById("table1").style.visibility="hidden";
}
</script>
</head>
<body>
<?php 
include("db/opendb.php");
include("include/functions.php"); ?>
 City
  <input type="text" name="txtcity" id="txtcity" onkeyup="GetCitiesList(this.value);" />

<div id="div2">
</div>


</body>
</html>

The third file is the javascript file for ajax

ajax_javascript.js

function GetCities(id)
{

xmlHttp=GetXmlHttpObject();


if (xmlHttp==null)
{
  alert ("Your browser does not support AJAX!");
  return;
} 

xmlHttp.open("POST", "ajax_file.php", true);

///////// Function getting values back from php file /////////////////////////
xmlHttp.onreadystatechange=function() 
{ 
  if (xmlHttp.readyState==4)
  {
    var response = xmlHttp.responseText;			
    
  document.getElementById("div1").innerHTML = response;

  }
} 


// Make our POST parameters string…
  var params =  "id=" + encodeURI(id); 

  // Set our POST header correctly…
  xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
  xmlHttp.setRequestHeader("Content-length", params.length);
  xmlHttp.setRequestHeader("Connection", "close");

  // Send the parms data…
  xmlHttp.send(params);  
}




function GetCitiesList(text)
{

xmlHttp=GetXmlHttpObject();


if (xmlHttp==null)
{
  alert ("Your browser does not support AJAX!");
  return;
} 

xmlHttp.open("POST", "ajax_file.php", true);

///////// Function getting values back from php file /////////////////////////
xmlHttp.onreadystatechange=function() 
{ 
  if (xmlHttp.readyState==4)
  {
    var response = xmlHttp.responseText;			
    
  document.getElementById("div2").innerHTML = response;

  }
} 


// Make our POST parameters string…
  var params =  "text=" + encodeURI(text); 

  // Set our POST header correctly…
  xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
  xmlHttp.setRequestHeader("Content-length", params.length);
  xmlHttp.setRequestHeader("Connection", "close");

  // Send the parms data…
  xmlHttp.send(params);  

}






///////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////// Return the appropriate Ajax object  //////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////////////////
function GetXmlHttpObject()
{
var xmlHttp=null;
try
  {
  // Firefox, Opera 8.0+, Safari
  xmlHttp=new XMLHttpRequest();
  }
catch (e)
  {
  // Internet Explorer
  try
    {
    xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
    }
  catch (e)
    {
    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
    }
  }
return xmlHttp;
}
//=======================================================================================

Finally the fourth file is the backend php file for ajax

ajax_file.php

<?php
include("db/opendb.php");
include("include/functions.php");

if(isset($_POST['id']))
{
$id = $_POST['id'];	
$query = "select * from city where country=".$id;

$str = "<select name=\"ddlcity\" id=\"ddlcity\">" . DropDown($query) . "</select>";

echo $str;

}

if(isset($_POST['text']))
{
$text = $_POST['text'];	

//if(strlen($text)<3)
//return;
$query = "select * from city where lower(city) like '". strtolower($text) ."%'";

//$str = "<select name=\"ddlcity\" id=\"ddlcity\" multiple=\"multiple\">" . DropDown($query) . "</select>";

$result = $conn -> query($query);
$str = "<table id='table1' border='1'>";

  foreach($result as $row)
  {
    $str = $str . "<tr><td onClick='enterSelection(this.innerHTML);'>$row[1]</td></tr>";
  }

$str = $str . "</table>";	
  
  echo $str;

}



?>

Finally, here is the code for functions.php file which is placed in a folder named “include”

<?php
function DropDown($query, $selected="")
{
  global $conn;	
  $result = $conn -> query($query);
  $ddl = "";
  foreach($result as $row)
  {
    if($row[0] == $selected)
    $ddl = $ddl. "<option value=\"$row[0]\">$row[1]</option>";	
    else
    $ddl = $ddl. "<option value=\"$row[0]\" select=\"selected\">$row[1]</option>";			
  }

$conn = NULL;

return $ddl;
}


?>

The source code of complete program can be downloaded from here

 

Tags:,

Add a Comment

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