Jump to content

Trouble with Joining Tables in prepared select statement


BuildMyWeb
Go to solution Solved by mac_gyver,

Recommended Posts

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'; }

 

Link to comment
Share on other sites

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 by BuildMyWeb
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.