How can make a confirmation code expires when old then 1 day

Question

I am trying to make a confirmation code expires when the date of win is old then 1 day on mysql table "winners". My php code works fine and set the confirmation_status to "expired" , but when there is more than 1 row which have the same id_recharge_winner , the script makes all those rows to "expired" . I would like just the older ones to be exoired.

Help me please

My table winners is like that:

|id_recharge_winner |confirmation_code |confirmation_status| date_confirmation |date_win|
-------------------------------------------------------------------------------------------
1                   |8eomdv          | not confirmed       |NULL      |2019-01-20 23:58:41
1                   |ioozpu          | not confirmed       |NULL      |2019-02-02 09:57:10
1                   |cpq2vp          | not confirmed       |NULL      |2019-01-26 01:05:18
2                   |tnymsp          | not confirmed       |NULL      |2019-02-02 01:09:54
2                   |qh8lqq          | not confirmed       |NULL      |2019-02-02 06:14:37
2                   |jgg3xi          | not confirmed       |NULL      |2019-01-26 01:22:40
3                   |cukxc5          | expired             |NULL      |2019-01-26 01:33:11
4                   |3ixoj4          | not confirmed       |NULL      |2019-01-26 01:43:42
5                   |20bqrn          | not confirmed       |NULL      |2019-01-26 11:18:16
6                   |lebx61          | not confirmed       |NULL      |2019-02-02 12:40:27
6                   |7tgoaz          | not confirmed       |NULL      |2019-01-26 12:42:41
6                   |kphs5k          | not confirmed       |NULL      |2019-01-26 12:51:33
6                   |6vxcy9          | not confirmed       |NULL      |2019-01-26 13:07:23
7                   |sttyul          | not confirmed       |NULL      |2019-01-26 13:11:47

My php:

for ($i=1;$i<=7;$i++){

    //Verify if confirmation code is expired
    $sql_expired = "SELECT id_recharge_winner, date_win, date_confirmation 
                    FROM winners 
                    WHERE id_recharge_winner ='$i'";
    $result_expired = mysqli_query($conn, $sql_expired);
    if (mysqli_num_rows($result_expired) > 0) {
        while($row = mysqli_fetch_assoc($result_expired)){
            $id_recharge_winner = $row['id_recharge_winner'];
            $date_win = $row['date_win'];
            $date_confirmation = $row['date_confirmation'];
            $expiration_delay = 1; //One day
            if ((time() - $date_win) >= $expiration_delay) {
                $sql_set_expired = "UPDATE `winners` 
                                    SET `confirmation_status` = 'expired' 
                                    WHERE confirmation_status = 'not confirmed' 
                                    AND id_recharge_winner = '$id_recharge_winner'";
                $set_expired_result = mysqli_query($conn, $sql_set_expired);
            }
        }
    }
}

Try to print `(time() - $date_win)` for debug the if statement — Arnaud Peralta, Feb 02 '19 at 23:55
Your second SQL statement doesn't use date_win when choosing rows. You can add it, or do the if/time comparison in the SQL directly as another where clause. — jhnc, Feb 02 '19 at 23:55
Your table is missing a PRIMARY key that you can use as a unique identifier. Typically it would be called 'id'. You can then use that to make sure your UPDATE will only change the one row you need. Alternatively, you could insert the confirmation_code inside your update query to narrow down the result but that’s not the proper way of doing this. — Patrick Simard, Feb 02 '19 at 23:57
Don't store a field that indicates whether it's expired or not. Just use the date of when it expires. And, always check that date when needed. — Brad, Feb 03 '19 at 01:29

score 1 · Answer 1 · answered Feb 03 '19 at 00:02

I suspect that you could simply get the job done with a single query that selects/updates all relevant records.

Consider :

UPDATE winners w
SET w.confirmation_status = 'expired' 
WHERE 
    w.id_recharge_winner = ?
    AND w.confirmation_status = 'not confirmed' 
    AND DATEDIFF(CURDATE(), w.date_win) > 1
    AND NOT EXISTS (
        SELECT 1
        FROM winners w1
        WHERE
            w1.id_recharge_winner = w.id_recharge_winner
            AND w1.confirmation_status = w.confirmation_status
            AND w1.date_win < w.date_win
    )

The WHERE clause applies the relevant filters ; if no records match, no UPDATE happens. The clause also incudes a NOT EXITS condition, that uses a correlated to ensure that that the record being updated (if any) is the oldest one (ie there is no older record).

PS : I replaced PHP variable $id_recharge_winner with a bind parameter (?) ; you should look at how to use parameterized queries to make your code safer and cleaner.

score 0 · Answer 2 · answered Feb 03 '19 at 00:07

Use timestampdiff in your WHERE clause to only select rows where the difference between the time won and now is greater than or equal to a day. You don't need to update the table and probably shouldn't. Whenever you want the expired wins use something like the following.

SELECT ...
       FROM winners
       WHERE ...
             AND timestampdiff(day, date_win, now()) >= 1
             ...
       ...;

If you want the not expired ones, negate the condition.

SELECT ...
       FROM winners
       WHERE ...
             AND timestampdiff(day, date_win, now()) < 1
             ...
       ...;

How can make a confirmation code expires when old then 1 day

2 Answers2