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:
- Requisitioned By
- Approved By and
- 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 | 1 | 2 | 3 | | 2 | 1 | 2 | 3 |
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 JOIN ops_files ON fin_requisition.file = ops_files.file_id JOIN fin_expense_cats ON fin_requisition.expense_cat = fin_expense_cats.cat_id JOIN users ON fin_requisition.req_by = users.id JOIN fin_approval_status ON fin_requisition.approval_status = fin_approval_status.status_id JOIN users users2 ON fin_requisition.approved_by = users2.id JOIN fin_disb_status ON fin_requisition.disb_status = fin_disb_status.status_id 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["username2"]; ?></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["username3"]; ?></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>
Advertisement
Answer
In sql you should use prpoer alias name for the diffent username eg: username usename2 , username3
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 username2 , fin_requisition.approval_date , fin_disb_status.status , fin_requisition.amt_disbursed , users3.username username3 , fin_requisition.date_disbursed , fin_requisition.notes FROM fin_requisition JOIN ops_files ON fin_requisition.file = ops_files.file_id JOIN fin_expense_cats ON fin_requisition.expense_cat = fin_expense_cats.cat_id JOIN users ON fin_requisition.req_by = users.id JOIN fin_approval_status ON fin_requisition.approval_status = fin_approval_status.status_id JOIN users users2 ON fin_requisition.approved_by = users2.id JOIN fin_disb_status ON fin_requisition.disb_status = fin_disb_status.status_id JOIN users users3 ON fin_requisition.disbursed_by = users3.id ORDER BY req_id ASC
then refer to these column alias in php code
<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["username2"]; ?></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["username3"]; ?></td> <td><?php echo $row["date_disbursed"]; ?></td> <td><?php echo $row["notes"]; ?></td>