Insert query inside if statement

Question

I want to add the current date when a user is logged in on my system, however it doesn't seem to work!

if (password_verify($_POST['password'], $password)) {
    session_regenerate_id();
    $_SESSION['loggedin'] = TRUE;
    $_SESSION['name'] = $_POST['username'];
    $_SESSION['id'] = $id;
    $query = "  INSERT INTO accounts (last_login) VALUES (SYSDATE() 
    );WHERE `id`='".$id."'";
    header('Location: home.php');

The field last_login field in MySQL is set to datetime. Now what happens is that it redirects the user to home.php, however nothing is written in the DB!

Thanks for your comment, but this is on a test server! It's just for educational purposes! — Leonidas, Aug 01 '19 at 11:01
IMO what environment it is doesn't matter, it's much more educational to do things the right way — treyBake, Aug 01 '19 at 11:04
As for the question itself... You're never actually executing your database query. What database access technology are you using? mysqli? PDO? Something else? In the tutorials/examples for that technology there are demonstrations of how to execute a query. Once you *execute* the query, you can then also check for errors from the database to see various things wrong with the query. — David, Aug 01 '19 at 11:05
It is mysqli but what do you mean it is never executing? What should i do to insert it? — Leonidas, Aug 01 '19 at 11:24
@Leonidas: You *define* the query, but all that does is create a string. You need to *execute* the query in order to, well, execute it. A Google search for "mysqli insert example" turns up the following: https://stackoverflow.com/questions/16835753/inserting-data-to-table-mysqli-insert Note the line using `mysqli_query()`. Note also the use of `mysqli_error()` to check for errors after executing the query, since you should never assume success but rather validate it. — David, Aug 01 '19 at 11:29

score 0 · Answer 1 · answered Aug 01 '19 at 11:30

A few things to note:

I'd be surprised if you wanted to insert a record with only one value and no reference to the user account
INSERT doesn't use a WHERE clause
I'd expect you'd want to do an UPDATE

Perhaps something like this:

$query="UPDATE accounts SET last_login=NOW() WHERE id='".$id."'";

score -1 · Answer 2 · answered Aug 01 '19 at 11:08

You may use NOW() Function in your query it will return the current date and time like

if (password_verify($_POST['password'], $password)) {
session_regenerate_id();
$_SESSION['loggedin'] = TRUE;
$_SESSION['name'] = $_POST['username'];
$_SESSION['id'] = $id;
$query = "  INSERT INTO accounts (last_login) VALUES (NOW()) WHERE id='$id'";
header('Location: home.php');

Insert query inside if statement

2 Answers2