How do I use SQL INNER JOIN & ALIAS with foreign keys to fetch data from a database?

I am trying to display data in a table as the code below shows. I'm trying to get data for these 3 columns in Requisitions table(view) from a database table called users:

  1. Requisitioned By
  2. Approved By and
  3. Disbursed By

Tables in the db are as follows. I am going to omit columns that seem to be working correctly.

fin_requisitions

| req_id | req_by | approved_by | disbursed_by |
| ------ | ------ | ----------- | ------------ |
|    1    |    2    |     4     |      3       |
|    2    |    4    |     4     |      2       |

users table

| id | username |
| ------ | ------ | 
|    1    |    Lisa    | 
|    2    |    Anne    |
|    3    |    John    |

The output is always Lisa even when it should be a different username. I'm a noob but from what I've learnt so far the problem can be easily solved by using SQL ALIAS correctly but I have no idea how so I ask, how do I fix this gurus?

 <table class="table table-striped">
                <thead>
                    <tr>
                        <td>ID</td>
                        <td>File</td>
                        <td>Expense Category</td>
                        <td>Amount Requisitioned</td>
                        <td>Details</td>
                        <td>Requisitioned By</td>
                        <td>Requisition Date</td>
                        <td>Approval Status</td>
                        <td>Amount Approved</td>
                        <td>Approved By</td>
                        <td>Date Approved</td>
                        <td>Disbursement Status</td>
                        <td>Amount Disbursed</td>
                        <td>Disbursed By</td>
                        <td>Date Disbursed</td>
                        <td>Note</td>
                        <td colspan=2>Actions</td>
                    </tr>
                </thead>
                <tbody>
                    <hr>

                    <?php
                    $sql = "SELECT fin_requisition.req_id, ops_files.file_name, fin_expense_cats.expense_cat, fin_requisition.amount, fin_requisition.details,
    users.username, fin_requisition.req_date, fin_approval_status.status, fin_requisition.amt_approved, users2.username, 
    fin_requisition.approval_date, fin_disb_status.status, fin_requisition.amt_disbursed, users3.username, fin_requisition.date_disbursed,
    fin_requisition.notes FROM fin_requisition
    INNER JOIN ops_files ON fin_requisition.file = ops_files.file_id
    INNER JOIN fin_expense_cats ON fin_requisition.expense_cat = fin_expense_cats.cat_id
    INNER JOIN users ON fin_requisition.req_by = users.id 
    INNER JOIN fin_approval_status ON fin_requisition.approval_status = fin_approval_status.status_id
    INNER JOIN users users2 ON fin_requisition.approved_by = users2.id 
    INNER JOIN fin_disb_status ON fin_requisition.disb_status = fin_disb_status.status_id 
    INNER JOIN users users3 ON fin_requisition.disbursed_by = users3.id ORDER BY req_id ASC";
                    $result = $mysqli->query($sql);
                    ?>

                    <?php foreach ($result as $row) : ?>
                        <tr>
                            <td><?php echo $row["req_id"]; ?></td>
                            <td><?php echo $row["file_name"]; ?></td>
                            <td><?php echo $row["expense_cat"]; ?></td>
                            <td><?php echo $row["amount"]; ?></td>
                            <td><?php echo $row["details"]; ?></td>
                            <td><?php echo $row["username"]; ?></td>
                            <td><?php echo $row["req_date"]; ?></td>
                            <td><?php echo $row["status"]; ?></td>
                            <td><?php echo $row["amt_approved"]; ?></td>
                            <td><?php echo $row["username"]; ?></td>
                            <td><?php echo $row["approval_date"]; ?></td>
                            <td><?php echo $row["status"]; ?></td>
                            <td><?php echo $row["amt_disbursed"]; ?></td>
                            <td><?php echo $row["username"]; ?></td>
                            <td><?php echo $row["date_disbursed"]; ?></td>
                            <td><?php echo $row["notes"]; ?></td>
                            <td>
                                <a href="edit_requisition.php?req_id=<?php echo ($row["req_id"]); ?>" class="btn btn-primary">Edit</a>
                            </td>
                            <td>
                                <a href="delete_requisition.php?req_id=<?php echo ($row["req_id"]); ?>" method="post">
                                    <button class="btn btn-danger" type="submit" onclick="return confirm('Are you sure you want to delete this record?')">Delete</button>
                                </a>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>


Read more here: https://stackoverflow.com/questions/65718379/how-do-i-use-sql-inner-join-alias-with-foreign-keys-to-fetch-data-from-a-datab

Content Attribution

This content was originally published by cal ibra at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: