I am working on a Column Chart and want to add the values of my MySQL Case When Statement into the chart. However, I need help to write the statement in PHP using PDO so I can integrate it into my application's column chart.How can I write the Case When Statement into PHP using PDO?

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

My expected result is supposed to look like this

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

Thank you in advance

1 Answers

0
Prince Michael On Best Solutions

Thanks to everyone who attempted to provide help to my question. I finally got solution to my question. I am going to post the code I used below.

Now there are 2 aspect of values that I needed for my chart. The first are Normal COUNT values as seen below from AG to IG and the second are Percentage values as seen from %AG to %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

To generate this values from the table, I digged online and found some codes on how to COUNT values in MySQL using PDO Prepared Statement. After studying the codes, I was able to write mine as seen below

   <?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();

    }

    ?> 

With this line of codes, I was able to count records and then generate the percentage.

<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>

OUTPUT

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

For Normal COUNT Values with no percentage, I use these codes

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' "); ?> 

I trust someone will find these useful