Having trouble with PDO queries

Question

Can someone please take a look at this block of code? I am very new to the PDO method, for some reason this keeps causing a 500 error whenever I submit.

I have narrowed it down to this:

Could it be this part? $hash = $stmt['hash'];

if(empty($response['error'])){
    $stmt = $db->prepare("SELECT * FROM Login WHERE username= :username"); // Prepare the query
 // Bind the parameters to the query
    $stmt->bindParam(':username', $username);
    //Carry out the query
    $stmt->execute();
    $hash = $stmt['hash'];

    $affectedRows = $stmt->rowCount(); // Getting affected rows count
    if($affectedRows != 1){
        $response['error'][] = "No User is related to the Username";
    }
    if(password_verify($password, $hash))
    {
      $_SESSION['username'] = $_POST['username'];
            $_SESSION['userid'] = $stmt['ID'];
    }
    else
    {
      $response['error'][] = "Your password is invalid.";
    }
}

If you need more info please ask I will be happy to supply anything I can.

Which logs? Where? Server logs? If I remove the $hash = $stmt['hash']; it doesnt error out, but it also obviously wont work. — Frank Edgar, May 15 '15 at 15:41
You're not fetching the result of your query, http://php.net/manual/en/pdostatement.fetch.php Don't think that'd be the 500 though so you still need to check the error logs. — chris85, May 15 '15 at 15:42
@FrankEdgar, your server has display_errors off, this is why it shows an error 500. `$hash = $stmt['hash']` isn't how you access row data. Read the manual of [PDOStatement](http://php.net/manual/en/class.pdostatement.php) — Devon Bessemer, May 15 '15 at 15:42
Right. that is why I was asking if it was that part I highlighted above. Thank you @chris85 I wasnt sure which statement I needed to bind results. The 500 error is just because I am using the wrong way of binding, when I remove that statement the error goes away. — Frank Edgar, May 15 '15 at 15:45
If you want to throw together an answer with the correct syntax I can give you credit. — Frank Edgar, May 15 '15 at 15:45
@FrankEdgar, the underlying issue here is that you are developing on a system where PHP isn't displaying errors. That is going to bother you every time you have an error. If you have access to php.ini, make sure display_errors is on, if not, then use .htaccess. — Devon Bessemer, May 15 '15 at 15:53

score 1 · Accepted Answer · edited May 23 '17 at 10:24

You need to fetch the result of the query to have it accessible. I'm not sure this is your issue, I'd think $hash would just be set to Resource Id#x, not what you want but not a 500. Here's how to fetch (http://php.net/manual/en/pdostatement.fetch.php) though

$stmt = $db->prepare("SELECT * FROM Login WHERE username= :username"); // Prepare the query
 // Bind the parameters to the query
    $stmt->bindParam(':username', $username);
    //Carry out the query
    $stmt->execute();
  //if you will only be getting back one result you dont need the while or hashes as an array
   while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
    $hashes[] = $result['hash'];
   }

Here's a thread on enabling error reporting PHP production server - turn on error messages

Also you don't have to bind to pass values with the PDO. You also could do

$stmt = $db->prepare("SELECT * FROM Login WHERE username= ?"); // Prepare the query
$stmt->execute(array($username));

What is the difference between the way I did it (lines 2 - 5) and the way you are saying to do it with `$stmt->execute(array($username));` ? Is your way better? Or just shorthand? I have mine like that because I was planning on binding multiple results. (in the future) — Frank Edgar, May 15 '15 at 16:18
Yea, just a shorter way, both work the same. You can have multiple values as well `array($username, $value2, $value3, $etc)`. Examples three and six on the PHP doc page illustrate this usage, http://php.net/manual/en/pdo.prepared-statements.php. — chris85, May 15 '15 at 17:36

score 1 · Answer 2 · answered May 15 '15 at 16:02

Your code is really messy. Just to help you with start point:

if (empty($response['error'])) {
    if (isset($_POST['username'])) {
        $username = $_POST['username'];
        $password = $_POST['password'];
        $stmt = $db->prepare("SELECT * FROM Login WHERE username= :username"); 
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        if ($row  = $stmt->fetch(PDO::FETCH_ASSOC)) {
           $hash = $row['hash'];
           if(password_verify($password, $hash)) {
              $_SESSION['username'] = $username;
              $_SESSION['userid'] = $stmt['ID'];
           } else {
              $response['error'][] = "Your password is invalid.";
           }
        } else {
           $response['error'][] = "No User is related to the Username";
        }
    } else {
      $response['error'][] = "Username is not set!";
    }
}

Having trouble with PDO queries

2 Answers2