I'm blocked on this which for sure is a simple thing to do. I have a table with a list of users with date logins, example:
---------------------
date | user
---------------------
2013-07-08 | Carl
2013-07-09 | Mark
2013-07-09 | Robert
2013-08-09 | Robert
2013-08-09 | Saul
2013-09-09 | Paula
2014-01-09 | Mark
2014-02-09 | Robert
and so.
So I do a
SELECT DISTINCT(user) FROM logins;
to get a full list of users. Now what I want is to get a result with the total count of logins (each year) of each user, and if the user didn't log in during the past year, still add it to the results with a 0 count. The important thing is to respect user list.
If I do this:
SELECT user, year(date) as year, COUNT(*) as count
FROM logins
GROUP BY user, year
ORDER BY year ASC, count DESC;
I will get the right count for users logged in 2013 and 2014 and so but the ones didn't log-in will be excluded, and I need to include them with a 0 count.
No matter if the solution is MySQL, PHP or even JS. just need the array to latter on encode as JSON.
So far I tried with MySQL with no luck, and now I'm trying with PHP getting the list of users in one array and the above results in another array but also no luck on a fast way to do it or I'm lacking of ideas because my psychiatric treatment pills (yes, my head is oversaturated).
That's why I need help.
THANKS FOR SHARING ALL YOUR KNOWLEDGE AS ALWAYS!
EDIT
As asked by @Rick, here is the table in question and the real fields I'm using are "isp" and "fecha_login"
CREATE TABLE `login_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`personal_id` int(11) DEFAULT NULL,
`responsable_id` int(11) DEFAULT NULL,
`nombre` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`fecha_login` datetime DEFAULT NULL,
`ip` int(11) unsigned DEFAULT NULL,
`isp` varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`ciudad` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`provincia` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`pais` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`dispositivo` varchar(100) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL,
`datosmoviles` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20991 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
and the explain select:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | ALL | NULL | NULL | NULL | NULL | 19690 | Using temporary; Using filesort | |
| 1 | PRIMARY | ALL | NULL | NULL | NULL | NULL | 19690 | Using join buffer (flat, BNL join) | |
| 1 | PRIMARY | l | ALL | NULL | NULL | NULL | NULL | 19690 | Using where; Using join buffer (incremental, BNL join) |
| 3 | DERIVED | login_history | ALL | NULL | NULL | NULL | NULL | 19690 | Using temporary |
| 2 | DERIVED | login_history | ALL | NULL | NULL | NULL | NULL | 19690 | Using temporary |
My Answer:
To achieve the desired result, you can use a combination of MySQL and PHP. Here's a step-by-step guide on how to do it:
1. First, you need to get a distinct list of users from the `login_history` table:
php
$users = [];
$query = "SELECT DISTINCT isp FROM login_history";
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
$users[] = $row['isp'];
}
2. Next, you need to get the count of logins for each user for each year, including users who didn't log in with a count of 0:
php
$login_counts = [];
$query = "SELECT isp, YEAR(fecha_login) AS year, COUNT(*) AS count
FROM login_history
GROUP BY isp, year
ORDER BY year ASC, count DESC";
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
$login_counts[$row['isp']][$row['year']] = $row['count'];
}
// Fill in missing years with 0 count for each user
foreach ($users as $user) {
foreach (range(2013, date('Y')) as $year) {
if (!isset($login_counts[$user][$year])) {
$login_counts[$user][$year] = 0;
}
}
}
3. Finally, you can encode the `$login_counts` array as JSON for further processing:
php
$json_data = json_encode($login_counts);
echo $json_data;
This code will give you a JSON object where each user is a key, and the value is an array of login counts for each year. Users who didn't log in during a specific year will have a count of 0.
Make sure to replace `$mysqli` with your MySQL connection object. Let me know if you need further assistance!
Rate this post
4 of 5 based on 9821 votesComments