CODEWITHSUNDEEP
javascriptphpgoogle-visualization
lrich
lrich

Reputation: 80

Initialize all missing data points to 0 in Google Visualization chart

I'm trying to chart the number of records for a certain given ID on an hourly basis from a MySQL database.

The problem I'm having is that if there are 0 records for the given ID during a given hour, I don't generate a data point for the table. This causes the table to interpolate between two counts if there is no count between the hours.

ex: 4 records at 1pm, 0 records at 2pm, 2 records at 3pm. The table will draw a straight line between 4 and 2 so it would appear on the table that there were 3 records at 2pm when there were actually none.

I'm sure the reason is that I'm not providing null or 0 values for non-existent records but I'm not sure how to go about doing it.

Some info on the table:

id             int AI
TagNumber      int
TimeStamp      timestamp
BatteryStatus  varchar
Location       int

Here is the php code I am using to count the number of instances of a given TagNumber on an hourly basis.

$result = $mysqli->('SELECT hour(TimeStamp) as Hour, count(*) as Count FROM table WHERE Location = 1 AND TagNumber = 12345678 GROUP BY Hour');
// The values here are hard coded for testing purposes. 

$rows = array();
$table = array();
$table['cols'] = array(
    array('label' => 'Hour', 'type' => 'number'),
    array('label' => 'Count', 'type' => 'number')
);

while($row = mysqli_fetch_assoc($result)) {
    $temp = array();
    $temp[] = array('v' => (int) $row['Hour']);
    $temp[] = array('v' => (int) $row['Count']);
    $rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);

I then pass the json encoded table to google visualization api.

What I believe I need to do is set every hour between 1 and 24 that does not have a record returned from the mysql query set to 0, I just have no idea how to achieve that.

I tried initializing an array of Hours from 1 to 24 with 0 as the 'Count' value, but I'm definitely doing something wrong and the php is throwing errors. enter image description here

The point at 12 should read 0, but instead it interpolates between 11 and 13.

Here is my chart with values:

In this case, I would needs hours 1->9, 12, and 15->24 to show a 0.

Upvotes: 0

Views: 93

Answers (1)

Pulkit
Pulkit

Reputation: 137

Try this:

$result = $mysqli->('SELECT hour(TimeStamp) as Hour, count(*) as Count FROM table WHERE Location = 1 AND TagNumber = 12345678 GROUP BY Hour');
// The values here are hard coded for testing purposes. 

$rows = array();
$table = array();
$table['cols'] = array(
    array('label' => 'Hour', 'type' => 'number'),
    array('label' => 'Count', 'type' => 'number')
);

$hoursNotAvail = array(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23);
while($row = mysqli_fetch_assoc($result)) {
    $temp = array();
    $temp[] = array('v' => (int) $row['Hour']);
    $temp[] = array('v' => (int) $row['Count']);
    $rows[] = array('c' => $temp);
    $hoursNotAvail[(int) $row['Hour']] = -1;
}
foreach($hoursNotAvail as $k => $v){
    if($v != -1){
        $temp = array();
        $temp[] = array('v' => $k);
        $temp[] = array('v' => 0);
        $rows[] = array('c' => $temp);
    }
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);

Upvotes: 1

Related Questions