duanjing7459
duanjing7459
2015-06-13 10:11

图表包含数据库的每日结果

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douben6670 douben6670 6年前

    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

    点赞 评论 复制链接分享

相关推荐