PHP PDO MySQL计数登录条纹按日期

Question

What I'm trying to do is:
* If user is logged on within 1 day, do nothing.
* if user is logged on after 1 day, and is within 2 days, set count + 1
* if user is logged on after 2 days, set count 1

$conn = $db->PDO();
try {
   $stmt = $conn->pdo->prepare("SELECT `id` FROM `user_logins` WHERE `user_id` = :uid LIMIT 1");
   $stmt->bindParam(':uid', $uid, $db->PARAM_INT);
   $stmt->execute();
   if($stmt->rowCount() > 0) {
      $stmt = $conn->pdo->prepare("SELECT `count`,`login_time` FROM `user_logins` WHERE `user_id` = :uid LIMIT 1");
      $stmt->bindParam(':uid', $uid, $db->PARAM_INT);
      $stmt->execute();
      $fetch = $stmt->fetch($db->FETCH_ASSOC);
      $loginStreak = $fetch['count'];
      $loginTime = $fetch['login_time'];
      $userPoints = $users->getUserInfo($uid, 'vip_points');
      if($loginTime < strtotime('+2 day')) {
          $stmt = $conn->pdo->prepare("UPDATE `user_logins` SET `count` = :c, `login_time` = :lt WHERE `user_id` = :uid LIMIT 1");
          $stmt->bindValue(':c', 1);
          $stmt->bindValue(':lt', NULL);
          $stmt->bindParam(':uid', $uid, $db->PARAM_INT);
          $stmt->execute();
       } elseif($loginTime < strtotime('+1 day')) {
          $stmt = $conn->pdo->prepare("UPDATE `user_logins` SET `count` = :c, `login_time` = :lt WHERE `user_id` = :uid LIMIT 1");
          if($loginStreak <= 7) {
            // login streak is lower or equal to 7 days, so give 1 point
            $users->updateUser($uid, 'vip_points', $userPoints + 1);
            $stmt->bindValue(':c', $loginStreak + 1);
          } elseif($loginStreak <= 14) {
            // login streak is lower or equal to 14 days, so give 2 points
            $users->updateUser($uid, 'vip_points', $userPoints + 2);
            $stmt->bindValue(':c', $loginStreak + 1);
          } elseif($loginStreak <= 21) {
            // login streak is lower or equal to 21 days, so give 3 points
            $users->updateUser($uid, 'vip_points', $userPoints + 3);
            $stmt->bindValue(':c', 21);
          }
          $stmt->bindValue(':lt', NULL);
          $stmt->bindParam(':uid', $uid, $db->PARAM_INT);
          $stmt->execute();
       }
     } else {
       $stmt = $conn->pdo->prepare("INSERT INTO `user_logins` (`user_id`,`login_time`,`count`) VALUES (:uid,:lt,:c)");
       $data = array(':uid' => $uid, ':lt' => NULL, ':c' => 1);
       $stmt->execute($data);
     } 
  } catch(PDOException $e) {
     die($e->getMessage());
  }
$conn = null;

So far I have come up with this, but it does not really work the way as written above. Any suggestions?

Answer 1

You can do it entirely in a single UPDATE query:

UPDATE user_logins
SET count = CASE 
        WHEN login_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
            THEN count 
        WHEN login_time > DATE_SUB(NOW(), INTERVAL 2 DAY)
            THEN count + 1
        ELSE 1
    END,
    login_time = CASE 
        WHEN login_time <= DATE_SUB(NOW(), INTERVAL 1 DAY)
            THEN NULL
        ELSE login_time
    END
WHERE user_id = :uid