Google Chart help in getting data to show correctly

PHP
1

Currently I have an SQL query that reports total views ever and how many total views daily. I am trying to convert this so it uses Google Chart functionality which reports daily and shows the amount of views across a week. I cannot figure out how to do it properly as everytime i kinda get it working, if there is 0 views in a day. That day does not show up so I just need some guidance from an expert. Thanks in advance.

Currently this is how I get the total and daily views code wise:

    public function countViews($type_id, $type) // Count all views for specific type
    {
		$bind = [
			':type_id' => $type_id,
			':type' => $type
		];
        $results = $this->db->select('chewi_views','type_id = :type_id AND type = :type', $bind);
        $totalRows = count($results);
		if($results === FALSE){ $totalRows = '0'; }
        return $totalRows;
    }

    public function countDailyViews($type_id, $type) // Count all daily views for specific type
    {
		$bind = [
			':type_id' => $type_id,
			':type' => $type
		];
        $results = $this->db->select('chewi_views','type_id = :type_id AND type = :type AND cast(date AS DATE) = CURDATE()', $bind);
        $totalRows = count($results);
		if($results === FALSE){ $totalRows = '0'; }
        return $totalRows;
    }

And in the database it stores data like this:
image

My end result should be something like this but Total Views Weekly.

image
image1170×551 9.7 KB

My sample non php code is:

<div id="chart_div" style="width: 100%; height: 500px;"></div>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['bar']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Week', 'Signup'],
          ['2018/10/15',  0],
          ['2018/10/16',  0],
          ['2018/10/17',  0],
          ['2018/10/18',  0],
          ['2018/10/19',  4],
          ['2018/10/20',  4],
          ['Today',  227]
        ]);

         var options = {
          chart: {
            title: 'Total Signup',
            //subtitle: 'Sales, Expenses, and Profit: 2014-2017',
          }
        };

        var chart = new google.charts.Bar(document.getElementById('chart_div'));

        chart.draw(data, google.charts.Bar.convertOptions(options));
      }
    </script>
2

When you query the data for each day, do you retrieve that information from the database sorted by the date, or do you loop through the days in the relevant period and retrieve data for each day? For the former approach, I could see how it would just miss out any day where there is no information in the database, because it would just not return any information for the day concerned.

One way would be to scan the array that you pass into the chart function to look for missing dates and insert them with a zero total. Another way would be to get the daily information from a loop starting on the first day of your desired period - that way the loop controls which day is being added, rather than the results from an earlier query.

3

@jack55. What is and what isn’t working?

4

Basically, right now my queries get all the data from 30 days ago but it only gets the sql rows. I need to somehow fill in the gaps.where there is no data so as you can see in the table pic above there is a date for each view, I have done a query that gets all the data that is 30 days or less but then i have gaps in the query

Here is the plain text stats which all work great, they get the daily views, weekly & total. I need help converting this so that it works in a Google Chart.
image

This is the graph generated ( a load of rubbish data wise)
image

Here is all my database sql code:

    public function countViews($type_id, $type) // Count all views for specific type
    {
		$bind = [
			':type_id' => $type_id,
			':type' => $type
		];
        $results = $this->db->select('chewi_views','type_id = :type_id AND type = :type', $bind);
        $totalRows = count($results);
		if($results === FALSE){ $totalRows = '0'; }
        return $totalRows;
    }

    public function countDailyViews($type_id, $type) // Count all daily views for specific type
    {
		$bind = [
			':type_id' => $type_id,
			':type' => $type
		];
        $results = $this->db->select('chewi_views','type_id = :type_id AND type = :type AND cast(date AS DATE) = CURDATE()', $bind);
        $totalRows = count($results);
		if($results === FALSE){ $totalRows = '0'; }
        return $totalRows;
    }
    
    public function getWeeklyViews($type_id, $type) // Count 7 days views for specific type
    {
		$bind = [
			':type_id' => $type_id,
			':type' => $type
		];
        $results = $this->db->select('chewi_views','type_id = :type_id AND type = :type AND date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)', $bind);
        return $results;
    }

Here is my Google Chart code:

        //get view data
        $weekdayview = $this->start->getWeeklyViews($id, '2');
        foreach ($weekdayview as $row) {
            $graphData .= "['".$row{'date'}."',".$row{'id'}."],"; //need to count views, all this does it display each row in table whereas i need it to count them and also show 0 for dates missing.
        }

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

function drawChart() {
  var data = google.visualization.arrayToDataTable([
          ['Week', 'Views'],
      <?php echo $graphData; ?>
        /*  ['17',  0],
          ['18',  0],
          ['19',  4],
          ['20',  4],
          ['21',  228],
          ['22',  2],
          ['23',  0]*/
  ]);
    
                var options = {
              title: 'Weekly Viewing',
              //hAxis: {title: 'Day',  titleTextStyle: {color: '#333'}},
                    height: 400,
                    width: 600,
              vAxis: {minValue: 0},
                    theme: 'material'
            };

     var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
    chart.draw(data, options);
  window.addEventListener('resize', drawChart, false);
}
    </script>

Thanks again!

5

I would be modifying this code:

//get view data
        $weekdayview = $this->start->getWeeklyViews($id, '2');
        foreach ($weekdayview as $row) {
            $graphData .= "['".$row{'date'}."',".$row{'id'}."],"; //need to count views, all this does it display each row in table whereas i need it to count them and also show 0 for dates missing.
        }

Like this pseudo-code, because I can’t remember how to do dates and don’t want to look it up

copyday = date of start of report (I.e. today minus seven days, like your query)
$weekdayview = $this->start->getWeeklyViews($id,'2'); // build the array
foreach($weekdayview as $row) { 
  // pseudo-code here
  while (copyday < $row['date'] ) {  // but compare date portion only
    $graphData .= "['" . copyday . "',0]";  // add a blank entry into the array
    copyday = copyday + 1 day 
    }
   $graphData .= "['".$row{'date'}."',".$row{'id'}."],";  // append the result
   copyday = $row['date'];  // but just the date portion 
}

What I’m intending to do there is - every time you get another row from the results to stick in the graph data array, you first check to see where the independent copy of the date is. If it’s before the date of the row data that means there is one or more day missing in the results, so you do a quick loop to fill in the blanks with zero rows, and each time you increment the copy date by one day.

You’ll need to compare just the date portion of your results, the time will confuse it.

1 Like
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.