How to save date only once per day in the table

Question

I have a login form who data are stored in a table users. I also have another table that stores the login date and time.

The table users (id, username, password)

The table user_login (id, user_id, login_date)

The code I tried:

$db = mysqli_connect('localhost', 'root', '', 'registration');

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'];
          $row=mysqli_fetch_array($results);
    $user_id= $row['id'];

    $date = date('Y-m-d');
    $checkdate= "SELECT id from user_login WHERE user_id='$user_id' AND DATE(login_date)='$date'";
    $check=mysqli_query($db, $checkdate)or die(mysqli_error($db));
    if(mysqli_num_rows($check)==1){
    $updatedate="UPDATE user_login set date= $date where id=$user_id";
    mysqli_query($db,$updatedate)or die(mysqli_error($db));
    }
    else{
    $insertdate="INSERT INTO user_login (user_id, login_date) values($user_id, $date)";
    mysqli_query($db,$insertdate)or die(mysqli_error($db));
    }
 // $_SESSION['success'] = "You are now logged in";
    header('location: profile.php');
    }else {
    array_push($errors, "Wrong username/password combination");
     }
    }
    }

The above code just adds date and time every time I login. But I want to save the date only once per day.

**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Oct 11 '19 at 20:38
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 11 '19 at 20:38
@Dharman i will make all those changes you suggested, but can u help me with the problem I asked. — miskaaa, Oct 11 '19 at 20:39
Add a unique constraint on the date field or use cron. It's not clear whichever you want — Dharman, Oct 11 '19 at 20:41
It's not the query. You would need to add a unique key to the table. You could try something using just SQL, but it would not be the best solution. — Dharman, Oct 11 '19 at 20:51
@Dharman i tried with the unique key, but its not working, still the date is added if logged in same day — miskaaa, Oct 11 '19 at 21:02
You can simply build the logic into the INSERT itself. If you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Oct 11 '19 at 23:05

Jasco · Answer 1 · 2019-10-23T14:02:51.620

Here's one idea.

You could save date into your user_login table and then you can check and compare it

Before inserting into your user login table you would then check it. Get id of the user from $results. And check table: $date=date('Y-m-d');

Select id from user_login where user_id = $user_id and Date(login_date) = $date

If there is a record just update date $cur_date=date('Y-m-d'); Update user_login set date=$cur_date where id=$user_login_id else insert one INSERT INTO user_login (user_id, login_date) values($user_id, $cur_date);

I hope you understand the logic. I hope you are using mysqli_escape_string in order to prevent from sql injection.

Don't use md5 for password encryption use bcrypt or other secure encryption functions.

here is working version of your code. Don't use it in production.

$db = mysqli_connect('localhost', 'root', '', 'registration');


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'];
        $row = mysqli_fetch_array($results);
        $user_id = $row['id'];

        $date = date('Y-m-d');
        $datetime = date('Y-m-d H:i:s');

        $checkdate = "SELECT id from user_login WHERE user_id='$user_id' AND DATE(login_date)='$date'";
        $check = mysqli_query($db, $checkdate) or die(mysqli_error($db));

        if (mysqli_num_rows($check) == 1) {
            $updatedate = "UPDATE user_login set login_date='$datetime' where user_id=$user_id and DATE(login_date)='$date'" ;
            mysqli_query($db, $updatedate) or die(mysqli_error($db));
        } else {
            $insertdate = "INSERT INTO user_login (user_id, login_date) values($user_id, '$datetime')";
            mysqli_query($db, $insertdate) or die(mysqli_error($db));
        }
        // $_SESSION['success'] = "You are now logged in";
        header('location: profile.php');
        die;
    }else {
        array_push($errors, "Wrong username/password combination");
    }

}

this is just demonstration.

Your suggested method of surviving sql injection is outdated. — Strawberry, Oct 12 '19 at 06:47
I understood the logic, but can you show it where i should insert the logic, I think i failed to implement the query at the right place — miskaaa, Oct 12 '19 at 12:40
I have edited the code, check it. When i execute it, it just adds date 0000-00-00 and for every login, it still adds a new row — miskaaa, Oct 12 '19 at 12:48

How to save date only once per day in the table

1 Answers1