Chart.js - cannot fetch result from MySQL via PHP

Question

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

You have three queries and you arbitrarily execute only one of them. Is that right? — tadman
– tadman, Commented Nov 2, 2016 at 8:19
ah, sorry, you can ignore that part. I was just playig around with the sql statement since I couldnt get any data at all in the beginning. I just need one query to count the records of each month and group them. — Armitage2k
– Armitage2k, Commented Nov 2, 2016 at 8:21
Please simplify your code to the minimum that demonstrates the error you are having. Asking us to ignore anything you have posted and leaving in commented out code experiments is not showing respect for the people you are asking for help from. Give the minimum amount of code that generates the error you want help with. — jwpfox
– jwpfox, Commented Nov 2, 2016 at 8:27

Gesha · Accepted Answer · 2016-11-02 08:50:31Z

1

$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)) { ...

edited Nov 2, 2016 at 8:50

answered Nov 2, 2016 at 8:23

Gesha

263 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

tadman Over a year ago

Doing an assignment inside an if is a great way to annoy your co-workers as that's usually a mistake, and often a big one. This is usually best done on another line, then tested subsequently with an if.

Armitage2k Over a year ago

of course it had to be something simple... I adjusted the code and get a $result, but the chart is not populated with labels and data. Updated the code to reflect changes.

Gesha Over a year ago

@Armitage2k I think, you incorrectly fills $output. May be you should $output[$row['mdate']] = $row[$row['cnt']] instead of array_push($output, $row);.

Gesha Over a year ago

@Armitage2k So $sql should be

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

Armitage2k Over a year ago

@Gesha that throws me an undefined index error for cnt and mdate and returns $output as null.

|

Collectives™ on Stack Overflow

Chart.js - cannot fetch result from MySQL via PHP

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related