求基于jQuery+PHP+Ajax关联双MySQL表的动态增删依赖选择框源码

前置准备 #

先在你的MySQL数据库执行以下建表语句：

-- 创建分类表
CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试分类数据
INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, '电子产品'),
(2, '服装'),
(3, '食品');

-- 创建子分类表
CREATE TABLE `sub_category` (
  `sub_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `sub_category_name` varchar(100) NOT NULL,
  PRIMARY KEY (`sub_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试子分类数据
INSERT INTO `sub_category` (`sub_category_id`, `category_id`, `sub_category_name`) VALUES
(1, 1, '手机'),
(2, 1, '笔记本电脑'),
(3, 1, '耳机'),
(4, 2, '上衣'),
(5, 2, '裤子'),
(6, 2, '鞋子'),
(7, 3, '零食'),
(8, 3, '生鲜'),
(9, 3, '饮料');

1. 数据库配置文件 db_config.php #

<?php
// 修改为你自己的数据库连接信息
$host = 'localhost';
$dbname = '你的数据库名';
$username = '你的数据库账号';
$password = '你的数据库密码';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}
?>

2. 前端主页面 index.html #

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>动态联动下拉选择框</title>
    <script src="https://cdn.staticfile.org/jquery/3.6.4/jquery.min.js"></script>
    <style>
        .item-row { margin: 10px 0; display: flex; gap: 10px; align-items: center; }
        select { padding: 6px; min-width: 180px; }
        .add-btn, .remove-btn { padding: 6px 12px; cursor: pointer; }
        .add-btn { background: #409eff; color: #fff; border: none; border-radius: 4px; }
        .remove-btn { background: #f56c6c; color: #fff; border: none; border-radius: 4px; }
    </style>
</head>
<body>
    <div class="container">
        <h3>分类选择列表</h3>
        <div id="item-container">
            <!-- 初始第一行 -->
            <div class="item-row">
                <select class="category-select">
                    <option value="">请选择分类</option>
                </select>
                <select class="sub-category-select">
                    <option value="">请先选择分类</option>
                </select>
                <button type="button" class="remove-btn">删除</button>
            </div>
        </div>
        <button type="button" class="add-btn">新增一行</button>
    </div>

    <script>
        $(document).ready(function(){
            // 页面加载时获取所有分类填充第一行
            loadCategories($('.category-select').first());

            // 加载分类方法
            function loadCategories(selectObj) {
                $.ajax({
                    url: 'ajax_handler.php',
                    method: 'POST',
                    data: {action: 'get_categories'},
                    dataType: 'json',
                    success: function(res) {
                        let html = '<option value="">请选择分类</option>';
                        res.forEach(item => {
                            html += `<option value="${item.category_id}">${item.category_name}</option>`;
                        });
                        selectObj.html(html);
                    }
                });
            }

            // 分类下拉变化时加载对应子分类
            $(document).on('change', '.category-select', function(){
                let categoryId = $(this).val();
                let subSelect = $(this).next('.sub-category-select');
                if(categoryId == '') {
                    subSelect.html('<option value="">请先选择分类</option>');
                    return;
                }
                $.ajax({
                    url: 'ajax_handler.php',
                    method: 'POST',
                    data: {action: 'get_subcategories', category_id: categoryId},
                    dataType: 'json',
                    success: function(res) {
                        let html = '<option value="">请选择子分类</option>';
                        res.forEach(item => {
                            html += `<option value="${item.sub_category_id}">${item.sub_category_name}</option>`;
                        });
                        subSelect.html(html);
                    }
                });
            });

            // 新增行按钮点击事件
            $('.add-btn').click(function(){
                let newRow = `
                <div class="item-row">
                    <select class="category-select">
                        <option value="">请选择分类</option>
                    </select>
                    <select class="sub-category-select">
                        <option value="">请先选择分类</option>
                    </select>
                    <button type="button" class="remove-btn">删除</button>
                </div>
                `;
                $('#item-container').append(newRow);
                // 给新行的分类下拉填充选项
                loadCategories($('#item-container .category-select').last());
            });

            // 删除行按钮点击事件
            $(document).on('click', '.remove-btn', function(){
                // 至少保留一行
                if($('.item-row').length > 1) {
                    $(this).closest('.item-row').remove();
                } else {
                    alert('至少保留一行选择项');
                }
            });
        });
    </script>
</body>
</html>

3. Ajax请求处理文件 ajax_handler.php #

<?php
include 'db_config.php';

$action = $_POST['action'];

if($action == 'get_categories') {
    $stmt = $conn->prepare("SELECT * FROM category ORDER BY category_id ASC");
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($data);
}

if($action == 'get_subcategories') {
    $category_id = $_POST['category_id'];
    $stmt = $conn->prepare("SELECT * FROM sub_category WHERE category_id = ? ORDER BY sub_category_id ASC");
    $stmt->execute([$category_id]);
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($data);
}
?>

使用说明 #

1. 将上述三个文件上传到你的PHP运行环境目录下
2. 修改db_config.php中的数据库连接信息为你自己的配置
3. 访问index.html即可运行，支持多行动态增删、分类子分类联动

内容的提问来源于stack exchange，提问作者Mian Usman