The basic idea is: in the JS dynamic creation of the Select control's option, through Ajax to obtain in PHP from the SQL database from the provincial and municipal information, the code is a bit long, but many are similar, such as JS in the province, city, district access method similar, Different SELECT statements are executed in PHP with different parameters.
Index.html Code:
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en"
"Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
<title>php+ajax Get SQL Database </title>
<meta http-equiv=content-type content= "text/html; charset=gb2312 ">
<script src= "Scripts/thumbnails.js" type= "Text/javascript" ></script>
<body>
<div id= "description" >
<select style= "width:100px; "Onchange=" Sech (this.id) "id=" Sheng ">
<option value= "Province" > Please select Province </option>
</select>
<select onchange= "Sech (this.id)" id= "Shi" >
<option value= "City" > Please select Urban </option>
</select>
<select id= "Xian" >
<option value= "County" > Please select County Township </option>
</select>
</div>
</div>
</body>
thumbnails.js Code: window.onload = Getprovince; function createrequest () {//ajax for PHP interaction requires object try { request = new XMLHttpRequest ();//Create a new Request object; } catch (Tryms) { try { request = new ActiveXObject ("Msxml2.xmlhttp"); } catch (otherms) { try { request = new ActiveXObject ("Microsoft.XMLHTTP"); } catch (Failed) { request = null; } } } return request;} function Sech (ID) {////City change triggered, select's onchange event var AA = document.getElementById ( ID), if (id== "Sheng") { getcity (aa.value);//Aa.value for province id}if (id== "Shi") { Getcounty (Aa.value);//Here Aa.value is the city's id} } function GETPROvince () {//Get all provinces request = Createrequest (); if (request = = null) { Alert ("Unable to create request"); return; } var url= "getdetails.php? Id=0 ";//id=0 when passed to PHP to get all the provinces request.open (" Get ", url, true); request.onreadystatechange = Displayprovince; Set the callback function request.send (NULL); //Send request} function getcity (ID) {//Get province corresponding City request = Createrequest (); if (Request = = null) { alert ("Unable to create request"); return; } var url= "getdetails.php?id=" + Escape (ID) request.open ("GET", url, True); Request.onreadystatechange = Displaycity; request.send (null);} function Getcounty (ID) {//Get city-corresponding zone request = Createrequest (); if (request = = null) { & Nbsp; alert ("Unable to create request"); return; } var url= "getdetails.php?id=" + Escape (ID); request.open ("GET", URL, true); request.onreadystatechange = Displaycounty; request.send (null);} function displayprovince () {//The data obtained is dynamically increased to select if (request.readystate = = 4) { if (Request.status = =) { var a=new Array;var b=request.responsetext;//assigns data returned by PHP to B a =b.split (",");//Through "," Save this data in array a document.getelementbyid ("Sheng"). Length=1; var obj= document.getElementById ("Sheng"); for (I=0;i obj.options.add (New Option (a[i],i+1)); Dynamically generated option is added to select, the first argument is text, the second parameter is value . } }} function displaycity () {//The data obtained is dynamically increased to select if (request.readystate = = 4) { if ( Request.status = =) { var a=new Array;var b=request.responsetext; a=b.split(","); document.getelementbyid ("Shi"). length=1;//re-Selects document.getelementbyid ("Xian"). length=1;//re-Select if (document.getElementById ("Sheng"). value!= "province") { var obj= document.getElementById (' Shi '); for (I=0;i obj.options.add, New Option (A[i), document.getElementById ("Sheng"). value*100+i+1)); Ocument.getelementbyid ("Sheng"). value*100+i+1 corresponds to the city's ID. } } }} function DisplayCounty () {// Increase the acquired data to select if (request.readystate = = 4) { if (request.status = =) { var a=new Array;var b=request.responsetext; a=b.split (","); document.getelementbyid ("Xian"). Length=1;if (document.getElementById ("Sheng"). value!= "Province" &&document.getelementbyid ("Shi"). value!= "City") { var Obj=document.getelementbyid (' Xian '); for (i=0;i obj.optIons.add (New Option (a[i],i+1001)); } } }}
getdetails.php Code:
<?php
Header ("content-type:text/html; charset=gb2312 ");
$conn = new COM ("ADODB. Connection ") or Die (" Cannot start ADO ");
$connstr = "PROVIDER=SQLOLEDB; Persist Security Info=false; User Id=sa; Password=zzh;initial catalog=notebook;data Source=localhost ";
if ($_request[' ID ']==0) {//Get list of provinces
$conn->open ($CONNSTR); Establishing a database connection
$SQLSTR = "Select name from province"; Set query string
$rs = $conn->execute ($SQLSTR); Execute query to get results
$num _cols = $rs->fields->count (); Get the number of data set columns
$Province =array ();
$i = 0;
while (! $rs->eof) {
$Province [$i]= $rs->fields[' name ']->value. ",";
$rs->movenext ();
$i + +;
}
foreach ($Province as $val)
Echo $val;
$conn->close ();
$rs = null;
$conn = null;
}
if ($_request[' id ']>0&&$_request[' id ']<35) {//Get a list of municipalities corresponding to the province
$conn->open ($CONNSTR); Establishing a database connection
$SQLSTR = "Select name from city where cid=". $_request[' ID ']; Set query string
$rs = $conn->execute ($SQLSTR); Execute query to get results
$num _cols = $rs->fields->count (); Get the number of data set columns
$City =array ();
$i = 0;
while (! $rs->eof) {
$City [$i]= $rs->fields[' name ']->value. ",";
$rs->movenext ();
$i + +;
}
foreach ($City as $val)
Echo $val;
$conn->close ();
$rs = null;
$conn = null;
}
if ($_request[' ID ']>100) {//Get a list of counties corresponding to provinces and cities
$conn->open ($CONNSTR); Establishing a database connection
$SQLSTR = "Select name from county where cid=". $_request[' ID ']; Set query string
$rs = $conn->execute ($SQLSTR); Execute query to get results
$num _cols = $rs->fields->count (); Get the number of data set columns
$County =array ();
$i = 0;
while (! $rs->eof) {
$County [$i]= $rs->fields[' name ']->value. ",";
$rs->movenext ();
$i + +;
}
foreach ($County as $val)
Echo $val;
$conn->close ();
$rs = null;
$conn = null;
}
?>
Database design, Table province table, City table, County table. Requirements: Province table requires ID and NAME,ID recommendations from 1 to 34, such as Beijing ID 1, Guangdong ID 2, and so on; the city table needs id,name and cid,id for Cid*100+1,cid as the superior of the municipality, such as Shenzhen's superior to Guangdong province, CID is 2, Shenzhen ID is 201, and so on. County table needs id,name and CID, because it is a three-level relationship, the ID can be arbitrary, it is recommended to start from 10001 self-increment. CID is the superior, for example, the CID in Bao ' An district is 201, and the CID of Longgang District is 201;
:HTML effect: Finished effect: note: PHP is server-side, it is recommended to publish the site through the IP debugging.
