How many people register on a day of the week
Hi I want to create a diagram for my project using chatjs. I have a table of users and when a user logs in to my site, the time logged is a datetime format like this 2018-03-02 10:35:25
. I want to do it with this chat DEMO
I need to write php code to show the graph, for example:
$months = array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday");
$monthvalues = array();
foreach ($months as $month) {
$monthvalues[$month] = 0;
}
$result = mysqli_query($db,"SELECT registerTime, count(*) FROM users group by WEEKDAY(registerTime)") or die(mysql_error($db));
while ($row = mysqli_fetch_array($result, MYSQL_NUM)) {
$monthvalues[$row[0]] = (int)$row[1];
}
and i print it like this
// Data
var data = {
labels: <?=json_encode($months);?>,
datasets: [{
label: "My Second dataset",
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: <?=json_encode(array_values($monthvalues));?>
}]
};
The above query only gives one log report. I cannot show this on a graph. I need a conclusion that I can reflect on a weekly basis.
The problem is, I can't figure out how to make graphs with code. Can anyone help me by providing an example in this question?
the result should be data: [28, 48, 40, 19, 86, 27, 90]
like this
january = 28, February = 48, ...
source to share
If you want to return an array you have to use GROUP BY like this
SELECT COUNT(registerTime) as `count` FROM users WHERE DATE(registerTime) = CURDATE() GROUP BY MONTH(registerTime)
And you can add the month number in the output to create shortcuts
SELECT COUNT(registerTime) as `count`,MONTH(registerTime) as `month` FROM users WHERE DATE(registerTime) = CURDATE() GROUP BY MONTH(registerTime)
source to share