PHP MySQL三级联动下拉框异常：选服务类别显示物料物品

开发者阿泽

2026-6-25

三级联动下拉框故障排查与修复

问题场景

基于PHP+MySQL开发三级联动下拉框，包含采购类型（typeAchat）、类别（categorie）、物品/服务（article）：

采购类型分「物料（materiel）」和「服务（service）」两个选项
选「物料」应显示物料类别，选「服务」应显示服务类别
物料类别能正确加载对应物品，但服务类别会错误加载物料物品

相关代码

主页面HTML

<!DOCTYPE html>
<html lang="fr">

<head>
    <meta charset="UTF-8">
    <title>新采购申请</title>
</head>

<body>
    <h2>新采购申请</h2>

    <form id="achatForm">
        <div class="form-group">
            <label for="typeAchat">采购类型：</label>
            <select class="form-control" id="typeAchat">
                <option value="">请选择采购类型</option>
                <?php
                // 连接到数据库'dac'
                $conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

                // 检查连接
                if (!$conn) {
                    die("连接失败: " . mysqli_connect_error());
                }

                // 获取采购类型的查询语句
                $queryTypeAchat = "SELECT * FROM type_achat";
                $resultTypeAchat = mysqli_query($conn, $queryTypeAchat);

                while ($row = mysqli_fetch_assoc($resultTypeAchat)) {
                    echo '<option value="' . $row['id_type_achat'] . '">' . $row['nom_type_achat'] . '</option>';
                }

                // 关闭连接
                mysqli_close($conn);
                ?>
            </select>
        </div>

        <div class="form-group" id="categoriesGroup" style="display: none;">
            <label for="categorieSelect">类别：</label>
            <select class="form-control" id="categorieSelect"></select>
        </div>

        <div class="form-group" id="articlesGroup" style="display: none;">
            <label for="articleSelect">物品或服务：</label>
            <select class="form-control" id="articleSelect"></select>
        </div>

        <input type="submit" value="提交">
    </form>

    <script>
        document.getElementById('typeAchat').addEventListener('change', function() {
            var typeAchat = this.value;
            if (typeAchat !== '') {
                document.getElementById('categoriesGroup').style.display = 'block';

                // AJAX请求加载对应类别
                var xhr = new XMLHttpRequest();
                xhr.open('POST', 'load_categories.php', true);
                xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
                xhr.onload = function() {
                    if (xhr.status === 200) {
                        document.getElementById('categorieSelect').innerHTML = xhr.responseText;
                        document.getElementById('articlesGroup').style.display = 'none';
                    } else {
                        console.error('请求失败，状态码: ' + xhr.status);
                    }
                };
                xhr.send('typeAchat=' + typeAchat);
            } else {
                document.getElementById('categoriesGroup').style.display = 'none';
                document.getElementById('articlesGroup').style.display = 'none';
            }
        });

        document.getElementById('categorieSelect').addEventListener('change', function() {
            var categorieId = this.value;
            if (categorieId !== '') {
                // AJAX请求加载对应物品
                var xhr = new XMLHttpRequest();
                xhr.open('POST', 'load_articles.php', true);
                xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
                xhr.onload = function() {
                    if (xhr.status === 200) {
                        document.getElementById('articleSelect').innerHTML = xhr.responseText;
                        document.getElementById('articlesGroup').style.display = 'block';
                    } else {
                        console.error('请求失败，状态码: ' + xhr.status);
                    }
                };
                xhr.send('categorieId=' + categorieId);
            } else {
                document.getElementById('articlesGroup').style.display = 'none';
            }
        });
    </script>
</body>

</html>

