php - 如何使确认码在 1 天后过期

Question

我试图在 mysql 表“winners”上的获胜日期早于 1 天时使确认代码过期。我的 php 代码工作正常并将 confirmation_status 设置为 "expired"，但是当有超过 1 行具有相同的 id_recharge_winner 时，脚本将所有这些行设置为 "expired"。我希望只有年长的人被排除在外。

请帮帮我

我的 table 上赢家是这样的:

|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

我的 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);
            }
        }
    }
}

Answer 1

我怀疑您可以简单地通过选择/更新所有相关记录的单个查询来完成工作。

考虑:

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
    )

WHERE 子句应用相关过滤器；如果没有记录匹配，则不会发生 UPDATE。该子句还包含一个 NOT EXITS 条件，该条件使用相关性来确保正在更新的记录(如果有)是最旧的记录(即没有更旧的记录)。

PS:我用绑定(bind)参数 (?) 替换了 PHP 变量 $id_recharge_winner ；你应该看看how to use parameterized queries让您的代码更安全、更简洁。