如何通过PHP PDO嵌入MySQL CASE WHEN语句并用于柱状图

没问题，我来帮你搞定这个需求！下面是从数据库查询到数据可视化的完整流程：

1. PHP PDO 数据库连接与SQL执行 #

首先咱们用PDO建立安全的数据库连接，然后执行你提供的统计SQL：

<?php
// 数据库连接参数，替换成你自己的配置
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';

try {
    // 初始化PDO连接，设置错误模式为异常，方便调试
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 你的统计SQL语句
    $sql = "SELECT 
                COUNT(CASE WHEN tbl_projects.projsector = 'Agriculture' THEN 1 END) AS `AG`,
                COUNT(CASE WHEN tbl_projects.projsector = 'Building Construction' THEN 1 END) AS `BC`,
                COUNT(CASE WHEN tbl_projects.projsector = 'Health' THEN 1 END) AS `HT`,
                COUNT(CASE WHEN tbl_projects.projsector = 'Education' THEN 1 END) AS `ED`,
                COUNT(CASE WHEN tbl_projects.projsector = 'Water Supply' THEN 1 END) AS `WS`,
                COUNT(CASE WHEN tbl_projects.projsector = 'Income Generation' THEN 1 END) AS `IG`,
                COUNT(tbl_projects.projsector) AS `Total Sectors`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Agriculture' THEN 1 END)/COUNT(tbl_projects.projsector) * 100)) AS `% AG`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Building Construction' THEN 1 END)/COUNT(tbl_projects.projsector) * 100)) AS `% BC`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Health' THEN 1 END)/COUNT(tbl_projects.projsector) * 100)) AS `% HT`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Education' THEN 1 END)/COUNT(tbl_projects.projsector) * 100 )) AS `% ED`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Water Supply' THEN 1 END)/COUNT(tbl_projects.projsector) * 100)) AS `% WS`,
                CONCAT(ROUND(COUNT(CASE WHEN tbl_projects.projsector = 'Income Generation' THEN 1 END)/COUNT(tbl_projects.projsector) * 100)) AS `% IG`
            FROM tbl_projects";

    // 执行查询并获取结果
    $stmt = $pdo->query($sql);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);

    // 关闭连接（可选，PHP会自动回收资源）
    $pdo = null;
} catch(PDOException $e) {
    die("数据库操作失败: " . $e->getMessage());
}
?>

2. 整理数据为柱状图适配格式 #

查询结果$stats是一个关联数组，咱们把它整理成图表需要的分类标签和对应数据，方便前端直接使用：

// 定义行业别名与全称的映射，让图表显示更友好
$sectorMap = [
    'AG' => 'Agriculture',
    'BC' => 'Building Construction',
    'HT' => 'Health',
    'ED' => 'Education',
    'WS' => 'Water Supply',
    'IG' => 'Income Generation'
];

// 提取项目数量和百分比数据
$chartLabels = [];
$countData = [];
$percentData = [];

foreach ($sectorMap as $alias => $name) {
    $chartLabels[] = $name;
    $countData[] = (int)$stats[$alias];
    $percentData[] = (int)$stats["% $alias"];
}

// 打包成JSON格式，方便传给前端
$chartData = json_encode([
    'labels' => $chartLabels,
    'counts' => $countData,
    'percentages' => $percentData,
    'total' => (int)$stats['Total Sectors']
]);

3. 集成到前端柱状图 #

这里以常用的Chart.js为例，展示如何用整理好的数据渲染柱状图，你也可以换成你正在使用的其他图表库：

<!-- 先引入Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<!-- 图表容器 -->
<canvas id="sectorChart" width="600" height="300"></canvas>

<script>
// 从PHP获取JSON格式的统计数据
const chartData = <?php echo $chartData; ?>;

// 初始化柱状图
const ctx = document.getElementById('sectorChart').getContext('2d');
new Chart(ctx, {
    type: 'bar',
    data: {
        labels: chartData.labels,
        datasets: [{
            label: '项目数量',
            data: chartData.counts,
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255, 99, 132, 1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: '项目数量'
                }
            },
            x: {
                title: {
                    display: true,
                    text: '行业分类'
                }
            }
        },
        // 可选：鼠标悬停时显示百分比
        plugins: {
            tooltip: {
                callbacks: {
                    label: function(context) {
                        let label = context.dataset.label + ': ' + context.parsed.y;
                        label += ' (' + chartData.percentages[context.dataIndex] + '%)';
                        return label;
                    }
                }
            }
        }
    }
});
</script>

额外提示 #

• 确保你的PHP环境已开启PDO扩展；
• 如果数据库中没有任何项目（Total Sectors为0），建议在SQL中用NULLIF(COUNT(tbl_projects.projsector), 0)作为除数，避免除法报错；
• 你可以根据自己的需求调整图表样式、颜色，或者切换成其他类型的图表（比如饼图）。

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