Trouble with Joining Tables in prepared select statement

[BuildMyWeb]

I have two tables: user_accts AND security_keys

 

im trying to run a prepared select query that gets several columns from the user-accts table where the email address provided in a login form is equal to the email address in a row of my table.  getting all the user data for the account logging in.

 

the second select would get a column value from the security_keys table where the value of security_key is equal in both tables.

 

the following is echoing 'problem'.

$stmt = $db_connect->prepare("SELECT ua.id, ua.status, ua.level, ua.creation, ua.f_name, ua.l_name, ua.pw, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, ua.security_key, sk.expiration FROM user_accts AS ua, security_keys AS sk WHERE ua.email = ? AND sk.security_key = ua.security_key");
    
    if( $stmt )
    {
        $stmt->bind_param("s", $login_email);
        $stmt->execute();
        $stmt->bind_result($id, $status, $level, $creation, $f_name, $l_name, $pw_db, $title, $org_name, $org_size, $manage_num, $manage_direct_num, $phone, $security_key, $expiration );
        
        while ( $stmt->fetch() )
        {
            // changes on every iteration to reflect the current row
        }
    
        $stmt->close();
    }
    
    else
    { echo'problem'; }

 

[Barand]

Instead of echoing "problem", try echoing something informative while you are debugging, like

echo $stmt->error;

[BuildMyWeb]

Notice: Trying to get property of non-object in /home/scripts/handler_login.php on line 41

 

line 41: { echo $stmt->error; }

Edited September 28, 2015 by BuildMyWeb

[mac_gyver]

the error information would be accessible via - $db_connect->error

[BuildMyWeb]

We are making the db connection. $dbconnect->errno return of zero.

[BuildMyWeb]

I had this script running well when the $stmt query was a select from a single table. The user acts table. When I edited ONLY the SELECT query in $stmt, we have the problem.

 

so this works fine for me:

$stmt = $db_connect->prepare("SELECT id, status, level, creation, f_name, l_name, pw, title, org_name, org_size, manage_num, manage_direct_num, phone FROM user_accts WHERE email=?");
    
    if( $stmt )
    {
        $stmt->bind_param("s", $login_email);
        $stmt->execute();
        $stmt->bind_result($id, $status, $level, $creation, $f_name, $l_name, $pw_db, $title, $org_name, $org_size, $manage_num, $manage_direct_num, $phone);
        
        while ( $stmt->fetch() )
        {
            // changes on every iteration to reflect the current row
        }
    
        $stmt->close();
    }

Edited September 29, 2015 by BuildMyWeb

[mac_gyver]

in your else {} logic, that corresponds to a failed prepare statement, the error information would be available in $db_connect->error, not in $stmt->error.

[BuildMyWeb]

thank you mac.  apparently i needed a back-to-basics wakeup.  i had a collation conflict.  the error message of course told me that right away and was an easy fix for something i spent hours poring over. 

[Jacques1]

To fix the error handling issues now and in the future, you should enable exceptions:

<?php

$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

$databaseConnection = new mysqli(...);

Whenever a query fails, PHP will automatically throw an exception with all relevant information. So you don't have to manually check every function call and extract the error message from some attribute.