829 829 - 1 year ago 125
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?

$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>";


<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
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);
<div id="piechart" style="width: 900px; height: 500px;"></div>

Thank you again for taking your time to review this.

Answer Source

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

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

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

foreach($dbh->query(...) as $results);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download