Armitage2k Armitage2k - 1 month ago 7
Javascript Question

Chart.js - cannot fetch result from MySQL via PHP

I am trying to populate a chart via the ChartJS plugin with data from my MySQL database, but while doing so I am running into a


mysqli_fetch_assoc(): Couldn't fetch mysqli_result in ...


error.Since I am using json_encode I tried to adjust the fetch array but cant seem to figure this one out.

Any help would be much appreciated.

<div class="box-body">
<div class="chart">

<canvas id="canvas_bar" style="height:250px"></canvas>

<?php
// Start MySQLi connection
$db = new MySQLi($dbhost,$dbuser,$dbpass,$dbname);
if ($db->connect_errno) { echo "Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error; }

// count all records per month
$sql = "SELECT LOWER(MONTHNAME(mod_date)) AS mdate, count(*) AS cnt FROM qci_modreport GROUP BY LOWER(MONTHNAME(mod_date))";

if (!($result)) {
print "ERROR, something wrong with the query.";
} else {
$output = array();

while ($row = mysqli_fetch_assoc($result)) {
$output[$row['mdate']] = $row['cnt'];
}
print (json_encode($output));
}
?>

<!-- chartJS 1.0.1-->
<!-- <script src="./plugins/chartjs/Chart.js"></script> -->
<script src="../../plugins/chartjs/Chart.min.js"></script>
<script>
var barChartData = {
labels: <?php echo json_encode(array_keys($output)); ?>,
datasets: [
{
fillColor: "#03586A", //rgba(151,187,205,0.5)
strokeColor: "#03586A", //rgba(151,187,205,0.8)
highlightFill: "#066477", //rgba(151,187,205,0.75)
highlightStroke: "#066477", //rgba(151,187,205,1)
data: <?php echo json_encode(array_values($output)); ?>
}]
};
$(document).ready(function () {
new Chart($("#canvas_bar").get(0).getContext("2d")).Bar(barChartData, {
tooltipFillColor: "rgba(51, 51, 51, 0.55)",
responsive: true,
barDatasetSpacing: 6,
barValueSpacing: 5
});
});



</script>

</div>
</div>
<!-- /.box-body -->

Answer

$result is not defined. You should use

if (!($result = $db->query($sql1))) { ...

or

$result = $db->query($sql1);

and only after you do

if (!$result) { ...

and

while ($row = mysqli_fetch_assoc($result)) { ...

Comments