How to insert my Case When Statement into PHP using PDO

[Prince Mike]

Hello,

Good afternoon.

Please solicit your kind help. I am new to PDO...

I have a working Case When statement as seen below. Now I am upgrading my application PHP to PHP version 7. I have this code working well with DW CS6 Server Connect but with DW CC and PHP 7, I cant use Server Connect. In addition, DMXzone extensions do not give room for advanced codes like this one. So I want to add it to my application directly.

Can someone please help me write it in PHP using PDO so I can integrate it into my application's column chart

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	Total SEctors	% AG	% BC	% HT	% ED	% WS	% IG
2	1	0	2	3	0	8	25	13	0	25	38	0

Thank you in advance

Mike

[Teodor K]

Hello Mike,

I'm Teodor and I am a part of the DMXzone team.

If your question is related to our extensions (as I see you mention Server Connect), please make sure to send me an email to support@dmxzone.com explaining the issue you're having. We do not provide support in Dreamweaver forum

Cheers,

Teodor

[Prince Mike]

@Teodor K

Thank you so much. I will contact you ASAP.

[Prince Mike · Accepted solution]

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	Total Sectors	% 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