Home >Backend Development >PHP Tutorial >PHP+Mysql+Ajax+JS realizes three-level linkage of provinces and municipalities_PHP tutorial
PHP+Mysql+Ajax+JS realizes three-level linkage of provinces and municipalities_PHP tutorial
- WBOYOriginal
- 2016-07-13 10:29:33952browse
The basic idea is: dynamically create the option of the select control in JS, and obtain the province and city information obtained from the SQL database in PHP through Ajax. The code is a bit long, but many are similar, such as province, city, and district in JS The acquisition method is similar. In PHP, different select statements are executed through different parameters.
index.html code:
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
thumbnails.js code:
window.onload = getProvince;
function createRequest() {//Ajax requires objects for PHP interaction
try {
request = new XMLHttpRequest();//Create one 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) {//Triggered when the province and city change, the onchange event of select
var aa = document.getElementById(id);
if(id==" sheng"){
getCity(aa.value);//here aa.value is the province’s 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";/ When /ID=0 is passed to PHP, let it get all provinces
request.open("GET", url, true);
request.onreadystatechange = displayProvince; //Set the callback function
request.send (null); //Send a request
}
function getCity(id){//Get the city corresponding to the province
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 the city corresponding Area
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 = displayCounty;
request.send(null);
}
function displayProvince() {//Dynamicly add the obtained data to select
if (request.readyState == 4) {
if (request.status == 200 ) {
var a=new Array;
var b=request.responseText;//Assign the data returned by PHP to b
a=b.split(",");//Pass ", "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)); //Dynamicly generate OPTION and add it to select. The first parameter is Text and the second parameter is Value.
}
}
}
function displayCity() {//Dynamicly add the acquired data to select
if (request.readyState == 4) {
if (request.status == 200) {
var a=new Array;
var b=request.responseText;
a=b.split(",");
document.getElementById("shi").length=1;//Reselect
document.getElementById("xian").length=1;//Reselect
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 ID.
}
}
}
}
function displayCounty() {//Add the obtained data to select
if (request.readyState == 4 ) {
if (request.status == 200) {
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));
}
}
}
}
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=root;Password=123456;Initial Catalog=area;Data Source=localhost";
if($_REQUEST['ID']==0){//Get the list of provinces
$conn->Open($connstr); //Establish a database connection
$sqlstr = "select name from Province"; //Set the query string
$rs = $conn->Execute($sqlstr); //Execute the query to get the results
$num_cols = $rs->Fields->Count( ); //Get the number of columns in the data set
$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 the list of cities corresponding to the province
$conn->Open($connstr); / /Establish database connection
$sqlstr = "select name from City where cid=".$_REQUEST['ID']; //Set query string
$rs = $conn->Execute($sqlstr) ; //Execute the query to get the results
$num_cols = $rs->Fields->Count(); //Get the number of columns in the data set
$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 the county list corresponding to the province and city
$conn->Open($connstr); //Establish a database connection
$sqlstr = "select name from County where cid=".$_REQUEST['ID']; //Set the query string
$rs = $conn->Execute($sqlstr); //Execute the query to get the results
$num_cols = $rs->Fields->Count(); //Get the number of columns in the data set
$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, tables Province table, City table, County table.
Requirements: Province table requires id and name, id is recommended to be from 1 to 34, for example, Beijing id is 1, Guangdong id is 2, and so on;
City table requires id, name and cid, id is cid* 100+1, cid is the superior of the city. For example, the superior of Shenzhen is Guangdong Province. If the cid is 2, the id of Shenzhen is 201, and so on.
The County table requires id, name and cid. Because it is a three-level relationship, the id can be arbitrary. It is recommended to increase it from 10001. The cid is the superior level. For example, the cid of Baoan District is 201, and the cid of Longgang District is also 201;
Screenshot:
HTML effect:
After completion:
Note: PHP is server-side. It is recommended to debug through IP after publishing the website.