基于Ajax+PHP实现三级联动下拉筛选数据库的技术问询

我来帮你搞定这个三级联动的需求，结合你的数据库表结构，我会给出完整的前端+后端示例代码，一步步实现联动逻辑。

1. 完整HTML结构 #

首先把三个下拉框的HTML写好，给每个下拉框加上id方便JS操作，同时预留加载状态的提示：

<html>
<head>
    <title>Product-MSN联动筛选</title>
    <script>
        // 这里后面放JS联动逻辑
    </script>
</head>
<body>
    <div id="filter-container">
        <div>
            <label for="type">选择类型:</label>
            <select id="type" name="type">
                <option value="">请选择类型</option>
            </select>
        </div>
        <div style="margin-top:10px;">
            <label for="product">选择产品:</label>
            <select id="product" name="product" disabled>
                <option value="">请先选择类型</option>
            </select>
        </div>
        <div style="margin-top:10px;">
            <label for="msn">选择MSN:</label>
            <select id="msn" name="msn" disabled>
                <option value="">请先选择产品</option>
            </select>
        </div>
    </div>
</body>
</html>

2. JavaScript联动逻辑 #

用原生JS实现下拉框的联动，通过Fetch API请求后端接口获取数据，同时处理加载状态和禁用状态：

<script>
    // 页面加载完成后初始化类型下拉框
    document.addEventListener('DOMContentLoaded', function() {
        loadTypes();
        
        // 类型选择事件
        document.getElementById('type').addEventListener('change', function() {
            const typeId = this.value;
            const productSelect = document.getElementById('product');
            const msnSelect = document.getElementById('msn');
            
            // 清空并重置产品和MSN下拉框
            productSelect.innerHTML = '<option value="">加载中...</option>';
            productSelect.disabled = true;
            msnSelect.innerHTML = '<option value="">请先选择产品</option>';
            msnSelect.disabled = true;
            
            if (typeId) {
                loadProducts(typeId);
            } else {
                productSelect.innerHTML = '<option value="">请先选择类型</option>';
            }
        });
        
        // 产品选择事件
        document.getElementById('product').addEventListener('change', function() {
            const prodId = this.value;
            const msnSelect = document.getElementById('msn');
            
            msnSelect.innerHTML = '<option value="">加载中...</option>';
            msnSelect.disabled = true;
            
            if (prodId) {
                loadMSNs(prodId);
            } else {
                msnSelect.innerHTML = '<option value="">请先选择产品</option>';
            }
        });
    });
    
    // 加载所有类型
    function loadTypes() {
        fetch('get_types.php')
            .then(response => response.json())
            .then(data => {
                const typeSelect = document.getElementById('type');
                data.forEach(type => {
                    const option = document.createElement('option');
                    option.value = type.id;
                    option.textContent = type.product_name; // 注意你的type表字段是product_name，这里对应显示
                    typeSelect.appendChild(option);
                });
            })
            .catch(error => {
                console.error('加载类型失败:', error);
                document.getElementById('type').innerHTML += '<option value="">加载失败，请刷新</option>';
            });
    }
    
    // 根据类型ID加载产品
    function loadProducts(typeId) {
        fetch(`get_products.php?type_id=${typeId}`)
            .then(response => response.json())
            .then(data => {
                const productSelect = document.getElementById('product');
                productSelect.innerHTML = '<option value="">请选择产品</option>';
                data.forEach(product => {
                    const option = document.createElement('option');
                    option.value = product.id;
                    option.textContent = product.product_name;
                    productSelect.appendChild(option);
                });
                productSelect.disabled = false;
            })
            .catch(error => {
                console.error('加载产品失败:', error);
                document.getElementById('product').innerHTML = '<option value="">加载失败，请重试</option>';
            });
    }
    
    // 根据产品ID加载MSN
    function loadMSNs(prodId) {
        fetch(`get_msns.php?prod_id=${prodId}`)
            .then(response => response.json())
            .then(data => {
                const msnSelect = document.getElementById('msn');
                msnSelect.innerHTML = '<option value="">请选择MSN</option>';
                data.forEach(msn => {
                    const option = document.createElement('option');
                    option.value = msn.id;
                    option.textContent = msn.msn_name;
                    msnSelect.appendChild(option);
                });
                msnSelect.disabled = false;
            })
            .catch(error => {
                console.error('加载MSN失败:', error);
                document.getElementById('msn').innerHTML = '<option value="">加载失败，请重试</option>';
            });
    }
</script>

3. 后端接口示例（PHP） #

下面是三个后端接口的PHP代码，负责从数据库获取对应数据，返回JSON格式：

get_types.php（获取所有类型） #

<?php
// 数据库连接配置，根据你的实际情况修改
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->query("SELECT id, product_name FROM type");
    $types = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    header('Content-Type: application/json');
    echo json_encode($types);
} catch(PDOException $e) {
    header('HTTP/1.1 500 Internal Server Error');
    echo json_encode(['error' => $e->getMessage()]);
}
?>

get_products.php（根据类型ID获取产品） #

<?php
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $typeId = $_GET['type_id'] ?? '';
    if (!$typeId) {
        throw new Exception('缺少类型ID参数');
    }
    
    $stmt = $pdo->prepare("SELECT id, product_name FROM Product WHERE id_typed = ?");
    $stmt->execute([$typeId]);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    header('Content-Type: application/json');
    echo json_encode($products);
} catch(PDOException $e) {
    header('HTTP/1.1 500 Internal Server Error');
    echo json_encode(['error' => $e->getMessage()]);
} catch(Exception $e) {
    header('HTTP/1.1 400 Bad Request');
    echo json_encode(['error' => $e->getMessage()]);
}
?>

get_msns.php（根据产品ID获取关联的MSN） #

<?php
$host = 'localhost';
$dbname = 'your_database';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $prodId = $_GET['prod_id'] ?? '';
    if (!$prodId) {
        throw new Exception('缺少产品ID参数');
    }
    
    // 通过中间表prod_msn关联MSN表
    $stmt = $pdo->prepare("
        SELECT m.id, m.msn_name 
        FROM prod_msn pm 
        JOIN msn m ON pm.id_msn = m.id 
        WHERE pm.id_prod = ?
    ");
    $stmt->execute([$prodId]);
    $msns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    header('Content-Type: application/json');
    echo json_encode($msns);
} catch(PDOException $e) {
    header('HTTP/1.1 500 Internal Server Error');
    echo json_encode(['error' => $e->getMessage()]);
} catch(Exception $e) {
    header('HTTP/1.1 400 Bad Request');
    echo json_encode(['error' => $e->getMessage()]);
}
?>

关键说明 #

• 数据库关联逻辑：
  • Type和Product通过type.id = Product.id_typed关联
  • Product和MSN通过中间表prod_msn关联，prod_msn.id_prod = Product.id，prod_msn.id_msn = msn.id
• 前端交互：每次选择上级下拉框时，清空并禁用下级下拉框，加载完成后再启用
• 错误处理：前后端都加入了基本的错误捕获，方便调试

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