load_categories.php

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['typeAchat'])) {
    $typeAchat = $_POST['typeAchat'];
    
    // 根据采购类型（物料/服务）查询对应类别
    $query = "SELECT * FROM categorie_materiels WHERE id_type_achat = $typeAchat";
    $result = mysqli_query($conn, $query);

    $options = '<option value="">请选择类别</option>';
    while ($row = mysqli_fetch_assoc($result)) {
        $options .= '<option value="' . $row['id_categorie_materiels'] . '">' . $row['nom_categorie_materiels'] . '</option>';
    }

    // 根据采购类型添加服务类别
    $queryService = "SELECT * FROM categorie_services WHERE id_type_achat = $typeAchat";
    $resultService = mysqli_query($conn, $queryService);

    while ($row = mysqli_fetch_assoc($resultService)) {
        $options .= '<option value="' . $row['id_categorie_services'] . '">' . $row['nom_categorie_services'] . '</option>';
    }

    echo $options;
}

mysqli_close($conn);
?>

load_articles.php

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['categorieId'])) {
    $categorieId = $_POST['categorieId'];

    // 检查该类别是否属于物料类别
    $queryMaterial = "SELECT * FROM categorie_materiels WHERE id_categorie_materiels = $categorieId";
    $resultMaterial = mysqli_query($conn, $queryMaterial);

    if (mysqli_num_rows($resultMaterial) > 0) {
        // 查询该物料类别下的物品
        $query = "SELECT * FROM materiel WHERE id_categorie_materiel = $categorieId";
        $result = mysqli_query($conn, $query);

        $options = '<option value="">请选择物品</option>';
        while ($row = mysqli_fetch_assoc($result)) {
            $options .= '<option value="' . $row['id_materiel'] . '">' . $row['nom_materiel'] . '</option>';
        }
    } else {
        // 查询该服务类别下的服务
        $queryService = "SELECT * FROM service WHERE id_categorie_services = $categorieId";
        $resultService = mysqli_query($conn, $queryService);

        $options = '<option value="">请选择服务</option>';
        while ($row = mysqli_fetch_assoc($resultService)) {
            $options .= '<option value="' . $row['id_service'] . '">' . $row['nom_service'] . '</option>';
        }
    }

    echo $options;
}

mysqli_close($conn);
?>

问题原因

类别ID冲突：categorie_materiels和categorie_services两个表的ID是独立自增的，极易出现相同ID值（比如物料类别ID=1，服务类别也有ID=1）。
判断逻辑缺陷：load_articles.php中优先检查物料类别表，若存在匹配ID就加载物料物品。当服务类别ID与物料类别ID重复时，会误判为物料类别，导致加载错误数据。
类别加载逻辑冗余：load_categories.php中不管选择哪种采购类型，都会同时加载两个表的类别数据，既不符合需求，也加剧了ID冲突的概率。

修复方案

方案一：给类别ID添加类型标识（推荐）

修改load_categories.php，在类别选项的value中加入类型前缀，明确区分物料和服务类别：

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['typeAchat'])) {
    $typeAchat = $_POST['typeAchat'];
    $options = '<option value="">请选择类别</option>';
    
    // 根据采购类型加载对应类别
    if ($typeAchat == 1) { // 替换为实际物料类型的ID
        $query = "SELECT * FROM categorie_materiels WHERE id_type_achat = $typeAchat";
        $result = mysqli_query($conn, $query);
        while ($row = mysqli_fetch_assoc($result)) {
            // 添加'm-'前缀标识物料类别
            $options .= '<option value="m-' . $row['id_categorie_materiels'] . '">' . $row['nom_categorie_materiels'] . '</option>';
        }
    } elseif ($typeAchat == 2) { // 替换为实际服务类型的ID
        $queryService = "SELECT * FROM categorie_services WHERE id_type_achat = $typeAchat";
        $resultService = mysqli_query($conn, $queryService);
        while ($row = mysqli_fetch_assoc($resultService)) {
            // 添加's-'前缀标识服务类别
            $options .= '<option value="s-' . $row['id_categorie_services'] . '">' . $row['nom_categorie_services'] . '</option>';
        }
    }

    echo $options;
}

mysqli_close($conn);
?>

