全国省市区乡村五级联动

发表于 | 分类于
三级联动SQL(包含港澳台), 密码：k6l9
港澳台JSON数据
1
{"taiwan":{"province":"台湾","provinceID":"710000","citys":[{"areas":[{"area":"阿莲区","areaID":""},{"area":"大寮区","areaID":""},{"area":"大社区","areaID":""},{"area":"大树区","areaID":""},{"area":"凤山区","areaID":""},{"area":"冈山区","areaID":""},{"area":"鼓山区","areaID":"710205"},{"area":"湖内区","areaID":""},{"area":"甲仙区","areaID":""},{"area":"林园区","areaID":""},{"area":"芩雅区","areaID":"710203"},{"area":"六龟区","areaID":""},{"area":"路竹区","areaID":""},{"area":"茂林区","areaID":""},{"area":"美浓区","areaID":""},{"area":"弥陀区","areaID":""},{"area":"楠梓区","areaID":"710210"},{"area":"那玛厦区","areaID":""},{"area":"内门区","areaID":""},{"area":"鸟松区","areaID":""},{"area":"旗津区","areaID":"710206"},{"area":"旗山区","areaID":""},{"area":"其它区","areaID":"710212"},{"area":"前金区","areaID":"710202"},{"area":"前镇区","areaID":"710207"},{"area":"桥头区","areaID":""},{"area":"茄萣区","areaID":""},{"area":"仁武区","areaID":""},{"area":"三民区","areaID":"710208"},{"area":"杉林区","areaID":""},{"area":"桃源区","areaID":""},{"area":"田寮区","areaID":""},{"area":"小港区","areaID":"710211"},{"area":"新兴区","areaID":"710201"},{"area":"燕巢区","areaID":""},{"area":"盐埕区","areaID":"710204"},{"area":"永安区","areaID":""},{"area":"梓官区","areaID":""},{"area":"左营区","areaID":"710209"}],"city":"高雄市","cityID":"710200"},{"areas":[{"area":"丰滨乡","areaID":""},{"area":"凤林镇","areaID":""},{"area":"富里乡","areaID":""},{"area":"光复乡","areaID":""},{"area":"花莲市","areaID":""},{"area":"吉安乡","areaID":""},{"area":"瑞穗乡","areaID":""},{"area":"寿丰乡","areaID":""},{"area":"太鲁阁","areaID":""},{"area":"万荣乡","areaID":""},{"area":"新城乡","areaID":""},{"area":"秀林乡","areaID":""},{"area":"玉里镇","areaID":""},{"area":"卓溪乡","areaID":""}],"city":"花莲县","cityID":"712600"},{"areas":[{"area":"安乐区","areaID":"710705"},{"area":"暖暖区","areaID":"710706"},{"area":"七堵区","areaID":"710707"},{"area":"其它区","areaID":"710708"},{"area":"仁爱区","areaID":"710701"},{"area":"信义区","areaID":"710702"},{"area":"中山区","areaID":"710704"},{"area":"中正区","areaID":"710703"}],"city":"基隆市","cityID":"710700"},{"areas":[{"area":"东区","areaID":"710901"},{"area":"其它区","areaID":"710903"},{"area":"西区","areaID":"710902"}],"city":"嘉义市","cityID":"710900"},{"areas":[{"area":"阿里山乡","areaID":""},{"area":"布袋镇","areaID":""},{"area":"大林镇","areaID":""},{"area":"大埔乡","areaID":""},{"area":"东石乡","areaID":""},{"area":"番路乡","areaID":""},{"area":"六脚乡","areaID":""},{"area":"鹿草乡","areaID":""},{"area":"梅山乡","areaID":""},{"area":"民雄乡","areaID":""},{"area":"朴子乡","areaID":""},{"area":"水上乡","areaID":""},{"area":"太保市","areaID":""},{"area":"溪口乡","areaID":""},{"area":"新港乡","areaID":""},{"area":"义竹乡","areaID":""},{"area":"中埔乡","areaID":""},{"area":"竹崎乡","areaID":""}],"city":"嘉义县","cityID":"711900"},{"areas":[{"area":"金城镇","areaID":""},{"area":"金湖镇","areaID":""},{"area":"金宁乡","areaID":""},{"area":"金沙镇","areaID":""},{"area":"烈屿乡","areaID":""},{"area":"乌坵乡","areaID":""}],"city":"金门县","cityID":"710500"},{"areas":[{"area":"北竿乡","areaID":""},{"area":"东引乡","areaID":""},{"area":"莒光乡","areaID":""},{"area":"南竿乡","areaID":""}],"city":"连江县","cityID":""},{"areas":[{"area":"大湖乡","areaID":""},{"area":"公馆乡","areaID":""},{"area":"后龙镇","areaID":""},{"area":"苗栗市","areaID":""},{"area":"南庄乡","areaID":""},{"area":"三湾乡","areaID":""},{"area":"三义乡","areaID":""},{"area":"狮潭乡","areaID":""},{"area":"泰安乡","areaID":""},{"area":"铜锣乡","areaID":""},{"area":"通霄镇","areaID":""},{"area":"头份镇","areaID":""},{"area":"头屋乡","areaID":""},{"area":"西湖乡","areaID":""},{"area":"苑里镇","areaID":""},{"area":"造桥乡","areaID":""},{"area":"竹南镇","areaID":""},{"area":"卓兰镇","areaID":""}],"city":"苗栗县","cityID":"711500"},{"areas":[{"area":"草屯镇","areaID":""},{"area":"国姓乡","areaID":""},{"area":"集集镇","areaID":""},{"area":"鹿谷乡","areaID":""},{"area":"名间乡","areaID":""},{"area":"南投市","areaID":""},{"area":"埔里镇","areaID":""},{"area":"仁爱乡","areaID":""},{"area":"水里乡","areaID":""},{"area":"信义乡","areaID":""},{"area":"鱼池乡","areaID":""},{"area":"中寮乡","areaID":""},{"area":"竹山镇","areaID":""}],"city":"南投县","cityID":"710600"},{"areas":[{"area":"白沙乡","areaID":""},{"area":"湖西乡","areaID":""},{"area":"马公市","areaID":""},{"area":"七美乡","areaID":""},{"area":"望安乡","areaID":""},{"area":"西屿乡","areaID":""}],"city":"澎湖县","cityID":"712700"},{"areas":[{"area":"长治乡","areaID":""},{"area":"潮州镇","areaID":""},{"area":"车城乡","areaID":""},{"area":"春日乡","areaID":""},{"area":"东港镇","areaID":""},{"area":"枋寮乡","areaID":""},{"area":"枋山乡","areaID":""},{"area":"高树乡","areaID":""},{"area":"桓春镇","areaID":""},{"area":"佳冬乡","areaID":""},{"area":"九如乡","areaID":""},{"area":"崁顶乡","areaID":""},{"area":"来义乡","areaID":""},{"area":"里港乡","areaID":""},{"area":"林边乡","areaID":""},{"area":"麟洛乡","areaID":""},{"area":"琉球乡","areaID":""},{"area":"玛家乡","areaID":""},{"area":"满州乡","areaID":""},{"area":"牡丹乡","areaID":""},{"area":"南州乡","areaID":""},{"area":"内埔乡","areaID":""},{"area":"屏东市","areaID":""},{"area":"三地门乡","areaID":""},{"area":"狮子乡","areaID":""},{"area":"泰武乡","areaID":""},{"area":"万丹乡","areaID":""},{"area":"万峦乡","areaID":""},{"area":"雾台乡","areaID":""},{"area":"新埤乡","areaID":""},{"area":"新园乡","areaID":""},{"area":"盐埔乡","areaID":""},{"area":"竹田乡","areaID":""}],"city":"屏东县","cityID":"712400"},{"areas":[{"area":"北投区","areaID":"710109"},{"area":"大安区","areaID":"710105"},{"area":"大同区","areaID":"710102"},{"area":"南港区","areaID":"710111"},{"area":"内湖区","areaID":"710110"},{"area":"其它区","areaID":"710113"},{"area":"士林区","areaID":"710108"},{"area":"松山区","areaID":"710104"},{"area":"万华区","areaID":"710106"},{"area":"文山区","areaID":"710112"},{"area":"信义区","areaID":"710107"},{"area":"中山区","areaID":"710103"},{"area":"中正区","areaID":"710101"}],"city":"台北市","cityID":"710100"},{"areas":[{"area":"卑南乡","areaID":""},{"area":"长滨乡","areaID":""},{"area":"成功镇","areaID":""},{"area":"池上乡","areaID":""},{"area":"达仁乡","areaID":""},{"area":"大武乡","areaID":""},{"area":"东河乡","areaID":""},{"area":"关山镇","areaID":""},{"area":"海瑞乡","areaID":""},{"area":"金峰乡","areaID":""},{"area":"兰屿乡","areaID":""},{"area":"鹿野乡","areaID":""},{"area":"绿岛乡","areaID":""},{"area":"台东市","areaID":""},{"area":"太麻里乡","areaID":""},{"area":"延平乡","areaID":""}],"city":"台东县","cityID":"712500"},{"areas":[{"area":"安定区","areaID":""},{"area":"安南区","areaID":"710306"},{"area":"安平区","areaID":"710305"},{"area":"白河区","areaID":""},{"area":"北门区","areaID":""},{"area":"北区","areaID":"710304"},{"area":"大内区","areaID":""},{"area":"东区","areaID":"710302"},{"area":"东山区","areaID":""},{"area":"关庙区","areaID":""},{"area":"官田区","areaID":""},{"area":"归仁区","areaID":""},{"area":"后壁区","areaID":""},{"area":"佳里区","areaID":""},{"area":"将军区","areaID":""},{"area":"六甲区","areaID":""},{"area":"柳营区","areaID":""},{"area":"龙崎区","areaID":""},{"area":"麻豆区","areaID":""},{"area":"南化区","areaID":""},{"area":"南区","areaID":"710303"},{"area":"楠西区","areaID":""},{"area":"七股区","areaID":""},{"area":"其它区","areaID":"710307"},{"area":"仁德区","areaID":""},{"area":"善化区","areaID":""},{"area":"山上区","areaID":""},{"area":"西港区","areaID":""},{"area":"下营区","areaID":""},{"area":"新化区","areaID":""},{"area":"新市区","areaID":""},{"area":"新营区","areaID":""},{"area":"学甲区","areaID":""},{"area":"盐水区","areaID":""},{"area":"永康区","areaID":""},{"area":"玉井区","areaID":""},{"area":"中西区","areaID":"710301"},{"area":"左镇区","areaID":""}],"city":"台南市","cityID":"710300"},{"areas":[{"area":"北区","areaID":"710405"},{"area":"北屯区","areaID":"710406"},{"area":"大安区","areaID":""},{"area":"大肚区","areaID":""},{"area":"大甲区","areaID":""},{"area":"大里区","areaID":""},{"area":"大雅区","areaID":""},{"area":"东区","areaID":"710402"},{"area":"东势区","areaID":""},{"area":"丰原区","areaID":""},{"area":"和平区","areaID":""},{"area":"后里区","areaID":""},{"area":"龙井区","areaID":""},{"area":"南区","areaID":"710403"},{"area":"南屯区","areaID":"710408"},{"area":"其它区","areaID":"710409"},{"area":"清水区","areaID":""},{"area":"沙鹿区","areaID":""},{"area":"神冈区","areaID":""},{"area":"石冈区","areaID":""},{"area":"太平区","areaID":""},{"area":"潭子区","areaID":""},{"area":"外埔区","areaID":""},{"area":"雾峰区","areaID":""},{"area":"梧栖区","areaID":""},{"area":"乌日区","areaID":""},{"area":"西区","areaID":"710404"},{"area":"西屯区","areaID":"710407"},{"area":"新社区","areaID":""},{"area":"中区","areaID":"710401"}],"city":"台中市","cityID":"710400"},{"areas":[{"area":"八德市","areaID":""},{"area":"大溪镇","areaID":""},{"area":"大园乡","areaID":""},{"area":"复兴乡","areaID":""},{"area":"观音乡","areaID":""},{"area":"龟山乡","areaID":""},{"area":"龙潭乡","areaID":""},{"area":"芦竹乡","areaID":""},{"area":"平镇市","areaID":""},{"area":"桃园市","areaID":""},{"area":"新屋乡","areaID":""},{"area":"杨梅市","areaID":""},{"area":"中坜市","areaID":""}],"city":"桃园县","cityID":"711400"},{"areas":[{"area":"八里区","areaID":""},{"area":"板桥区","areaID":""},{"area":"淡水区","areaID":""},{"area":"贡寮区","areaID":""},{"area":"金山区","areaID":""},{"area":"林口区","areaID":""},{"area":"芦洲区","areaID":""},{"area":"坪林区","areaID":""},{"area":"平溪区","areaID":""},{"area":"瑞芳区","areaID":""},{"area":"三峡区","areaID":""},{"area":"三芝区","areaID":""},{"area":"三重区","areaID":""},{"area":"深坑区","areaID":""},{"area":"石碇区","areaID":""},{"area":"石门区","areaID":""},{"area":"树林区","areaID":""},{"area":"双溪区","areaID":""},{"area":"泰山区","areaID":""},{"area":"土城区","areaID":""},{"area":"万里区","areaID":""},{"area":"五股区","areaID":""},{"area":"乌来区","areaID":""},{"area":"汐止区","areaID":""},{"area":"新店区","areaID":""},{"area":"新庄区","areaID":""},{"area":"莺歌区","areaID":""},{"area":"永和区","areaID":""},{"area":"中和区","areaID":""}],"city":"新北市","cityID":"711100"},{"areas":[{"area":"北区","areaID":"710802"},{"area":"东区","areaID":"710801"},{"area":"其它区","areaID":"710804"},{"area":"香山区","areaID":"710803"}],"city":"新竹市","cityID":"710800"},{"areas":[{"area":"宝山乡","areaID":""},{"area":"北埔乡","areaID":""},{"area":"峨眉乡","areaID":""},{"area":"关西镇","areaID":""},{"area":"横山乡","areaID":""},{"area":"湖口乡","areaID":""},{"area":"尖石乡","areaID":""},{"area":"五峰乡","areaID":""},{"area":"新丰乡","areaID":""},{"area":"新埔镇","areaID":""},{"area":"芎林乡","areaID":""},{"area":"竹北市","areaID":""},{"area":"竹东镇","areaID":""}],"city":"新竹县","cityID":"711300"},{"areas":[{"area":"大同乡","areaID":""},{"area":"钓鱼台","areaID":""},{"area":"冬山乡","areaID":""},{"area":"礁溪乡","areaID":""},{"area":"罗东镇","areaID":""},{"area":"南澳乡","areaID":""},{"area":"三星乡","areaID":""},{"area":"苏澳镇","areaID":""},{"area":"头城镇","areaID":""},{"area":"五结乡","areaID":""},{"area":"宜兰市","areaID":""},{"area":"员山乡","areaID":""},{"area":"壮围乡","areaID":""}],"city":"宜兰县","cityID":"711200"},{"areas":[{"area":"褒忠乡","areaID":""},{"area":"北港镇","areaID":""},{"area":"莿桐乡","areaID":""},{"area":"大埤乡","areaID":""},{"area":"东势乡","areaID":""},{"area":"斗六乡","areaID":""},{"area":"斗南镇","areaID":""},{"area":"二仑乡","areaID":""},{"area":"古坑乡","areaID":""},{"area":"虎尾镇","areaID":""},{"area":"口湖乡","areaID":""},{"area":"林内乡","areaID":""},{"area":"仑背乡","areaID":""},{"area":"麦寮乡","areaID":""},{"area":"水林乡","areaID":""},{"area":"四湖乡","areaID":""},{"area":"台西乡","areaID":""},{"area":"土库镇","areaID":""},{"area":"西螺镇","areaID":""},{"area":"元长乡","areaID":""}],"city":"云林县","cityID":"712100"},{"areas":[{"area":"北斗镇","areaID":""},{"area":"大城乡","areaID":""},{"area":"大村乡","areaID":""},{"area":"二林镇","areaID":""},{"area":"二水乡","areaID":""},{"area":"芳苑乡","areaID":""},{"area":"芬园乡","areaID":""},{"area":"福兴乡","areaID":""},{"area":"和美镇","areaID":""},{"area":"花坛乡","areaID":""},{"area":"鹿港镇","areaID":""},{"area":"埤头乡","areaID":""},{"area":"埔心乡","areaID":""},{"area":"埔盐乡","areaID":""},{"area":"社头乡","areaID":""},{"area":"伸港乡","areaID":""},{"area":"田尾乡","areaID":""},{"area":"田中镇","areaID":""},{"area":"溪湖镇","areaID":""},{"area":"溪州乡","areaID":""},{"area":"线西乡","areaID":""},{"area":"秀水乡","areaID":""},{"area":"永靖乡","areaID":""},{"area":"员林镇","areaID":""},{"area":"彰化市","areaID":""},{"area":"竹塘乡","areaID":""}],"city":"彰化县","cityID":"711700"}]},"hk":{"province":"香港","provinceID":"810000","citys":[{"areas":[{"area":"九龙城区","areaID":"810201"},{"area":"油尖旺区","areaID":"810202"},{"area":"深水埗区","areaID":"810203"},{"area":"黄大仙区","areaID":"810204"},{"area":"观塘区","areaID":"810205"}],"city":"九龙","cityID":"810200"},{"areas":[{"area":"中西区","areaID":"810101"},{"area":"湾仔","areaID":"810102"},{"area":"东区","areaID":"810103"},{"area":"南区","areaID":"810104"}],"city":"香港岛","cityID":"810100"},{"areas":[{"area":"北区","areaID":"810301"},{"area":"大埔区","areaID":"810302"},{"area":"沙田区","areaID":"810303"},{"area":"西贡区","areaID":"810304"},{"area":"元朗区","areaID":"810305"},{"area":"屯门区","areaID":"810306"},{"area":"荃湾区","areaID":"810307"},{"area":"葵青区","areaID":"810308"},{"area":"离岛区","areaID":"810309"}],"city":"新界","cityID":"810300"}]},"mo":{"province":"澳门","provinceID":"820000","citys":[{"areas":[],"city":"澳门半岛","cityID":"820100"},{"areas":[],"city":"离岛","cityID":"820200"}]}}
五级联动DEMO
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `location` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `top_id` int(11) unsigned NOT NULL DEFAULT '0',
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(64) NOT NULL DEFAULT '',
  `province_code` varchar(8) NOT NULL DEFAULT '',
  `city_code` varchar(8) NOT NULL DEFAULT '',
  `area_code` varchar(8) NOT NULL DEFAULT '',
  `street_code` varchar(16) NOT NULL DEFAULT '',
  `village_code` varchar(16) NOT NULL DEFAULT '',
  `level` smallint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
