How to implement province and city query in php

php method to implement province and city query: 1. Design a data table; 2. Find the corresponding parent_id according to the query id; 3. Through "function GetCityInfo(searstr, cityselect) {... }" and other codes can be used to query provinces and cities.

The operating environment of this article: Windows 7 system, PHP version 7.1, Dell G3 computer.

PHP realizes three-level linkage query of province, city, county (district)

# #Linkagequery

What is linkage query? In fact, this thing is very common in life, such as online shopping (almost everyone has used this). When shopping online, we need to fill in the order information. There is one in the information. Where the address needs to be filled in for an item, some websites use linkage query, which saves users the time to fill in the order. This is when linkage query comes in handy. For example, when we select: Hebei Province in the province drop-down box, the corresponding For urban areas, you need to change the information inside, load the corresponding urban areas into the drop-down box, and paste a picture

Database Design

Design a table. Then this table queries itself in a loop, and searches for the corresponding parent_id according to the queried id, and then you can query the desired results. The last two fields are redundant fields, which can be used or not, according to your project needs

The statement to create the database is as follows. The database is MySQL database

CREATE TABLE `china_area` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `code` varchar(10) NOT NULL,
  `name` varchar(191) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Simply insert some data. The complete table data is very large, about three thousand rows. I will not show all the data codes here to avoid being flashy. , if you want this data, you can search it online, or design it yourself. I also uploaded one, but it requires points.