修改load_articles.php，根据前缀判断类别类型：

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['categorieId'])) {
    $categorieId = $_POST['categorieId'];
    // 拆分类型标识和实际ID
    list($type, $id) = explode('-', $categorieId);
    
    if ($type == 'm') {
        // 查询物料类别下的物品
        $query = "SELECT * FROM materiel WHERE id_categorie_materiel = $id";
        $result = mysqli_query($conn, $query);
        $options = '<option value="">请选择物品</option>';
        while ($row = mysqli_fetch_assoc($result)) {
            $options .= '<option value="' . $row['id_materiel'] . '">' . $row['nom_materiel'] . '</option>';
        }
    } elseif ($type == 's') {
        // 查询服务类别下的服务
        $queryService = "SELECT * FROM service WHERE id_categorie_services = $id";
        $resultService = mysqli_query($conn, $queryService);
        $options = '<option value="">请选择服务</option>';
        while ($row = mysqli_fetch_assoc($resultService)) {
            $options .= '<option value="' . $row['id_service'] . '">' . $row['nom_service'] . '</option>';
        }
    }

    echo $options;
}

mysqli_close($conn);
?>

方案二：优化类别判断逻辑

若不想修改ID格式，可调整load_articles.php的查询顺序，优先检查服务类别表：

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['categorieId'])) {
    $categorieId = $_POST['categorieId'];

    // 优先检查服务类别
    $queryServiceCat = "SELECT * FROM categorie_services WHERE id_categorie_services = $categorieId";
    $resultServiceCat = mysqli_query($conn, $queryServiceCat);

    if (mysqli_num_rows($resultServiceCat) > 0) {
        // 查询服务类别下的服务
        $queryService = "SELECT * FROM service WHERE id_categorie_services = $categorieId";
        $resultService = mysqli_query($conn, $queryService);
        $options = '<option value="">请选择服务</option>';
        while ($row = mysqli_fetch_assoc($resultService)) {
            $options .= '<option value="' . $row['id_service'] . '">' . $row['nom_service'] . '</option>';
        }
    } else {
        // 查询物料类别下的物品
        $query = "SELECT * FROM materiel WHERE id_categorie_materiel = $categorieId";
        $result = mysqli_query($conn, $query);
        $options = '<option value="">请选择物品</option>';
        while ($row = mysqli_fetch_assoc($result)) {
            $options .= '<option value="' . $row['id_materiel'] . '">' . $row['nom_materiel'] . '</option>';
        }
    }

    echo $options;
}

mysqli_close($conn);
?>

同时修改load_categories.php，仅加载对应类型的类别：

<?php
// 连接到数据库'dac'
$conn = mysqli_connect("localhost", "casablanca", "casablanca", "dac");

if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

if (isset($_POST['typeAchat'])) {
    $typeAchat = $_POST['typeAchat'];
    $options = '<option value="">请选择类别</option>';
    
    // 仅加载对应类型的类别
    $query = "SELECT * FROM categorie_materiels WHERE id_type_achat = $typeAchat";
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $options .= '<option value="' . $row['id_categorie_materiels'] . '">' . $row['nom_categorie_materiels'] . '</option>';
        }
    } else {
        $queryService = "SELECT * FROM categorie_services WHERE id_type_achat = $typeAchat";
        $resultService = mysqli_query($conn, $queryService);
        while ($row = mysqli_fetch_assoc($resultService)) {
            $options .= '<option value="' . $row['id_categorie_services'] . '">' . $row['nom_categorie_services'] . '</option>';
        }
    }

    echo $options;
}

mysqli_close($conn);
?>

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

PHP MySQL三级联动下拉框异常：选服务类别显示物料物品

问题场景 #

相关代码 #

主页面HTML #

load_categories.php #

load_articles.php #

问题原因 #

修复方案 #

方案一：给类别ID添加类型标识（推荐） #

方案二：优化类别判断逻辑 #