Showing two different values depending on SESSION value in INNER JOIN

I have two different tables, one named users, and another named transactions. Transactions contains wallet1, wallet2, amount. Users contains user details such as firstname, lastname, and wallet. I am trying to display the corresponding first name and last name, depending on whether or not the SESSION_wallet is equal to wallet1 or wallet2 within transactions. I tried searching for a while, and came up with a solution for showing the correct display name for the first and last name making the transfer, however, I am trying to make it display the correct value for “Transfer to:”

Here is some of my code to get a better understanding of what I mean:

MySQLi Query:

$result2 = mysqli_query($link, "SELECT * FROM transactions INNER JOIN users ON transactions.wallet1 = users.wallet WHERE transactions.wallet1 = '" . $_SESSION["wallet"] . "' OR transactions.wallet2 =  '" . $_SESSION["wallet"] . "' Order by transactions.id DESC LIMIT 5 ");

PHP Code:

<?php  
if(mysqli_num_rows($result2) > 0)  
{  
    while($row = mysqli_fetch_array($result2))  
    {  
    ?>  

The table that needs to display the transfer from, and transfer to:

<?php
if ($_SESSION["wallet"] == $row["wallet1"]) {
    echo "<td>Transfer to ".$row["firstname"]." ".$row["lastname"]."</td>";
}
else if ($_SESSION["wallet"] == $row["wallet2"]) { 
    echo "<td>Transfer from ".$row["firstname"]." ".$row["lastname"]."</td>";
}
?>

Right now my tables are only showing the first and last name of the user that made the Transfer, however, I need it to display the first and last name of the user that the transaction is made to as well. The else if code is working correct, but the first part is not showing the corresponding value.

Answer

You will need to JOIN your transactions table to your users table twice, once to get each users name. Then to avoid duplicate column names overwriting the results in the output array, you will need to use column aliases. Something like this should work:

$result2 = mysqli_query($link, "SELECT t.*, 
                                u1.firstname AS w1_firstname,
                                u1.lastname AS w1_lastname,
                                u2.firstname AS w2_firstname,
                                u2.lastname AS w2_lastname
                                FROM transactions t
                                INNER JOIN users u1 ON t.wallet1 = u1.wallet
                                INNER JOIN users u2 ON t.wallet2 = u2.wallet
                                WHERE t.wallet1 = '{$_SESSION["wallet"]}'
                                   OR t.wallet2 = '{$_SESSION["wallet"]}'
                                ORDER BY t.id DESC 
                                LIMIT 5 ");

Then you can access each user’s names as $row['w1_firstname'] etc.:

if ($_SESSION["wallet"] == $row["wallet1"]) {
    echo "<td>Transfer to ".$row["w2_firstname"]." ".$row["w2_lastname"]."</td>";
}
else if ($_SESSION["wallet"] == $row["wallet2"]) { 
    echo "<td>Transfer from ".$row["w1_firstname"]." ".$row["w1_lastname"]."</td>";
}

Note that ideally you should use a prepared query for this, for example:

$stmt = $link->prepare("SELECT t.*, 
                        u1.firstname AS w1_firstname,
                        u1.lastname AS w1_lastname,
                        u2.firstname AS w2_firstname,
                        u2.lastname AS w2_lastname
                        FROM transactions t
                        INNER JOIN users u1 ON t.wallet1 = u1.wallet
                        INNER JOIN users u2 ON t.wallet2 = u2.wallet
                        WHERE t.wallet1 = ? 
                           OR t.wallet2 = ?
                        ORDER BY t.id DESC 
                        LIMIT 5");
$stmt->bind_param('ss', $_SESSION["wallet"], $_SESSION["wallet"]);
$stmt->execute();
$result2 = $stmt->get_result();