INSERT INTO `china_area` (`id`, `parent_id`, `code`, `name`, `created_at`, `updated_at`) VALUES(1, 0, '110000', '北京市', NULL, NULL),(2, 0, '120000', '天津市', NULL, NULL),(3, 0, '130000', '河北省', NULL, NULL),(4, 0, '140000', '山西省', NULL, NULL),(5, 0, '150000', '内蒙古自治区', NULL, NULL),(6, 0, '210000', '辽宁省', NULL, NULL),(7, 0, '220000', '吉林省', NULL, NULL),(8, 0, '230000', '黑龙江省', NULL, NULL),(9, 0, '310000', '上海市', NULL, NULL),(10, 0, '320000', '江苏省', NULL, NULL),(11, 0, '330000', '浙江省', NULL, NULL),(12, 0, '340000', '安徽省', NULL, NULL),(13, 0, '350000', '福建省', NULL, NULL),(14, 0, '360000', '江西省', NULL, NULL),(15, 0, '370000', '山东省', NULL, NULL),(16, 0, '410000', '河南省', NULL, NULL),(17, 0, '420000', '湖北省', NULL, NULL),(18, 0, '430000', '湖南省', NULL, NULL),(19, 0, '440000', '广东省', NULL, NULL),(20, 0, '450000', '广西壮族自治区', NULL, NULL),(21, 0, '460000', '海南省', NULL, NULL),(22, 0, '500000', '重庆市', NULL, NULL),(23, 0, '510000', '四川省', NULL, NULL),(24, 0, '520000', '贵州省', NULL, NULL),(25, 0, '530000', '云南省', NULL, NULL),(26, 0, '540000', '西藏自治区', NULL, NULL),(27, 0, '610000', '陕西省', NULL, NULL),(28, 0, '620000', '甘肃省', NULL, NULL),(29, 0, '630000', '青海省', NULL, NULL),(30, 0, '640000', '宁夏回族自治区', NULL, NULL),(31, 0, '650000', '新疆维吾尔自治区', NULL, NULL),(32, 0, '710000', '台湾省', NULL, NULL),(33, 0, '810000', '香港特别行政区', NULL, NULL),(34, 0, '820000', '澳门特别行政区', NULL, NULL),(35, 1, '110100', '北京城区', NULL, NULL),(36, 35, '110101', '东城区', NULL, NULL),(37, 35, '110102', '西城区', NULL, NULL),(38, 35, '110105', '朝阳区', NULL, NULL),(39, 35, '110106', '丰台区', NULL, NULL),(40, 35, '110107', '石景山区', NULL, NULL),(41, 35, '110108', '海淀区', NULL, NULL),(42, 35, '110109', '门头沟区', NULL, NULL),(43, 35, '110111', '房山区', NULL, NULL),(44, 35, '110112', '通州区', NULL, NULL),(45, 35, '110113', '顺义区', NULL, NULL),(46, 35, '110114', '昌平区', NULL, NULL),(47, 35, '110115', '大兴区', NULL, NULL),(48, 35, '110116', '怀柔区', NULL, NULL),(49, 35, '110117', '平谷区', NULL, NULL),(50, 35, '110118', '密云区', NULL, NULL),(51, 35, '110119', '延庆区', NULL, NULL),(52, 2, '120100', '天津城区', NULL, NULL),(53, 52, '120101', '和平区', NULL, NULL),(54, 52, '120102', '河东区', NULL, NULL),(55, 52, '120103', '河西区', NULL, NULL),(56, 52, '120104', '南开区', NULL, NULL),(57, 52, '120105', '河北区', NULL, NULL),(58, 52, '120106', '红桥区', NULL, NULL),(59, 52, '120110', '东丽区', NULL, NULL),(60, 52, '120111', '西青区', NULL, NULL),(61, 52, '120112', '津南区', NULL, NULL),(62, 52, '120113', '北辰区', NULL, NULL),(63, 52, '120114', '武清区', NULL, NULL),(64, 52, '120115', '宝坻区', NULL, NULL),(65, 52, '120116', '滨海新区', NULL, NULL),(66, 52, '120117', '宁河区', NULL, NULL),(67, 52, '120118', '静海区', NULL, NULL),(68, 52, '120119', '蓟州区', NULL, NULL),(69, 3, '130100', '石家庄市', NULL, NULL),(70, 3, '130200', '唐山市', NULL, NULL),(71, 3, '130300', '秦皇岛市', NULL, NULL),(72, 3, '130400', '邯郸市', NULL, NULL),(73, 3, '130500', '邢台市', NULL, NULL),(74, 3, '130600', '保定市', NULL, NULL),(75, 3, '130700', '张家口市', NULL, NULL),(76, 3, '130800', '承德市', NULL, NULL),(77, 3, '130900', '沧州市', NULL, NULL),(78, 3, '131000', '廊坊市', NULL, NULL),(79, 3, '131100', '衡水市', NULL, NULL),(80, 69, '130102', '长安区', NULL, NULL),(81, 69, '130104', '桥西区', NULL, NULL),(82, 69, '130105', '新华区', NULL, NULL),(83, 69, '130107', '井陉矿区', NULL, NULL),(84, 69, '130108', '裕华区', NULL, NULL),(85, 69, '130109', '藁城区', NULL, NULL),(86, 69, '130110', '鹿泉区', NULL, NULL),(87, 69, '130111', '栾城区', NULL, NULL),(88, 69, '130121', '井陉县', NULL, NULL),(89, 69, '130123', '正定县', NULL, NULL),(90, 69, '130125', '行唐县', NULL, NULL),(91, 69, '130126', '灵寿县', NULL, NULL),(92, 69, '130127', '高邑县', NULL, NULL),(93, 69, '130128', '深泽县', NULL, NULL),(94, 69, '130129', '赞皇县', NULL, NULL),(95, 69, '130130', '无极县', NULL, NULL);

The database is roughly like this. Find the corresponding parent_id through the id of the upper level. Find the corresponding data by the value, search in a loop, three-level linkage search twice to find the corresponding result, without further ado, let’s start to explain the idea.

Design ideas

The first drop-down box (that is, the province) is loaded when the page is initialized, so there is no need to load asynchronously, the second (city) and the third ( Counties, districts) are loaded asynchronously, and then when the user operates the drop-down box, an Ajax request is sent through the js event to obtain the background code, and then the background is searched based on the value of the passed parent_id (try not to use fields in the database for page transmission). The database query code is as follows

SELECT * FROM `china_area` WHERE parent_id=".$_GET['precityid']

Then the results are spliced ​​into json and sent to the front desk, and then the results are spliced ​​using js.

Code Implementation

The first drop-down box can be generated directly when the page is loaded. The city and county only need to add the select tag without adding the corresponding data. Here By default, the value used for the unselected drop-down box is -1

