Member Avatar for nishita_1

please help me. below chart.js works very well but here show all date data from database but i want only last fifteen days data to show. How can i show last 15 days sale reports. i am attach code below. thanks

chart_edit_result.PNG

I an developed an chart.js for my daily sale table from sql . every think works good but chart show all data but i need last 15 days data as well. what can i do now.

<?php
    /* Database connection settings */
    $host = 'localhost';
    $user = 'root';
    $pass = '';
    $db = 'store';
    $mysqli = new mysqli($host,$user,$pass,$db) or die($mysqli->error);

    $data1 = '';
    $data2 = '';

    //query to get data from the table
    $sql = "SELECT order_date, SUM(grand_total) AS total_grand  FROM orders GROUP BY order_date ";

    $result = mysqli_query($mysqli, $sql);

    //loop through the returned data
    while ($row = mysqli_fetch_array($result)) {

        $data1 = $data1 . '"'. $row['order_date'].'",';
        $data2 = $data2 . '"'. $row['total_grand'] .'",';
    }

    $data1 = trim($data1,",");
    $data2 = trim($data2,",");
?>

<!DOCTYPE html>
<html>
    <head>

        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <script type="text/javascript" src="Chart.bundle.min.js"></script>

        <style type="text/css">         
            body{
                font-family: Arial;
                margin: 80px 100px 10px 100px;
                padding: 0;
                color: white;
                text-align: center;
                background: #555652;
            }

            .container {
                color: #E8E9EB;
                background: #222;
                border: #555652 1px solid;
                padding: 10px;
            }
        </style>

    </head>

    <body>     
        <div class="container"> 

            <canvas id="chart" style="width: 100%; height: 65vh; background: #222; border: 1px solid #555652; margin-top: 10px;"></canvas>

            <script>
                var ctx = document.getElementById("chart").getContext('2d');
                var myChart = new Chart(ctx, {
                type: 'line',
                data: {
                    labels: [<?php echo $data1; ?> ],
                    datasets: 
                    [{
                        label: 'Data 1',
                        data: [<?php echo $data1; ?>],
                        backgroundColor: 'transparent',
                        borderColor:'rgba(255,99,132)',
                        borderWidth: 3
                    },

                    {
                        label: 'Sales Total ',
                        data: [<?php echo $data2; ?>, ],
                        backgroundColor: 'transparent',
                        borderColor:'rgba(0,255,255)',
                        borderWidth: 3  
                    }]
                },

                options: {
                    scales: {scales:{yAxes: [{beginAtZero: false}], xAxes: [{autoskip: true, maxTicketsLimit: 20}]}},
                    tooltips:{mode: 'index'},
                    legend:{display: true, position: 'top', labels: {fontColor: 'rgb(255,255,255)', fontSize: 16}}
                }
            });
            </script>
        </div>

    </body>
</html>
  • 3 Contributors
  • 3 Replies
  • 4K Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by pandglobal
Member Avatar for rproffitt

My initial thought is about line 12. If you want a certain date range I'd consider adding a WHERE date is in the range you desire.

If fact, the first example at https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html shows this idea.

commented: Dear sir, thank you for your co-operation. sir i want auto range. sale every day update then last 15 days automatically show. then what should i do. +0
Member Avatar for rproffitt

You write you want it to be the last 15 days. Now that you know about the WHERE clause and have your documentation on date and time functions you only need to work out the code for this. To me this would be something like this but I leave the actual coding to you.

Take your line 13, add WHERE then statement/code something like order_date > (NOW - 15 days).

Member Avatar for pandglobal

The problem is quite a simple one, just limit the number of returned data from your sql query to 15.

Limit = 15

So the database will only return last 15days details as data.

Your query should look like this

//query to get data from the table
$sql = "SELECT order_date, SUM(grand_total) AS total_grand  FROM orders GROUP BY order_date LIMIT = 15 ";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.