Compare Two MySQL tables using PHP PDO return Column data from either table based on if row exists

Question

I'm trying to run some PHP to search table 2 based on ID and if the ID exists then return data from the row.

My structure goes something like this

user_login - ID || USERNAME || EMAIL || PASS || CREATED DATE
user_details - ID (sameID as above) || USER FIRST NAME || LAST NAME || Address....

For the admin panel I'm trying to display the users "Username" from table 1 if they have not completed the full registration. So if they're ID is not present in table 2 then display username from table 1.

I'm still learning, and I know my code is not the correct way to do this. It does work, but I feel like it's sloppy and i'm having trouble finding resources on the best way to compare two tables.

// search db for id in table user_details, if nothing then return username.

$userID = $_SESSION['$userID'];
                              
// PDO Prepaired prepair and execute 
$navBarsql = 'SELECT * FROM user_details WHERE user_login_ID = :user_login_ID;';
                              
$stmt = $pdo->prepare($navBarsql);
$stmt->execute([
    'user_login_ID' => $userID,
                              
]);
                              
$row = $stmt->fetch(PDO::FETCH_ASSOC);
                              
if($row){                      
    echo $row['userFname'] . " " . $row['userLName'];                     
}else{
    // PDO Prepaired prepair and execute 
    $navBarsql = 'SELECT * FROM user_login WHERE id = :id;';
                                      
    $stmt = $pdo->prepare($navBarsql);
    $stmt->execute([
        'id' => $userID,                             
    ]);
                                      
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    // search user db for username

    echo ucfirst($row['username']);                          
}

I've seen some crazy long sql statements that seems like you can do this within the query but none of my attempts have gotten me anywhere.

Thanks so much for all the help!

Answer 1

Use a LEFT JOIN to combine them into a single query. If there's no row in user_details those columns will be NULL.

$userID = $_SESSION['$userID'];
                              
// PDO Prepaired prepair and execute 
$navBarsql = 'SELECT ul.username, ud.userFname, ud.userLname
              FROM user_login AS ul
              LEFT JOIN user_details AS ud ON ul.id = ud.user_login_ID
              WHERE ul.id = :user_login_ID;';
                              
$stmt = $pdo->prepare($navBarsql);
$stmt->execute([
    'user_login_ID' => $userID,
]);
                              
$row = $stmt->fetch(PDO::FETCH_ASSOC);
                              
if($row){
    if ($row['userFname'] !== null) {
        echo $row['userFname'] . " " . $row['userLName'];
    } else {
        echo ucfirst($row['username']);
    }
} else {
    echo "UserID $userID not found.";
}