Counting based on IF statement issue

[PNewCode]

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?

[ginerjm]

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?

[PNewCode]

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

[Barand]

What is the query tht is getting this data?

[PNewCode]

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

[Barand]

Do you update the visited date each time the user visits, or is a new record created each time they visit?

[PNewCode]

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"

[Barand]

Which?

• Update, or
• Insert new record?

[PNewCode]

Just now, Barand said:

Which?

Oh I'm sorry. It's update.

[Barand]

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;

 

[PNewCode]

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!

[Barand]

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

[Barand]

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?

[PNewCode]

@Barand You're awesome thank you. I can't wait to test all of that out tomorrow

[PNewCode]

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 prints

Expired: 186
Warning: 321
Pending: 44
Okay: 28

And 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
 

[PNewCode]

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";

 

[Barand]

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                     |                       |

 

[Barand]

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 |
+---------+---------+---------+------+

 

[Barand]

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 |
+----+--------------------------------------+-------+---------------------+---------------------+---------+