<?php
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root');

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $type = $_POST['type'] ?? '';
        $parentId = $_POST['parent_id'] ?? 0;
        switch ($type) {
            case 'province':
                $level = 2;
                break;
            case 'city':
                $level = 3;
                break;
            case 'area':
                $level = 4;
                break;
            case 'street':
                $level = 5;
                break;
            default:
                $level = 0;
                break;
        }

        $sth = $dbh->prepare('SELECT * FROM `location` WHERE `parent_id` = :parentId AND `level` = :level');
        $sth->bindParam(':parentId', $parentId, PDO::PARAM_INT);
        $sth->bindParam(':level', $level, PDO::PARAM_INT);
        $sth->execute();
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);

        die(json_encode(['data' => $data]));
    }

    $level = 1;
    $sth = $dbh->prepare('SELECT * FROM `location` WHERE `level` = :level');
    $sth->bindParam(':level', $level, PDO::PARAM_INT);
    $sth->execute();
    $provinceList = $sth->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    die($e->getMessage());
}
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <link href="layui/css/layui.css" rel="stylesheet" media="all" />
</head>
<body>
<div class="layui-layout layui-layout-admin">
    <div class="layui-body">
        <form class="layui-form" action="">
            <div class="layui-form-item">
                <div class="layui-input-inline">
                    <select name="province">
                        <option value="">请选择省</option>
                        <?php
                            foreach ($provinceList as $province) {
                                echo "<option value=\"{$province['id']}\">{$province['name']}</option>";
                            }
                        ?>
                    </select>
                </div>
                <div class="layui-input-inline">
                    <select name="city">
                        <option value="">请选择市</option>
                    </select>
                </div>
                <div class="layui-input-inline">
                    <select name="area">
                        <option value="">请选择县/区</option>
                    </select>
                </div>
                <div class="layui-input-inline">
                    <select name="street">
                        <option value="">请选择街道</option>
                    </select>
                </div>
                <div class="layui-input-inline">
                    <select name="village">
                        <option value="">请选择居委会</option>
                    </select>
                </div>
            </div>
        </form>
    </div>
</div>
<script src="jquery-3.3.1.min.js"></script>
<script src="layui/layui.all.js"></script>
<script>
    layui.use(['form'], function() {
        var form = layui.form;

        form.on('select', function(data) {
            let id = data.value;
            let type = data.elem.name;

            if (id !== '' && type !== 'village') {
                $.ajax({
                    url: '',
                    type: 'POST',
                    data: {type: type, parent_id: id},
                    dataType: 'JSON',
                }).done(function(response) {
                    let data = response.data;
                    let obj;
                    switch (type) {
                        case 'province':
                            obj = $('select[name="city"]');
                            obj.html('<option value="">请选择市</option>');
                            break;
                        case 'city':
                            obj = $('select[name="area"]');
                            obj.html('<option value="">请选择县/区</option>');
                            break;
                        case 'area':
                            obj = $('select[name="street"]');
                            obj.html('<option value="">请选择街道</option>');
                            break;
                        case 'street':
                            obj = $('select[name="village"]');
                            obj.html('<option value="">请选择居委会</option>');
                            break;
                    }
                    $.each(data, function() {
                        let option = `<option value="${this.id}">${this.name}</option>`;
                        obj.append(option);
                    });
                    form.render();
                });
            }
        });
    });
</script>
</body>
</html>
0%