Showing two different values depending on SESSION value in INNER JOIN

Question

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.

Does it mean it enters the `if` branch and only outputs the static part of the echo or it doesn't enter the branch at all? — El_Vanja, Mar 21 '20 at 22:44
It enters the if branch, but it outputs the first and last name of the session. It has to output the first and last name of transactions.wallet2. — displayNamePHP, Mar 21 '20 at 22:46
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Mar 22 '20 at 01:14
Thank you, but security is not important at this stage. I just want to learn right now, then move on to improving the code later on, once I got the hang of it :-) — displayNamePHP, Mar 22 '20 at 01:15

Nick · Accepted Answer · 2020-03-22T00:47:03.037

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();

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/210083/discussion-on-answer-by-nick-showing-two-different-values-depending-on-session-v). — Samuel Liew, Mar 22 '20 at 02:04

Showing two different values depending on SESSION value in INNER JOIN

1 Answers1