how to insert date of login and time into another table in mysql

Question

I have developed a login form with database table that holds the user details. I would like to have an additional table that holds the date of the user login.

The table users contain:

users( id , username, password)

the table user_login has:

user_login(id, user_id, login_date)

here user_id is the foreign key for the id from users table and the type for login_date is set to timestamp.

I have tried something like this:

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


if (isset($_POST['login_user'])) {
    $username = mysqli_real_escape_string($db, $_POST['username']);
    $password = mysqli_real_escape_string($db, $_POST['password_1']);

    if (empty($username)) {
        array_push($errors, "Username is required");
    }
    if (empty($password)) {
        array_push($errors, "Password is required");
    }

    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'];

            $date = date('Y-m-d H:i:s');
            $indate= "INSERT INTO user_login 
                                    (user_id, login_date) 
                                    SELECT * 
                                    FROM users as u 
                                    WHERE username='$name'
                                    AND login_date= '$date'";
            mysqli_query($db, $indate);

            // $_SESSION['success'] = "You are now logged in";
            header('location: profile.php');
        }else {
            array_push($errors, "Wrong username/password combination");
        }
     }
}

but when i execute the above code, the user can login successfully but the date is not added to the table, I don't know where i went wrong. Can someone tell me the correct query to insert the date in the table user_login

You're trying to execute twice and one without the connection. So I voted this as a typographical error. — Funk Forty Niner, Oct 11 '19 at 15:05
Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) attack. Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Oct 11 '19 at 15:07
Please dont __roll your own__ password hashing, specially not using MD5() or SHA1(). PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them for the safety of your users. — RiggsFolly, Oct 11 '19 at 15:07
"I don't know where i went wrong" What debugging have you done to _check_ where you might have gone wrong? — Patrick Q, Oct 11 '19 at 15:08
@PatrickQ the query is not executing, can u give a solution for this probelm — viki, Oct 11 '19 at 15:09
`INSERT INTO user_login (user_id, login_date) SELECT u.id FROM user`... I'm surprised anything is inserting at all, since you're selecting 1 column but inserting 2 — aynber, Oct 11 '19 at 15:09
`$indate= mysqli_query(` you probably just mean `$indate = ` here — Patrick Q, Oct 11 '19 at 15:09
The `SELECT * FROM users.......` Will give you the `id` of this user. if you only FETCH the row. Then your `user_login` insert would be TRIVIAL — RiggsFolly, Oct 11 '19 at 15:10
@PatrickQ i removed the mysqli_query from $indate. But with the insert query , is it right ? Can u tell me how to make the insert query for this problem — viki, Oct 11 '19 at 15:16
@RiggsFolly i have edited the code now, still the insert query is not executed. can you tell me where i went wrong — viki, Oct 11 '19 at 15:20
Use PHP's error reporting and use `mysqli_error($db)` on the query. Those might show you errors. — Funk Forty Niner, Oct 11 '19 at 15:21
@viki As I mentioned before, _you_ need to do some debugging. [This](https://stackoverflow.com/questions/17053466/how-to-display-errors-for-my-mysqli-query) and the linked duplicates would be a good place to start. Debugging is an invaluable skill, and learning how to pinpoint problems yourself (even if you don't know the _solution_) is far better than just copy/pasting what others tell you to do. — Patrick Q, Oct 11 '19 at 15:22
@PatrickQ i tried debugging, it says "column count doesn't match the value at row 1". Can you explain this. I am new with mysql and php. thats why i am looking for some solutions, so i can learn from it. Can you help me here — viki, Oct 11 '19 at 15:37

score -2 · Accepted Answer · answered Oct 11 '19 at 15:38

Why dont you use update statements to update login date each time user loggedin. something like.

Again You should be better doing all this queries with prepared statements rather than escaping using msqli_escape..... function because of sql injection.

$update = "UPDATE user_login SET login_date='$date' WHERE username='$username'";
if ($db->query($update) === TRUE) {
    echo "Date updated";
} else {
    echo "Cannot update date";
}

so it can become something like

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


if (isset($_POST['login_user'])) {
    $username = mysqli_real_escape_string($db, $_POST['username']);
    $password = mysqli_real_escape_string($db, $_POST['password_1']);

    if (empty($username)) {
        array_push($errors, "Username is required");
    }
    if (empty($password)) {
        array_push($errors, "Password is required");
    }

    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_start();

            $_SESSION['username'] = $username;
            $name=$_SESSION['username'];

            $date = date('Y-m-d H:i:s');
            $indate= "INSERT INTO user_login 
                                    (user_id, login_date) 
                                    SELECT * 
                                    FROM users as u 
                                    WHERE username='$name'
                                    AND login_date= '$date'";


$update = "UPDATE user_login SET login_date='$date' WHERE username='$username'";
if ($db->query($update) === TRUE) {
    echo "Date updated";
} else {
    echo "Cannot update date";
}



            mysqli_query($db, $indate);

            // $_SESSION['success'] = "You are now logged in";
            header('location: profile.php');
        }else {
            array_push($errors, "Wrong username/password combination");
        }
     }
}

but then what is the point of having insert query in the code. can I remove it ? — viki, Oct 11 '19 at 15:41
Thanks alot. I will also appreciate if you can upvote my answer. thank you — Henrymart, Oct 11 '19 at 15:55

how to insert date of login and time into another table in mysql

1 Answers1