时间戳

首页 > 解决方案 > 如何使用 PDO 将我的 MySQL Case When 语句插入 PHP，以便我可以将输出添加到我的柱形图中

pdo - 如何使用 PDO 将我的 MySQL Case When 语句插入 PHP，以便我可以将输出添加到我的柱形图中

问题描述

我正在制作一个柱形图，并希望将我的 MySQL Case When Statement 的值添加到图表中。但是，我需要帮助才能使用 PDO 在 PHP 中编写语句，以便将其集成到我的应用程序的柱形图中。如何使用 PDO 将 Case When 语句写入 PHP？

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

我的预期结果应该是这样的

AG  BC HT ED WS IG TotalSEctors  %AG  %BC %HT %ED %WS %IG    
2   1  0  2  3  0      8          25  13   0  25  38   0

先感谢您

标签： pdo

解决方案

感谢所有试图为我的问题提供帮助的人。我终于解决了我的问题。我将在下面发布我使用的代码。

现在我的图表需要两个方面的值。第一个是从 AG 到 IG 的正常 COUNT 值，第二个是从 %AG 到 %IG 看到的百分比值。

AG  BC HT ED WS IG TotalSEctors  %AG  %BC %HT %ED %WS %IG    
2   1  0  2  3  0      8          25  13   0  25  38   0

为了从表中生成这些值，我在网上挖掘并找到了一些关于如何使用 PDO Prepared Statement 在 MySQL 中对值进行计数的代码。在研究了代码之后，我能够编写如下所示的代码

   <?php

    //database connection

    include('Connections/db.php');

    //create a function for count

    function rowCount($connect,$query){

        $stmt = $connect->prepare($query);

    $stmt->execute();

    return $stmt->rowCount();

    }

    ?>

使用这行代码，我能够计算记录，然后生成百分比。

<h1> Agriculture = `<?php echo (rowCount($connect,"SELECT projsector AS '% AG' FROM 
tbl_projects WHERE projsector = 'Agriculture' ") /  rowCount($connect,"SELECT 
projsector FROM tbl_projects ")) * 100; ?>` </h1>

<h1> Building Construction = `<?php echo (rowCount($connect,"SELECT projsector AS '% 
AG' FROM tbl_projects WHERE projsector = 'Building Construction' ") /  
rowCount($connect,"SELECT projsector FROM tbl_projects ")) * 100; ?> </h1>

<h1> Health = `<?php echo (rowCount($connect,"SELECT projsector AS '% AG' FROM 
tbl_projects WHERE projsector = 'Health' ") /  rowCount($connect,"SELECT projsector 
FROM tbl_projects ")) * 100; ?>` </h1>

<h1> Education = `<?php echo (rowCount($connect,"SELECT projsector AS '% AG' FROM 
tbl_projects WHERE projsector = 'Education' ") /  rowCount($connect,"SELECT 
projsector FROM tbl_projects ")) * 100; ?>` </h1>   

<h1> Water Supply = `<?php echo (rowCount($connect,"SELECT projsector AS '% AG' FROM 
tbl_projects WHERE projsector = 'Water Supply' ") /  rowCount($connect,"SELECT 
projsector FROM tbl_projects ")) * 100; ?>` </h1> 

<h1> Income Generation = `<?php echo (rowCount($connect,"SELECT projsector AS '% AG' 
FROM tbl_projects WHERE projsector = 'Income Generation' ") /  
rowCount($connect,"SELECT projsector FROM tbl_projects ")) * 100; ?>` </h1>

输出

Agriculture = 25%
Building Construction = 12.5%
Health = 0%
Education = 25%
Water Supply = 37.5%
Income Generation = 0%

对于没有百分比的正常 COUNT 值，我使用这些代码

Agriculture = <?php echo rowCount($connect,"SELECT projsector AS '% AG' FROM 
tbl_projects WHERE projsector = 'Agriculture' "); ?>   

Building Construction = <?php echo rowCount($connect,"SELECT projsector AS '% BC' 
FROM tbl_projects WHERE projsector = 'Building Construction' "); ?>

Health = <?php echo rowCount($connect,"SELECT projsector AS '% HT' FROM tbl_projects 
WHERE projsector = 'Health' "); ?>   

Education = <?php echo rowCount($connect,"SELECT projsector AS '% ED' FROM 
tbl_projects WHERE projsector = 'Education' "); ?>

Water Supply = <?php echo rowCount($connect,"SELECT projsector AS '% WS' FROM 
tbl_projects WHERE projsector = 'Water Supply' "); ?>  

Income Generation = <?php echo rowCount($connect,"SELECT projsector AS '% IG' FROM 
tbl_projects WHERE projsector = 'Income Generation' "); ?>

我相信有人会发现这些有用

推荐阅读