<p>//引用了bootstrap框架和jq库，节省代码
    </p><p>
        <select>
            <option>请选择...</option>
            <?php             $conn = mysqli_connect("localhost", "root", "", "zbt");//省份下拉框，可以在页面刚加载的时候生成
            mysqli_query($conn, "set names utf8");
            $sqlstr = "SELECT * FROM `china_area` WHERE parent_id =0";
            $result = mysqli_query($conn, $sqlstr);
            while ($myrow = mysqli_fetch_array($result)) {?>
                <option>"><?php  echo $myrow[3] ?>
</option>
            <?php  } ?>
        </select>
    </p>
    <p>
        <select><!--市区下拉框-->
        </select>
    </p>
    <p>
        <select><!--县级下拉框-->
        </select>
    </p>

There are just so many HTML codes, and then there is the js code. Here I quoted the bootstrap framework and the jQuery library (I don’t know if it can be said to be a framework) ), jq has encapsulated many methods for us, just use them directly, don’t talk nonsense, just write the code

<script>
    $(function() {
        $(".province").change(function() { //省份下拉框事件
            var citysele = $(".city");//获取要改变的下一个下拉框
            GetCityInfo("precityid=" + $(this).val(), citysele);
        });

        $(".city").change(function() { //市区下拉框事件
            var citysele = $(".county");
            GetCityInfo("precityid=" + $(this).val(), citysele);
        });

        $(".county").change(function() { //弹出结果
            alert("您选择的城市是:" + $(".province option:selected").text() + " " + $(".city option:selected").text() + " " + $(".county option:selected").text())
        });

        function GetCityInfo(searstr, cityselect) {//两个参数，第一个是搜索字符串，第二个是相对应的下一个要变动的下拉框
            let sendtourl = encodeURI("conn.php?" + searstr);
            $.ajax({
                type: "GET",
                url: sendtourl,
                dataType: "json",
                success: function(data) {
                    cityselect.empty();
                    if (data.data.length <= 0) {
                        var option = "<option>暂无数据";//没有数据
                        cityselect.append(option);
                        return;
                    }
                    cityselect.append("<option value=&#39;-1&#39;>请选择...");//执行到这里就是有数据，将数据添加到相对应的下拉框
                    for (var i = 0; i < data.data.length; i++) {
                        var option = "<option value=" + data.data[i].id + ">" + data.data[i].name + "";
                        cityselect.append(option);
                    }
                },
                error: function(xhr) {
                    alert("error" + xhr.status);
                }
            });
        }

    })</script>

The code is a bit low, but it can be used, sometimes it may happen that the js code written is not there To give you a hint, the js code is best placed at the end of the page. Next, design the PHP code for backend query database and directly enter the code

<?php header(&#39;Content-Type:application/json&#39;);if (!isset(getallheaders()[&#39;Referer&#39;])) exit;//不是自己的网址不返回数据$conn=mysqli_connect("localhost","root","","zbt"); mysqli_query($conn,"set names utf8");if (isset($_GET[&#39;precityid&#39;])) {
	$sqlstr="SELECT * FROM `china_area` WHERE parent_id=".$_GET[&#39;precityid&#39;];//根据前台的数据查询相对应的表
	$result=mysqli_query($conn,$sqlstr);
	$datas=array();
	while($myrow=mysqli_fetch_array($result)){
		$rows=array();
		$rows["id"]=$myrow[0];
		$rows["parent_id"]=$myrow[1];
		$rows["code"]=$myrow[2];
		$rows[&#39;name&#39;]=$myrow[3];
		array_push($datas,$rows);
		$rows="";
	}//拼接结果
	$data=array();
	$data["code"]=200;//状态码
	$data["msg"]=getallheaders()[&#39;Referer&#39;];//自定义消息，我是为了看一下Referer是否正确，这里可以删除
	$data["data"]=$datas;
	echo json_encode($data,JSON_UNESCAPED_UNICODE);}?>

. That’s it. Put the project on the server and run it

Summary

The effect is okay. Of course, this is just for learning how to use it. The real application in the actual environment is definitely more complicated than this. If you think it is good, you can click three times in one click.

Recommended learning: "

PHP Video Tutorial"

The above is the detailed content of How to implement province and city query in php. For more information, please follow other related articles on the PHP Chinese website!