ServerError 500 on updating mysql table

Question

So the problem with this is, i want to create whenever a user presses login a value in the database changes to 1, but whenever i use UPDATE we get a servererror 500 at the click of login

<?php
require("inc/db.php");
ini_set('error_reporting', 0);
ini_set('display_errors', 0);

session_start();

if(isset($_POST['submit'])){
  $errMsg = '';
  //username and password sent from Form
  $username = trim($_POST['username']);
  $password = trim($_POST['password']);

  if($username == '')
    $errMsg .= 'You must enter your Username<br>';

  if($password == '')
    $errMsg .= 'You must enter your Password<br>';


  if($errMsg == ''){
    $records = $conn->prepare('SELECT id,username,password,email,bday,points,uid FROM  users WHERE username = :username AND password = :password');
    $records->bindParam(':username', $username) ;
    $records->bindParam(':password', $password) ;
    $records->execute();
    $results = $records->fetch(PDO::FETCH_ASSOC);

    if($results > 0){

      $sql = "UPDATE users SET isloggedin = :isloggedin WHERE username = :username";
      $stmt = $conn->prepare($sql);
      $stmt->bindParam(':isloggedin', '1');
      $stmt->execute();


      $_SESSION['username'] = $results['username'];
      $_SESSION['id'] = $results['id'];
      $_SESSION['points'] = $results['points'];
      $_SESSION['uid'] = $results['uid'];





      header('location:dashboard.php');
    }else{
      $errMsg .= 'Username and Password are not found<br>';
    }
  }
}
?>

So, the UPDATE function is not working properly, me and my friend cant find the solution to this, we tried to work around it, or tried to change lines for it, but nothing worked...

Thanks in advance!

Answer 1

There are a few issues, which will surface one at a time once error reporting is turned up. Always when developing and testing code, set error_reporting all the way up to E_ALL and enable display errors with ini_set('display_errors', 1). Those together will detail the 500 error on screen for you, making it much more obvious right away.

The first issue I spotted is that your UPDATE statement has two parameters :isloggedin, :username but only the first of them is bound with bindParam(). You must also bind the second, as PDO is throwing an exception related to an invalid parameter number.

  $sql = "UPDATE users SET isloggedin = :isloggedin WHERE username = :username";
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':isloggedin', '1');
  $stmt->bindParam(':username', $username);
  $stmt->execute();

But that only gets you partway there. PDO will next error due to the use of the static string value '1' in bindParam(), which only accepts variables as its second argument (because it utilizes them by reference). The alternative is to use bindValue() to pass a static value. Since you are not executing the same statement many times with changing variable values, it is also then safe to bind :username with bindValue(), though that one would work either way for your case.

  $sql = "UPDATE users SET isloggedin = :isloggedin WHERE username = :username";
  $stmt = $conn->prepare($sql);
  // Make sure static values are bound by value
  $stmt->bindValue(':isloggedin', '1');
  $stmt->bindValue(':username', $username);
  $stmt->execute();