Can't get the database to update last login time using PHP and MysQL?

Question

Here is my last login part that dosen't seem to work here it is. I think I messed up somewhere?

    // Query the database:
    $q = "SELECT user_id, first_name, user_level FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL";      
    $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));


    if (@mysqli_num_rows($r) == 1) { // A match was made.

        // Register the values & redirect:
        $_SESSION = mysqli_fetch_array ($r, MYSQLI_ASSOC); 
        $q = "UPDATE users SET last_login = NOW() WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; 
        // save the info to the database
        $r= mysqli_query( $dbc );
        mysqli_free_result($r);
        mysqli_close($dbc);

Here is the full script.

<?php
if (isset($_POST['submitted'])) {
 require_once (MYSQL);

 // Validate the email address:
 if (!empty($_POST['email'])) {
  $e = mysqli_real_escape_string ($dbc, $_POST['email']);
 } else {
  $e = FALSE;
  echo '<p class="error">You forgot to enter your email address!</p>';
 }

 // Validate the password:
 if (!empty($_POST['pass'])) {
  $p = mysqli_real_escape_string ($dbc, $_POST['pass']);
 } else {
  $p = FALSE;
  echo '<p class="error">You forgot to enter your password!</p>';
 }

 if ($e && $p) { // If everything's OK.

  // Query the database:
  $q = "SELECT user_id, first_name, user_level FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL";  
  $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));


  if (@mysqli_num_rows($r) == 1) { // A match was made.

   // Register the values & redirect:
   $_SESSION = mysqli_fetch_array ($r, MYSQLI_ASSOC); 
   $q = "UPDATE users SET last_login = NOW() WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; 
   // save the info to the database
   $r= mysqli_query( $dbc );
   mysqli_free_result($r);
   mysqli_close($dbc);

   $url = BASE_URL . 'index.php'; // Define the URL:
   ob_end_clean(); // Delete the buffer.
   header("Location: $url");
   exit(); // Quit the script.

  } else { // No match was made.
   echo '<p class="error">Either the email address and password entered do not match those on file or you have not yet activated your account.</p>';
  }

 } else { // If everything wasn't OK.
  echo '<p class="error">Please try again.</p>';
 }

 mysqli_close($dbc);

} // End of SUBMIT conditional.
?>

Answer 1

Hi,

Maybe a bit of a wild guess, but this seems strange :

$q = "UPDATE users SET last_login = NOW() WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; 
// save the info to the database
$r= mysqli_query( $dbc );

The update query is stored in $q, but that $q variable is not passed as a parameter to mysqli_query ?

Should it not be passed as a second parameter ?
ie :

$r= mysqli_query( $dbc, $q );

(That's what is done for the select query -- but not for the update one)

Answer 2

You are probably not even running the query,

$q = "UPDATE users SET last_login = NOW() WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; 
                        // save the info to the database
                        $r= mysqli_query( $dbc );

should be

$q = "UPDATE users SET last_login = NOW() WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL"; 
                        // save the info to the database
                        $r= mysqli_query( $dbc ,$q);

and I would advice you to check whether the update took place using affected_rows