OctoberCMS - How to retrieve data from database into backend chart

Question

I'm creating a chart for my booking data but based on the sample code here I declare the value and label myself. So what I'm trying to do is creating a chart based on my database but I'm not sure how to retrieve the label and value from my own database? Is there any example? Or anyone can help me?

<?php

    // our data
    $chartData = [
        ['label' => '1st Month', 'value' => 10],
        ['label' => '2nd Month', 'value' => 6],
        ['label' => '3rd Month', 'value' => 3],
        ['label' => '4th Month', 'value' => 20],
        ['label' => '6th Month', 'value' => 20],
        ['label' => 'nth Month', 'value' => 15],
    ];

    // preparing data for chart
    $values = $labels = [];
    foreach ($chartData as $key => $data) {
        $values[] = [$key, $data['value']];
        $labels[] = [$key, $data['label']];
    }

    $labelDataInJson = json_encode($labels);
    // removing first and last [, ] so it can be valid data for chart
    $chartDataInJson = strtr(json_encode($values), ['[[' => '[', ']]' => ']']);

?>

    <div class="scoreboard" style="padding: 2rem;padding-top: 0;">
    <h3>Booking Report</h3>
    <div 
        data-control="chart-line" 
        data-time-mode="weeks" 
        style="height: 200px;width: 100%;"
        data-chart-options='xaxis: {mode: "none", ticks: <?= $labelDataInJson ?>}'
    >
        <span 
            data-chart="dataset" 
            data-set-color="#008dc9" 
            data-set-name="Visits"
            data-set-data='<?= $chartDataInJson ?>'
        </span>
    </div>
</div>

I think on top you can just try to call your model and fetch data may be. — Hardik Satasiya
can you share details about what you trying to show and what is your model name, table details — Hardik Satasiya
My model name is booking. For my table details I have booking id, booking status, booking payment type, booking detail, created at and so on. So what I'm trying to show basically is booking total were made based on the date/month/year. — Snowball

Hardik Satasiya Hardik Satasiya · Accepted Answer · 2020-07-03T08:05:01

You need to arrange your data correctly, just follow below code to arrange your data.

Here I used User's modal in your code you can replace it with Booking table. and achieve same result.

// we will show last 7 (6 day + 1 current day) day's stats
$sixDayAgo = \Carbon\Carbon::now()->subDay(6);
$users = RainLab\User\Models\User::whereDate('created_at', '>=', 
                                      $sixDayAgo->toDateString())->get();

$period = \Carbon\CarbonPeriod::create($sixDayAgo, \Carbon\Carbon::now());

$chartData = [];
foreach ($period as $date) {
    $val = ['label' => $date->format('Y-m-d'), 'value' => 0];
    foreach ($users as $user) {
      if($date->format('Y-m-d') == $user->created_at->format('Y-m-d')) {
        $val['value']++;
      }
    }
    $chartData[] = $val;
}
// dd($chartData);

once you get your $chartData then you can show chart

follow https://tutorialmeta.com/october-cms/how-to-use-charts-in-octobercms this tutorial to show $chartData as chart.

Result

if any doubts please comment.

OctoberCMS - How to retrieve data from database into backend chart

1 Answers

Result