PNewCode Posted February 1 Share Posted February 1 Hello. I have a few pages that count total entries in a db that work fine. And I have some that count entries WHERE just fine. But what I'm not able to figure out is how to echo a count based on a few variables that are stated after the initial SQL statement. I want to be able to have (for example)echo = 'Okay = 10, Pending = 5, Expired = 2'; and it based on if ($diffdate->m >= 3 OR $diffdate->y >= 1) { $expire = "EXPIRED"; } elseif (empty($visited)) { $expire = "PENDING"; } else { $expire = "OKAY"; } Any thoughts? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted February 1 Share Posted February 1 I see that you have a few lines that analyzes pieces of a date value and issues a known response. Now where it the 'counting' supposed to and what is the set of data being counted? Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 1 Author Share Posted February 1 (edited) 11 minutes ago, ginerjm said: I see that you have a few lines that analyzes pieces of a date value and issues a known response. Now where it the 'counting' supposed to and what is the set of data being counted? The database table has colums "created_at" and "Visited" The code above shows each one correctly as exired = hasn't logged in at all since they created the account and it's been over a year and 3 months pending = hasn't logged in at all since they created the account and it's been within 3 months since created okay = has logged in within 3 months since last login Created_at = created account Logged in = visited So I want to use that information to count (the code in the original post shows each one) how many are Expired how many are Pending and now many are Okay Edited February 1 by PNewCode Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 What is the query tht is getting this data? Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 1 Author Share Posted February 1 40 minutes ago, Barand said: What is the query tht is getting this data? $sql = "SELECT id,unique_id,fname,date_format(visited, '%b %e %Y %h:%i%p'),date_format(created_at, '%b %e %Y %h:%i%p'),visited,created_at FROM users ORDER BY visited DESC"; This is important to have as a ORDER BY because the list as a whole starts with users most recent logged in to last Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 Do you update the visited date each time the user visits, or is a new record created each time they visit? Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 1 Author Share Posted February 1 6 minutes ago, Barand said: Do you update the visited date each time the user visits, or is a new record created each time they visit? Yes each time they login, a new date enters in "visited" Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 Which? Update, or Insert new record? Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 1 Author Share Posted February 1 Just now, Barand said: Which? Oh I'm sorry. It's update. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted February 1 Solution Share Posted February 1 This query should do it SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending , SUM(visited IS NOT NULL) as ok FROM users; Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 1 Author Share Posted February 1 16 minutes ago, Barand said: SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending , SUM(visited IS NOT NULL) as ok FROM users; Thank you I will certainly give that a shot tomorrow when I get back to the computer. Just for educational purposes though, is it not possible to stem from what I had and add to it? Like for example if ($diffdate->m >= 3 OR $diffdate->y >= 1) { $total1 = (something here that counts the above if statment for a total with that condition); } echo $total1; If not, I'd be curious to know why? Many thanks! Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 You would get totals of 1 or 0 for every record. SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as vis_date , date_format(created_at, '%b %e %Y %h:%i%p') as cr_date , visited , created_at , visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15 as expired , visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3 as pending , visited IS NOT NULL as ok FROM users ORDER BY visited DESC You could then accumulate them as you list the results Quote Link to comment Share on other sites More sharing options...
Barand Posted February 1 Share Posted February 1 An alternative (if your MySQL supports WINDOW functions) which puts the totals in each record.. SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as vis_date , date_format(created_at, '%b %e %Y %h:%i%p') as cr_date , visited , created_at , CASE WHEN visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15 THEN 'EXPIRED' WHEN visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3 THEN 'PENDING' WHEN visited IS NOT NULL THEN 'OK' ELSE '???' END as status , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) OVER () as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) OVER () as pending , SUM(visited IS NOT NULL) OVER () as ok FROM users ORDER BY visited DESC What about those who joined between 3 and 15 months ago but haven't visited yet. What status should they have? Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 2 Author Share Posted February 2 @Barand You're awesome thank you. I can't wait to test all of that out tomorrow Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 2 Author Share Posted February 2 15 hours ago, Barand said: This query should do it SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending , SUM(visited IS NOT NULL) as ok FROM users; Hello again. I went with this one after adding a few other lines after it to print it out correctly and this works. However I changed it to the following and it's echo $sql = "SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 6) as expired // after 6 months it is expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) > 3 < 6) as warning // between 3 and 6 months it is in warning , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending // after 3 months it is pending , SUM(visited IS NOT NULL) as ok, visited, created_at FROM users ORDER BY visited DESC"; $query_result = $conn->query($sql); while ($row = $query_result->fetch_assoc()) { $visited = $row['visited'] ; $created_at = $row['created_at'] ; } $result = $conn->query($sql); if(!$result) { die('Error: ' . mysqli_error($link)); } else { $num_rows = mysqli_fetch_assoc($result); // echo it echo "<font color='black'>Expired</font>: <font color='red'>" . $num_rows['expired']."</font>"; echo "<br>"; echo "<font color='black'>Warning</font>: <font color='red'>" . $num_rows['warning']."</font>"; echo "<br>"; echo "<font color='black'>Pending</font>: <font color='red'>" . $num_rows['pending']."</font>"; echo "<br>"; echo "<font color='black'>Okay</font>: <font color='red'>" . $num_rows['ok']."</font><br><br>"; } I know my font tags aren't exactly ideal. I get told that a lot. But since they still work and it's what I'm used to, I don't put it as top priority to change yet lol THE PROBLEM is this is what it printsExpired: 186 Warning: 321 Pending: 44 Okay: 28And there are only 349 records. So I'm assuming something is wrong with the Expired and Warning because the Okay and Pending are accurate Here's something I didn't explain before and I should have... and I apologize for that. So a lot of those accounts don't have ANY login entry at all, and it is blank in the "visited" row What I am attempting to do is * if the user has logged in (visited has a timestamp), and is less than 3 months since they logged in, then it's in Okay * if the user made the account, and hasn't logged in since they created it (visited column is still blank) in less than 3 months, then it's in Pending * if the user has logged in (visited has a timestamp), and hasn't logged in more than 3 months but less than 6 months, then it's in Warning * if the user made the account, and hasn't logged in since they created it (visited column is still blank) in more than 3 months but less than 6 months, then it's in Warning * if the user has logged in (visited has a timestamp), and hasn't logged in more than 6 months, then it's in Expired * if the user made the account, and hasn't logged in since they last logged (visited column is still blank) in more than 6 months, then it's in Expired Somehow, what you provided ALMOST works. But that Expired and Warning is off some how. Any thoughts? Side note: I didn't attempt your last suggestion because (thought I could be wrong) it looks like I would have to alter the database and I'm not allowed to do that. But good educational suggestion still Quote Link to comment Share on other sites More sharing options...
PNewCode Posted February 2 Author Share Posted February 2 Update. Here is the working final if anyone else comes here looking for a solution. I credit @Barand for getting me there! ❤️ $sql = "SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 6) as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 3 and timestampdiff(MONTH, created_at, now()) < 6)as warning , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending , SUM(visited IS NOT NULL) as ok, visited, created_at FROM users ORDER BY visited DESC"; Quote Link to comment Share on other sites More sharing options...
Barand Posted February 2 Share Posted February 2 Update - not there yet. Selecting visited and created_at in a query that returns only a single row is meaningless - you will just get single arbitrary dates from any one of your 349 records. The "working" final solution does not include those cases where the user has visited but not in the last 3 or 6 months (You have the Xs but not the Os below) Summary decision table... Has visited? | Y Y Y | N N N | | | | How long ago? | <=3 3 - 6 >=6 | <=3 3 - 6 >=6 | --------------------+-----------------------+-----------------------+ | | | EXPIRED | O | X | | | | WARNING | O | X | | | | PENDING | | X | | | | OK | X | | Quote Link to comment Share on other sites More sharing options...
Barand Posted February 3 Share Posted February 3 SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as visit_date , date_format(created_at, '%b %e %Y %h:%i%p') as create_date , CASE WHEN visited IS NULL THEN timestampdiff(DAY, created_at, now()) ELSE timestampdiff(DAY, visited, now()) END as elapsed , CASE WHEN visited IS NULL THEN CASE WHEN timestampdiff(DAY, created_at, now()) < 90 THEN 'PENDING' WHEN timestampdiff(DAY, created_at, now()) >= 180 THEN 'EXPIRED' ELSE 'WARNING' END ELSE CASE WHEN timestampdiff(DAY, visited, now()) < 90 THEN 'OK' WHEN timestampdiff(DAY, visited, now()) >= 180 THEN 'EXPIRED' ELSE 'WARNING' END END as status FROM users ORDER BY visited DESC; +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ | id | unique_id | fname | visit_date | create_date | elapsed | status | +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | Feb 1 2024 09:25PM | May 14 2023 09:25PM | 1 | OK | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | Feb 1 2024 09:25PM | Jul 11 2023 09:25PM | 1 | OK | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | Sep 1 2023 09:25PM | May 31 2023 09:25PM | 154 | WARNING | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | Apr 1 2023 09:25PM | Sep 18 2023 09:25PM | 307 | EXPIRED | | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | NULL | Mar 24 2023 09:25PM | 315 | EXPIRED | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | NULL | Jun 25 2023 09:25PM | 222 | EXPIRED | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | NULL | Sep 27 2023 09:25PM | 128 | WARNING | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | NULL | Aug 12 2022 09:25PM | 539 | EXPIRED | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | NULL | Dec 13 2023 09:25PM | 51 | PENDING | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | NULL | Apr 15 2023 09:25PM | 293 | EXPIRED | +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ Summary SELECT SUM( (visited IS NULL AND timestampdiff(DAY, created_at, now()) >= 180) OR (visited IS NOT NULL AND timestampdiff(DAY, visited, now()) >= 180) ) as expired , SUM( (visited IS NULL AND timestampdiff(DAY, created_at, now()) BETWEEN 90 and 179) OR (visited IS NOT NULL AND timestampdiff(DAY, visited, now()) BETWEEN 90 and 179) ) as warning , SUM(visited IS NULL AND timestampdiff(DAY, created_at, now()) < 90) as pending , SUM(visited IS NOT NULL AND timestampdiff(DAY, visited, now()) < 90) as ok FROM users; +---------+---------+---------+------+ | expired | warning | pending | ok | +---------+---------+---------+------+ | 5 | 2 | 1 | 2 | +---------+---------+---------+------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4 Share Posted February 4 There is an alternative that you might want to consider and that is to create an additional "status" column in your users table, adding it as a generated column which will automatically maintain the user status based on the date calculations (as the above queries do). At present, I have mysql> select * from users; +----+--------------------------------------+-------+---------------------+---------------------+ | id | unique_id | fname | created_at | visited | +----+--------------------------------------+-------+---------------------+---------------------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | +----+--------------------------------------+-------+---------------------+---------------------+ If I then alter the table, adding the new status column... mysql> ALTER TABLE users -> ADD COLUMN `status` VARCHAR(10) GENERATED ALWAYS AS -> (CASE WHEN visited IS NULL -> THEN CASE WHEN timestampdiff(DAY, created_at, now()) < 90 -> THEN 'PENDING' -> WHEN timestampdiff(DAY, created_at, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> ELSE CASE WHEN timestampdiff(DAY, visited, now()) < 90 -> THEN 'OK' -> WHEN timestampdiff(DAY, visited, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> END) VIRTUAL AFTER `visited`; I can then use a conventional GROUP BY status to get the totals of each type... mysql> SELECT status -> , COUNT(*) as total -> FROM users -> GROUP BY status; +---------+-------+ | status | total | +---------+-------+ | EXPIRED | 4 | | OK | 2 | | PENDING | 1 | | WARNING | 3 | +---------+-------+ and selecting all the data now looks like this... mysql> SELECT * FROM users; +----+--------------------------------------+-------+---------------------+---------------------+---------+ | id | unique_id | fname | created_at | visited | status | +----+--------------------------------------+-------+---------------------+---------------------+---------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | EXPIRED | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | OK | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | OK | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | PENDING | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | WARNING | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | WARNING | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | WARNING | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | EXPIRED | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | EXPIRED | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | EXPIRED | +----+--------------------------------------+-------+---------------------+---------------------+---------+ 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.