829 829 - 3 months ago 22
MySQL Question

PHP MySQL Count IF Google Charts and pull data from column / row

Thank you in advance for your time.
I have two questions and I am very new at this.

What I am trying to accomplish is to pull data from an osticket database and then have google charts display a pie chart for me. I can get the code to pull the data, but it is pulling all the rows, which is fine for most columns, but for one column "isoverdue" I want it only to count it if, the row is = 1. I have tried a couple of different ways to add an if statement, but they always result in a failed MySQL error.

The second issue I am having is how can I get the google code to update dynamically? I have it hard coded with the results from the pull, but I am wondering how can I make, so that it is not hard coded? Perhaps I would need a new variable so it will total up the counts?

<?php
$servername = "localhost";
$username = "";
$password = "";
$db = "";
$dbh = new PDO("mysql:host=$servername;dbname=$db", $username, $password);

// Create connection
$conn = new mysqli($servername, $username, $password, $db);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
foreach($dbh->query('SELECT COUNT(created) FROM ost_ticket') as $opened);
foreach($dbh->query('SELECT COUNT(closed) FROM ost_ticket') as $closed1);
foreach($dbh->query('SELECT COUNT(isoverdue) FROM ost_ticket') as $overdue);

echo "<tr>";
echo "<td>".$opened['COUNT(created)']."</td>", "<td>".$closed1['COUNT(closed)']."</td>", "<td>" .$overdue['COUNT(isoverdue)']."</td>";
echo "</tr>";

?>


<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {

var data = google.visualization.arrayToDataTable([
['Status', 'Hours per Day'],
['Opened', 8],
['Closed', 5],
['Overdue', 3],

]);

var options = {
title: 'My Daily Activities'
};

var chart = new google.visualization.PieChart(document.getElementById('piechart'));

chart.draw(data, options);
}
</script>
</head>
<body>
<div id="piechart" style="width: 900px; height: 500px;"></div>
</body>
</html>


Thank you again for taking your time to review this.

Answer

for isoverdue, try a CASE statement...

SUM(CASE WHEN isoverdue = 1 THEN 1 ELSE 0 END)

also, you should be able to include them in the same statement,
so the query isn't run two extra times...

SELECT
  COUNT(created) as created,
  COUNT(closed) as closed,
  SUM(CASE WHEN isoverdue = 1 THEN 1 ELSE 0 END) as isoverdue
FROM
  ost_ticket

you can also name the fields with the as operator, then use the name to get value

foreach($dbh->query(...) as $results);
$results['created']
Comments