How to display data from database into line graph using php and mysql?-CodePudding

I want to display monthly revenues from a database on a line graph. Currently there are two rows in the database. First month: 250,000. Second month: 268600. My problem is that the data is not displayed on the graph as expected. The month values are below each other and there is only one of the revenue values(250000). But on the graph the value 268600 is plotted. Image of the graph:image of the graph Database image:database image

How can I display the data correctly on the graph? So 1 month : 250000. 2 months : 268600. I use chart.js for the chart.

Here is my code:

<?php
session_start();
require_once('connection.php');
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">   
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <link rel="stylesheet" href="../css/statistics.css">
    <title>statistics</title>
</head>

<body>
   

            <?php
            session_start();
            require_once('connection.php');
            ?>

            <?php
            $id = $_SESSION['id'];
            $sql = "SELECT month, revenue FROM revenue_per_month WHERE user_id = $id";

            $result = mysqli_query($conn, $sql);
            if (mysqli_num_rows($result) > 0) {
                while ($row = mysqli_fetch_assoc($result)) {

                    $revenue = $row["revenue"];
                    $month = $row["month"];
                }
            }

            $revenue_value = array();
            $month_value = array();
            foreach ($result as $row) {
                $revenue_value[] = $row["revenue"];
                $month_value[] = $row["month"];
            }


            ?>


            <div >
                <canvas id="myChart"></canvas>
            </div>
            <!--todo=============== script ===============-->

            <script>
                const data = {
                    labels: [<?php echo json_encode($month_value) ?>],
                    datasets: [{
                        label: 'Revenue value:',
                        backgroundColor: '#20e0307a',
                        borderColor: '#04aa1a',
                        data: [<?php echo json_encode($revenue_value) ?>],
                        borderWidth: 1,
                    }]
                };

                var chartEl = document.getElementById("myChart");
                chartEl.height = 250;
                const config = {
                    type: 'line',
                    data: data,
                    options: {
                        plugins: {

                            title: {
                                display: true,
                                text: 'Revenues'
                            },
                            legend: {
                                display: true,
                                position: 'bottom'
                            }
                        }
                    }
                };
            </script>
           

 <script>
                const myChart = new Chart(
                    document.getElementById('myChart'),
                    config
                );
            </script>


  
</body>

</html>

CodePudding user response：

Assuming that you want a single dataset, you need a single array (not two). So change to :

foreach ($result as $row) {
    $revenue_value[] = [
        'x' => $row["month"],
        'y' => $row["revenue"]
    ];
}

Then in your javascript 'data' variable, remove the square brackets :

            const data = {
                datasets: [{
                    label: 'Revenue value:',
                    backgroundColor: '#20e0307a',
                    borderColor: '#04aa1a',
                    data: <?= json_encode($revenue_value) ?>,
                    borderWidth: 1,
                }]
            };