anttwuan anttwuan - 18 days ago 6
MySQL Question

PHP PDO MySQL Count Login Streaks By Date

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

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
Comments