I’m working on a project where i have two tables 1 is the Users table and another one in the Department table. here i have given foreign key connection to department table to the users table and checked that is working fine.(getting data of particular foreign key). now my requirement is in a single table i want to display all users along with that the foreign key value also. here i’m attaching the screenshot of my output.
require_once "database.php"; $result = "SELECT * FROM Users"; // , Department WHERE Department.Department_ID = Users.Dpt_id $output = mysqli_query($conn, $result); <?php while ($single = $output->fetch_assoc()):?> <tr> <td><?php echo $single['firstname']; ?></td> <td><?php echo $single['lastname']; ?></td> <td><?php echo $single['email']; ?></td> <td><?php echo $single['phnumber']; ?></td> <td><?php echo $single['provider']; ?></td> <td><?php echo $single['location']; ?></td> <td><?php echo $single['Dpt_id']; ?> </td> <td><?php if ($single['Dadmin'] == 1){ echo '<p>Department admin</p>'; } elseif($single['Superuser'] == 1){ echo '<p>SUPER</p>'; } else{ echo '<p>USER</p>'; } ?></td> <?php endwhile ?>
tried code:
$result = "SELECT * FROM Users, Department WHERE Department.Department_ID = Users.Dpt_id"; $output = mysqli_query($conn, $result);
so here is my code. please help me to get the value from the foreign key.in this image i’m getting foreign key id of 1 in the td of department name
Advertisement
Answer
I think you want this result but i don’t know your column name:
SELECT u.*, d.department_name FROM USERS u LEFT JOIN Department d ON u.dpt_id = d.department_id