Loop through multiple rows as labels and data in chart.js and PHP

Question

Using chart.js I want to create a graph of a given stock price. The labels and data are fetched using the data from $select_query. Here I create a dynamic function that gets the ticker name from the URL: https://signal-invest.com/tick/?ticker=LOW

$select_query = $con->prepare("SELECT Date_buy, Name_buy, Price_buy FROM daily_buy_orders WHERE Name_buy = ?");
$select_query->bind_param('s', $ticker); // 's' specifies the variable type => 'string'
$select_query->execute();

I tried to loop through the rows using the while loop but failed.

<canvas id="myChart" style="border: 1px solid black; margin: 25px 25px" height="300"></canvas>
                <script>
                var ctx = document.getElementById('myChart').getContext('2d');
                var myChart = new Chart(ctx, {
                    type: 'line',
                    data: {
                        //labels: ['Red', 'Blue', 'Yellow', 'Green', 'Purple', 'Orange'],
                        labels: [<?php
                          $get_dates = $select_query->get_result();
                            while ($rows = mysqli_fetch_assoc($get_dates)) {
                            $dates = $rows["Date_buy"];
                            echo ''.$dates.'';
                            }
                            ?>],
                        datasets: [{
                            label: '# of Votes',
                            //data: [12, 19, 3, 5, 2, 3],
                            data: [<?php
                            $get_prices = $select_query->get_result();
                            while ($rowss = mysqli_fetch_assoc($get_prices)) {
                            $prices = $rowss["Price_buy"];
                            echo ''.$prices.'';
                            }
                            ?>],
                            backgroundColor: 'rgba(255, 99, 132, 0.2)',
                            borderColor: 'rgb(75, 192, 192)',
                            borderWidth: 1
                        }]
                    },
                    options: {
                        scales: {
                            y: {
                                beginAtZero: true
                            }
                        }
                    }
                });
                </script>

Above code messes with my table below. When creating the tables the while loop actually works. I tried to use the same thinking when doing the while loops for the labels and data in chartjs, didn't work though.

           <table style="width:50%" border="solid">
            <tr>
            <th>Name</th>
            <th>Price</th>
            <th>Date of signal</th>
             </tr>
                <?php
                $result = $select_query->get_result();
                while ($row = mysqli_fetch_assoc($result)) {
                $name_buy = $row["Name_buy"];
                $price_buy = $row["Price_buy"];
                $date = $row["Date_buy"];
              echo buy_table($name_buy, $price_buy, $date);
                }
                ?>
            </table>

I was going to suggest the same as the answer just posted below.. you can use json_encode or also you can try with labels:['<?php echo implode("','", $dates); ?>'], — Marcelo Viana
– Marcelo Viana, Commented Apr 5, 2021 at 7:37
I found this example where they first make the variabls arrays and then append data into them. — doomdaam
– doomdaam, Commented Apr 5, 2021 at 7:38

doomdaam · Accepted Answer · 2021-04-06 15:46:01Z

Trying to dynamically build an array of JS with PHP code is never a good idea. Instead, build the array in PHP and the use json_encode to pass it to JS (or even better, use a templating engine and pass it in with data attributes, but's that a whole other topic)

Also, you are trying to loop the result multiple times, which I'm not sure is possible. It's been a long time since I used the native mysqli functions.

First, fetch the data and build the arrays you need to pass to Chart.js in PHP:

$statement = $con->prepare("SELECT Date_buy, Name_buy, Price_buy FROM daily_buy_orders WHERE Name_buy = ?");
$statement->bind_param('s', $ticker); // 's' specifies the variable type => 'string'
$statement->execute();
$result = $statement->get_result();

$dates = [];
$prices = [];
while ($row = $result->fetch_assoc()) {
    $dates[] = $row['Date_buy'];
    $prices[] = $row['Price_buy']
}

Now you can pass this to the chart in a much cleaner way:

var myChart = new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?php json_encode($dates); ?>,
        datasets: [{
            label: '# of Votes',
            data: <?php json_encode($prices); ?>,
        }]
    }
}

I haven't tested this, but it gives you the idea

EDIT: This returns the following output: "LOWArrayLOWArrayLOWArrayLOWArray". Where Name_buy is LOW, meaning the second print statement is bugged. How to solve this?

    $arrDates = array();
    $arrPrices = array();
    $arrNames = array();
    // $dates = [];
    // $prices = [];
    // $names = [];
    $result = $select_query->get_result();
    while ($row = mysqli_fetch_assoc($result)) {
        $arrDates[] = $row['Date_buy'];
        $arrPrices[] = $row['Price_buy'];
        $arrNames[] = $row['Name_buy'];
        print($row['Name_buy']);
        print($arrNames);
        }

I edited your entry by adding some results of your suggestion. It fails to appen the data to my array variables.

Collectives™ on Stack Overflow

Loop through multiple rows as labels and data in chart.js and PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related