Php - select data whose time coincides with a date in the future
I have a table in my database called users_added
, inside this table, I have a field called expire
that stores a unix timestamp.
My question is, how can I go through x number of days and see how many days there are users left (before expiration)
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 cannot go any further from here as I am stuck. I don't know how to achieve this.
source to share
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 job; subtracting the expiration timestamp from the current timestamp gives you the number of seconds before the expiration, and division by 86400 gives you the number of days. Then you can group the results by the number of days to get a bill for each day.
source to share
I would generate an array of possible dates using a while loop, then use max and min to run the MySQL query using BETWEEN AND The first part would look something like this:
$date = date("Y-m-d");
$expdts = array();
$i = 0;
while($i<=10){
$expdts[] = strtotime($date . " + " . $i . "days");
$i++;
}
and then just run one MySQl query:
"SELECT * FROM users_added WHERE expire BETWEEN :time1 AND :time2"
:time1
constitutes $expdts[0]
and :time2
$expdts[10]
then sort them by expire variable. As an inflection point, this can be done using the $ expdts array, since the keys in this array are how many "days" are from the timestamp from the current date.
I haven't tested this in the first part.
source to share