CODEWITHSUNDEEP
javascriptphpmysqljsoncharts
kentor
kentor

Reputation: 18524

Charts with daily results from Database

I want to create a chart showing "userlogins" of an application each day in the month. Further I want to show them in my charts depending on their usergroup (paid or free user).

The problem: I get all those userlogins from my database. Unforunately there are days where no one (could) logged in and then there are no logins for a whole day (also possible that only free users couldn't login). This would cause a resultset of my SQL query with a missing date, which would lead to problems with my Chart.

Instead of:

2015-06-11: 50
2015-06-12: 0
2015-06-13: 108

I would get: 

2015-06-11: 50
2015-06-13: 108

My data.php which does the SQL queries:

$sql = "SELECT count(distinct(member_id)) as logins, DATE(date_seen) FROM botuser_statistics WHERE subscriber=0 GROUP BY DATE(date_seen)";
$rows = $db->query($sql);

$logins = array();
$logins["type"]["dates"] = array();
$logins["type"]["logins"] = array();

foreach($rows as $row){
    $logins["free"]["dates"][] = $row['DATE(date_seen)'];
    $logins["free"]["logins"][] = $row['logins'];
}

$sql = "SELECT count(distinct(member_id)) as logins, DATE(date_seen) FROM botuser_statistics WHERE subscriber=1 GROUP BY DATE(date_seen)";
$rows = $db->query($sql);

foreach($rows as $row){
    $logins["paid"]["dates"][] = $row['DATE(date_seen)'];
    $logins["paid"]["logins"][] = $row['logins'];
}
echo json_encode($logins);

This is my JavaScript drawing the chart:

jQuery.getJSON("data.php", function (result) {
    datesfree = result["free"]["dates"];
    loginsfree = result["free"]["logins"];
    loginspaid = result["paid"]["logins"];

    // Get context with jQuery - using jQuery's .get() method.
    var ctx = $("#userlogins").get(0).getContext("2d");

    var data = {
        labels: datesfree,
        datasets: [
            {
                label: "Free Users",
                fillColor: "rgba(220,220,220,0.2)",
                strokeColor: "rgba(220,220,220,1)",
                pointColor: "rgba(220,220,220,1)",
                pointStrokeColor: "#fff",
                pointHighlightFill: "#fff",
                pointHighlightStroke: "rgba(220,220,220,1)",
                data: loginsfree
            },
            {
                label: "Paid Users",
                fillColor: "rgba(151,187,205,0.2)",
                strokeColor: "rgba(151,187,205,1)",
                pointColor: "rgba(151,187,205,1)",
                pointStrokeColor: "#fff",
                pointHighlightFill: "#fff",
                pointHighlightStroke: "rgba(151,187,205,1)",
                data: loginspaid
            }
        ]
    };
});

This is how my chart looks like: enter image description here

Notice the blue point (paid users), on 12th of June - It should be zero on the 12th of June and it should be 20 on the 13th of June but it isn't, because the SQL query hasn't even returned that date. How should I solve this?

Upvotes: 0

Views: 317

Answers (1)

nivasan89
nivasan89

Reputation: 144

You need to have a lookup for the list of dates, like a table or something which has all the dates that you want to plot. And then, join that column to get count of logins for each date (use IFNULL(login_count,0) to consider zero for the dates that are missing). This way you will have the recordset along with dates for which the count is zero.

table_date_lookup (this table will have the lookup of dates) join table_botuser_statistics

Edit - 1 :-

Create a temporary table (or view) with the dates listed. Table name - tbl_dates

~~~~~~~~~
dates
~~~~~~~~~
2015-05-13
2015-05-14
2015-05-15
~~~~~~~~~

And then use a query like the below one -

SELECT A.dates , IFNULL(B.logins,0) FROM tbl_dates AS A
LEFT JOIN
(SELECT count(distinct(member_id)) as logins, DATE(date_seen) FROM botuser_statistics WHERE subscriber=0 GROUP BY DATE(date_seen)) AS B
ON A.dates = B.date_seen

Upvotes: 1

Related Questions