oliverbj oliverbj - 6 months ago 24
PHP Question

php - select data where time is same as future date

I have a table in my database, called

users_added
, inside that table, I have a field called
expire
which stores a unix timestamp.

My question is, how can I loop through x number of days, and see how many days the users have left (until the expire date is met)

Example:

1 day remaining:
10 users

2 days remaining:
0 users

3 days remaining
50 users


etc etc..

Graphical example:

enter image description here

This is what I have so far:

// Start date
$date = date("Y-m-d",$time);
// End date
$end_date = date('Y-m-d', strtotime($date . " +10 days"));



while (strtotime($date) <= strtotime($end_date)){


$stmt = $dbh->prepare("SELECT * FROM users_added WHERE user_by=:user AND expire<:time");
$stmt->bindParam(":user",$userdata['username']);
$stmt->bindParam(":time",$end_date);
$stmt->execute();
$expireData = $stmt->fetchAll();
$remaining = $date-$end_date;
echo "$remaining";
echo "day(s) remaining:";

$date = date ("Y-m-d", strtotime("+1 day", strtotime($date)));
}


I can't really go further from here, as I am stuck. I don't know how to achieve this.

Answer

This worked for me:

mysql> CREATE TABLE users (expiry INT UNSIGNED);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO users (expiry) VALUES
        (UNIX_TIMESTAMP() + 1000),
        (UNIX_TIMESTAMP() + 12348),
        (UNIX_TIMESTAMP() + 89284),
        (UNIX_TIMESTAMP() + 99438),
        (UNIX_TIMESTAMP() + 333552),
        (UNIX_TIMESTAMP() + 883718),
        (UNIX_TIMESTAMP() + 994872);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT
        ROUND((expiry - UNIX_TIMESTAMP()) / 86400) AS days,
        COUNT(*) AS cnt
       FROM users
       WHERE expiry > UNIX_TIMESTAMP()
       GROUP BY days;
+------+-----+
| days | cnt |
+------+-----+
|    0 |   2 |
|    1 |   2 |
|    4 |   1 |
|   10 |   1 |
|   12 |   1 |
+------+-----+
5 rows in set (0.00 sec)

mysql> 

Just let the database do the work; subtracting the expiry timestamp from the current timestamp gives you the number of seconds before expiry, and dividing that by 86400 gives you number of days. Then you can group the results by number of days, to get the count for each day.