The use of query and execute consecutively was confusing and quite possibly wrong - the execute method is used to execute a prepared statement but you did not create such a prepared statement. I began just formatting your code so I could work through it to see if I could identify an error and ended up with the following which might, or might not, be of use. I put comments in to help a little.
<?php
/* column headers for chart datatable */
$payload=array( "['Date', 'Post']" );
/* create sql and prepare statement */
$sql='select `date`, count(`idpv`) as `count` from `pageview` group by day( `date` ) desc';
$stmt=$db->prepare( $sql );
if( $stmt ){
/* execute sql statement */
$result=$stmt->execute();
$stmt->store_result();
/* If there are results, process recordset */
if( $result && $stmt->num_rows > 0 ){
/* bind results */
$stmt->bind_result( $date, $count );
/* fetch recordset */
while ( $stmt->fetch() ) {
$date=substr( $date, 0, -9 );
$payload[]="[ '$date', $count ]";
}
$stmt->free_result();
$stmt->close();
}
}
$db->close();
/*
if there were no results this will only have column headers for datatable
this will be used by javascript function
*/
$srcdata='['.implode( ',', $payload ).']';
?>
<html>
<head>
<meta charset='utf-8' />
<title>Google charts & mysqli</title>
<!--
assumed here that the required google api scripts have been loaded
-->
<script>
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback( drawChart );
function drawChart() {
/*
echo the source data as a javascript variable before using in datatable
*/
var srcdata=<?php echo $srcdata; ?>
if( srcdata.length > 1 ){
/*
There are more than just headers in src data array
so add to datatable and create chart
*/
var data = google.visualization.arrayToDataTable( srcdata );
var options = {
title: 'Page Views'
};
var chart = new google.visualization.PieChart( document.getElementById('Views') );
chart.draw( data, options );
}
}
</script>
</head>
<body>
<div id="Views" style="width: 500px; height: 500px; float: left;"></div>
</body>
</html>
Tested demo, based upon a rudimentary emulation of your pageview table with some random example data inserted.
mysql> describe pageview;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| date | datetime | NO | | NULL | |
| idpv | mediumint(9) | NO | | 0 | |
+-------+--------------+------+-----+---------+-------+
and a snippet of the dummy data
mysql> select * from pageview limit 10;
+---------------------+------+
| date | idpv |
+---------------------+------+
| 2015-01-11 00:00:00 | 54 |
| 2015-01-17 00:00:00 | 55 |
| 2015-01-20 00:00:00 | 121 |
| 2015-02-07 00:00:00 | 42 |
| 2015-03-08 00:00:00 | 57 |
| 2015-03-09 00:00:00 | 122 |
| 2015-04-01 00:00:00 | 5 |
| 2015-04-12 00:00:00 | 39 |
| 2015-04-19 00:00:00 | 98 |
| 2015-04-20 00:00:00 | 90 |
+---------------------+------+
-
<?php
include __DIR__ . '/db.php'; #pertinent to my system!
/* column headers for chart datatable */
$payload=array( "['Date', 'Post']" );
/* create sql and prepare statement */
$sql='select
`date`,
count(`idpv`) as `count`
from `pageview`
group by day( `date` )
order by date( `date` ) desc';
$stmt=$db->prepare( $sql );
if( $stmt ){
/* execute sql statement */
$result=$stmt->execute();
$stmt->store_result();
/* If there are results, process recordset */
if( $result && $stmt->num_rows > 0 ){
/* store and bind results */
$stmt->bind_result( $date, $count );
/* fetch recordset */
while ( $stmt->fetch() ) {
$payload[]="[ '$date', $count ]";
}
$stmt->free_result();
$stmt->close();
}
}
$db->close();
/*
if there were no results this will only have column headers for datatable
this will be used by javascript function
*/
$srcdata='['.implode( ',', $payload ).']';
?>
<html>
<head>
<meta charset='utf-8' />
<title>Google charts & mysqli</title>
<!--
assumed here that the required google api scripts have been loaded
-->
<script src='https://www.gstatic.com/charts/loader.js'></script>
<script>
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback( drawChart );
function drawChart() {
/*
echo the source data as a javascript variable before using in datatable
*/
var srcdata=<?php echo $srcdata; ?>
if( srcdata.length > 1 ){
/*
There are more than just headers in src data array
so add to datatable and create chart
*/
var data = google.visualization.arrayToDataTable( srcdata );
var options = {
title: 'Page Views'
};
var chart = new google.visualization.PieChart( document.getElementById('Views') );
chart.draw( data, options );
}
}
</script>
</head>
<body>
<div id="Views" style="width: 500px; height: 500px; float: left;"></div>
</body>
</html>
