CODEWITHSUNDEEP
phpmysqlauthentication
uvii
uvii

Reputation: 11

How to check for 3 consecutive days from the table using PHP and Mysql

I have a table that holds the user info while login and another table to store the timestamp of every login of the user. 

Users(id, username, password, login_count)

user_login(user_id, Login_date) - user_id here is the foreign key for the id from users table.

the code i tried to save the login timestamps:

    if (count($errors) == 0) {

$password = md5($password);
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";
$results = mysqli_query($db, $query);

if (mysqli_num_rows($results) == 1) {
  $_SESSION['username'] = $username;
    $name=$_SESSION['username'];

    $indate= "INSERT IGNORE INTO user_login (user_id) SELECT id FROM users WHERE username='$name'";
    mysqli_query($db, $indate)or die(mysqli_error($db));

 header('location: profile.php');
}
    else {
    array_push($errors, "Wrong username/password combination");
}
    } 
    }

The above code just inserts the timestamps of each and every login. What i would to do now, is to check for 3 consecutive days of login for the user from the table and when it is 3 consecutive days, it should set a flag value at login_count at users table. I don't know how to proceed. Kindly give some suggestions and help me with this problem.

Upvotes: 0

Views: 585

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Consider the following:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,date DATE NOT NULL
);

INSERT INTO my_table (date) VALUES
('2019-01-01'),
('2019-01-02'),
('2019-01-04'),
('2019-01-05'),
('2019-01-06'),
('2019-01-08');


SELECT x.* 
  FROM my_table x 
  JOIN my_table y 
    ON y.date BETWEEN x.date AND x.date + INTERVAL 2 DAY 
 GROUP 
    BY x.date 
HAVING COUNT(DISTINCT y.date) = 3;
+----+------------+
| id | date       |
+----+------------+
|  3 | 2019-01-04 |
+----+------------+

Upvotes: 0

Related Questions