Home > Article > Backend Development > php+Mysql+Ajax+JS implements the example code of three-level linkage between provinces and municipalities
php+Mysql+Ajax+JS implements the example code of three-level linkage between provinces and municipalities
- 怪我咯Original
- 2017-07-10 14:00:511834browse
I recently worked on a project that required the use of three-level linkage between provinces and municipalities. I read a lot of information on the Internet, so I came up with the following ideas and code
The basic idea is: dynamically create a select control in JS option, obtain the province and city information obtained from the SQL database in PHP through Ajax. The code is a bit long, but many of them are similar. For example, the methods for obtaining provinces, cities, and districts in JS are similar. In PHP, different selections are executed through different parameters. statement.
index.html code:
The code is as follows:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>省市区三级联动</title> <META http-equiv=Content-Type content="text/html; charset=gb2312"> <script src="scripts/thumbnails.js" type="text/javascript"></script> </head> <body> <p id="description"> <select style="width:100px; " onchange="sech(this.id)" id="sheng"> <option value="province">请选择省份</option> </select> <select onchange="sech(this.id)" id="shi"> <option value="city">请选择市区</option> </select> <select id="xian"> <option value="county">请选择县乡</option> </select> </p> </p> </body> </html>
thumbnails.js code:
The code is as follows:
window.onload = getProvince;
function createRequest() {//Ajax于PHP交互需要对象
try {
request = new XMLHttpRequest();//创建一个新的请求对象;
} 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) {//省市改变时触发，select的onchange事件
var aa = document.getElementById(id);
if(id=="sheng"){
getCity(aa.value);//这里aa.value为省的id
}
if(id=="shi")
{
getCounty(aa.value);//这里aa.value为市的id
}
}
function getProvince() {//获取所有省
request = createRequest();
if (request == null) {
alert("Unable to create request");
return;
}
var url= "getDetails.php?ID=0";//ID=0时传递至PHP时让其获取所有省
request.open("GET", url, true);
request.onreadystatechange = displayProvince; //设置回调函数
request.send(null); //发送请求
}
function getCity(id){//获取省对应的市
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){//获取市对应的区
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() {//将获取的数据动态增加至select
if (request.readyState == 4) {
if (request.status == 200) {
var a=new Array;
var b=request.responseText;//将PHP返回的数据赋值给b
a=b.split(",");//通过","将这一数据保存在数组a中
document.getElementById("sheng").length=1;
var obj=document.getElementById("sheng');
for(i=0;i
obj.options.add(new Option(a[i],i+1)); //动态生成OPTION加到select中，第一个参数为Text,第二个参数为Value值.
}
}
}
function displayCity() {//将获取的数据动态增加至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;//重新选择
document.getElementById("xian").length=1;//重新选择
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对应的是市的ID。
}
}
}
}
function displayCounty() {//将获取的数据增加至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));
}
}
}
}getDetails. php code:
The code is as follows:
<?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=root;Password=123456;Initial Catalog=area;Data Source=localhost";
if($_REQUEST['ID']==0){//获得省列表
$conn->Open($connstr); //建立数据库连接
$sqlstr = "select name from Province"; //设置查询字符串
$rs = $conn->Execute($sqlstr); //执行查询获得结果
$num_cols = $rs->Fields->Count(); //得到数据集列数
$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){//获得省对应的市列表
$conn->Open($connstr); //建立数据库连接
$sqlstr = "select name from City where cid=".$_REQUEST['ID']; //设置查询字符串
$rs = $conn->Execute($sqlstr); //执行查询获得结果
$num_cols = $rs->Fields->Count(); //得到数据集列数
$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){//获得省市对应的县列表
$conn->Open($connstr); //建立数据库连接
$sqlstr = "select name from County where cid=".$_REQUEST['ID']; //设置查询字符串
$rs = $conn->Execute($sqlstr); //执行查询获得结果
$num_cols = $rs->Fields->Count(); //得到数据集列数
$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: The Province table requires id and name. The 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, and the 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, for example, the cid of Baoan District is 201, and the cid of Longgang District is also 201;
Screenshot:
HTML effect:
After completion:
The above is the detailed content of php+Mysql+Ajax+JS implements the example code of three-level linkage between provinces and municipalities. For more information, please follow other related articles on the PHP Chinese website!