Highlighted

How to insert my Case When Statement into PHP using PDO

Participant ,
Mar 30, 2019

Copy link to clipboard

Copied

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

AGBCHTEDWSIGTotal SEctors% AG% BC% HT% ED% WS% IG
21023082513025380

Thank you in advance

Mike

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

21023082513025380

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

Views

664

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How to insert my Case When Statement into PHP using PDO

Participant ,
Mar 30, 2019

Copy link to clipboard

Copied

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

AGBCHTEDWSIGTotal SEctors% AG% BC% HT% ED% WS% IG
21023082513025380

Thank you in advance

Mike

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

21023082513025380

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

Views

665

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 30, 2019 0
Enthusiast ,
Mar 31, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 31, 2019 1
Participant ,
Mar 31, 2019

Copy link to clipboard

Copied

@Teodor K

Thank you so much. I will contact you ASAP.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 31, 2019 0
Participant ,
Apr 13, 2019

Copy link to clipboard

Copied

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

21023082513025380

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 13, 2019 1