php三级联动数据库

PHP三级联动数据库

基础概念

PHP三级联动通常指的是在网页表单中，通过三个下拉菜单（Select）实现数据的级联选择。例如，省份、城市、区县的三级联动。这种功能通常用于地理信息的选择，但也可以应用于其他层级关系的数据选择。

类型

静态三级联动：预先定义好所有层级的数据，通过JavaScript在前端实现联动。
动态三级联动：通过AJAX技术，从服务器动态获取下一层级的数据，实现联动。

应用场景

地理信息系统（GIS）
行政区划选择
产品分类选择

实现步骤

数据库设计：设计三个表，分别存储省份、城市、区县的信息，并通过外键建立关联。
前端页面：创建三个下拉菜单，并使用JavaScript或jQuery实现联动效果。
后端处理：编写PHP脚本处理AJAX请求，返回下一层级的数据。

示例代码

数据库设计

CREATE TABLE provinces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE cities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    province_id INT,
    name VARCHAR(50) NOT NULL,
    FOREIGN KEY (province_id) REFERENCES provinces(id)
);

CREATE TABLE districts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    city_id INT,
    name VARCHAR(50) NOT NULL,
    FOREIGN KEY (city_id) REFERENCES cities(id)
);

前端页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>三级联动示例</title>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
    <select id="province">
        <option value="">请选择省份</option>
    </select>
    <select id="city" disabled>
        <option value="">请选择城市</option>
    </select>
    <select id="district" disabled>
        <option value="">请选择区县</option>
    </select>

    <script>
        $(document).ready(function() {
            $.ajax({
                url: 'get_provinces.php',
                type: 'GET',
                success: function(data) {
                    var provinces = JSON.parse(data);
                    $.each(provinces, function(index, province) {
                        $('#province').append($('<option>', { 
                            value: province.id,
                            text : province.name 
                        }));
                    });
                }
            });

            $('#province').change(function() {
                var provinceId = $(this).val();
                if (provinceId) {
                    $('#city').prop('disabled', false);
                    $.ajax({
                        url: 'get_cities.php',
                        type: 'GET',
                        data: { province_id: provinceId },
                        success: function(data) {
                            var cities = JSON.parse(data);
                            $('#city').empty().append($('<option>', { 
                                value: '',
                                text : '请选择城市' 
                            }));
                            $.each(cities, function(index, city) {
                                $('#city').append($('<option>', { 
                                    value: city.id,
                                    text : city.name 
                                }));
                            });
                        }
                    });
                } else {
                    $('#city').empty().prop('disabled', true);
                    $('#district').empty().prop('disabled', true);
                }
            });

            $('#city').change(function() {
                var cityId = $(this).val();
                if (cityId) {
                    $('#district').prop('disabled', false);
                    $.ajax({
                        url: 'get_districts.php',
                        type: 'GET',
                        data: { city_id: cityId },
                        success: function(data) {
                            var districts = JSON.parse(data);
                            $('#district').empty().append($('<option>', { 
                                value: '',
                                text : '请选择区县' 
                            }));
                            $.each(districts, function(index, district) {
                                $('#district').append($('<option>', { 
                                    value: district.id,
                                    text : district.name 
                                }));
                            });
                        }
                    });
                } else {
                    $('#district').empty().prop('disabled', true);
                }
            });
        });
    </script>
</body>
</html>

后端处理

// get_provinces.php
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT id, name FROM provinces";
$result = $conn->query($sql);

$provinces = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $provinces[] = $row;
    }
}
echo json_encode($provinces);

$conn->close();
?>

// get_cities.php
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$province_id = $_GET['province_id'];

$sql = "SELECT id, name FROM cities WHERE province_id = $province_id";
$result = $conn->query($sql);

$cities = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $cities[] = $row;
    }
}
echo json_encode($cities);

$conn->close();
?>

// get_districts.php
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$city_id = $_GET['city_id'];

$sql = "SELECT id, name FROM districts WHERE city_id = $city_id";
$result = $conn->query($sql);

$districts = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $districts[] = $row;
    }
}
echo json_encode($districts);

$conn->close();
?>