Wrong(not updatable) date in database

Question

I have code:

<!DOCTYPE html>
<html>
<head>
</head>
<body>
<?php
    session_start();
    $conn = mysqli_connect("localhost","root","");
    mysqli_select_db($conn, "test");
?>

<?php
if (isset($_GET['logout'])==1) 
{
    session_destroy();
    $_SESSION['ishere'] = false;
}
?>

<?php
if (isset($_POST['login_button'])) 
{
    $login = $_POST['login'];
    $password = $_POST['password'];

    if (mysqli_num_rows(mysqli_query($conn, "SELECT login, password FROM user WHERE login = '".$login."' AND password = '".$password."';")) > 0) 
    {   
        mysqli_query($conn, "UPDATE user SET 'login_time' = NOW() WHERE login = '".$login."';");

        $_SESSION['ishere'] = true;
        $_SESSION['login'] = $login;        
    }
    else echo "Something went wrong. Try again!";
}

if (isset($_SESSION['ishere'])==true)
{
    echo "Hello <b>".$_SESSION['login']."</b><br><br>";
    $time = mysqli_fetch_all(mysqli_query($conn, "SELECT login_time FROM user WHERE login='".$_SESSION['login']."'"));
    echo "Czas ostatniego logowanie ";
    foreach ($time as $i) {
        echo date('Y-m-d H:i:s', implode($i));
    }
    echo '<a href="?logout=1">[Logout]</a>';
}
?>

<?php if (isset($_SESSION['ishere'])==false): ?>
    <form method="POST" action="login.php">
        <b>Login:</b> <input type="text" name="login"><br>
        <b>Password:</b> <input type="password" name="password"><br>
        <input type="submit" value="Login" name="login_button">
    </form>
<?php endif; ?>

<?php mysqli_close($conn); ?>
</body>
</html>

Field login_time is in my db as int and the I changed it to date. When I login I have all the time the same timestamp which is convert to date: 1970-01-01 01:00:00. What I do wrong? I thought that this code while I login will update my db and set login_time to current time, then when I login again the new one will be printed :( Ah and I used functions like NOW(), time(), date('Y-m-d H:i:s') and CURRENT_TIMESTAMP. How can write to db correct time and read it?

EDIT. I changed my code:

mysqli_query($conn, "UPDATE user SET login_time = now() WHERE login = '".$login."';");

$result = mysqli_query($conn, "SELECT login_time FROM user WHERE login='".$_SESSION['login']."'");
$time = mysqli_fetch_assoc($result);
echo date('Y-m-d H:i:s', $time["login_time"]);

And now I have the same date, even if I logout and login again. And the date is from 2038...

EDIT2. UNIX_TIMESTAMP() is a solution :)

Also when already using `mysqli` read up on parameter binding. (And then read up on PDO, and why that's less cumbersome.) — mario, Nov 18 '17 at 17:22

score 1 · Answer 1 · answered Nov 18 '17 at 17:21

1

dont'use quote around column name (when needed eventualy use backtics) the quote are for literal value

"UPDATE user SET login_time = NOW() WHERE login = '".$login."';");

answered Nov 18 '17 at 17:21

ScaisEdge

131,976
10
91
107

ok now is date but `2038-01-19 04:14:07` and never update – Bubuu Nov 18 '17 at 17:30
You mean that your now() function return 2038-01-19 ?? what do you mean not update ? ...are you sure for te value use for login in where condition is correct and the update iquery is reached ? ... and last .. have you checked i the update query raise an error? – ScaisEdge Nov 18 '17 at 17:34
2038 is a key number/date here – Funk Forty Niner Nov 18 '17 at 17:38
yes, now() return `2038-01-19 04:14:07`. When I logout and login again it should be new current date. – Bubuu Nov 18 '17 at 17:40
@Fred-ii- . explain better please – ScaisEdge Nov 18 '17 at 17:41
@Bubuu now() is a mysql function .. and should return you currenct date time .. – ScaisEdge Nov 18 '17 at 17:45

Wrong(not updatable) date in database

1 Answers1