php - select data where time is same as future date

Question

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:

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 1

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.

Answer 2

I would generate an array of possible dates using a while loop, then use a the max and min to run a MySQL query using BETWEEN AND The first part would look something like:

$date = date("Y-m-d");
$expdts = array();
$i = 0;
while($i<=10){
    $expdts[] = strtotime($date . " + " . $i . "days");
    $i++;
}

and then just run a single MySQl query of:

"SELECT * FROM users_added WHERE expire BETWEEN :time1 AND :time2"

:time1 being $expdts[0] and :time2 being $expdts[10] then sort them by the expire variable. As a bouns this can be done with the $expdts array, as the keys in this array are how many 'days out' the time stamp is from the current date.

I haven't tested this past the first part.