How to retrieve data from a one to many sql relationship using PHP

Question

I have two tables. One is called "employee" and has a column named "role". I have another table with the name "role" that has two columns in it "role_id" and "name".

I have linked the two in MySQL with a foreign key (I think!), by going to relationship view through phpmyadmin and selecting the appropriate settings.

Now in PHP I have the following code:

$employees = "SELECT employee_id, first_name, last_name, role 
                 FROM employee 
                 WHERE status = 1"; 
$employee = mysqli_query($link, $employees);

foreach ($employee as $employeeInfo) {
    <?php echo $employeeInfo['role'];?>
}

However, this returns the number I have assigned to the role in the employee's table. Not the name from the linked table.

I think it has something to do with having to use the JOIN condition is the PHP code. However, doing some experimentation just gets me blank results.

Do you have any tips for outputting the role name from the second table in this case?

Thank you

Mysql `Join` is the way to go, mind sharing what you have tried with that so we can see what the problem is? — catcon, May 31 '20 at 06:52
SELECT employee_id, first_name, last_name, role FROM employee WHERE status = 1 JOIN role as name ---- One problem I have is that I dont really know how to use join. It seems like it might affect the output, so I cant use mysqli_query anymore? — David Edwards, May 31 '20 at 06:57
https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins may be worth a read to understand more about the types of joins. — Nigel Ren, May 31 '20 at 07:03

Hardood · Answer 1 · 2020-05-31T07:01:17.933

0

Use join to get the name of the role.

$employees = "SELECT a.employee_id, a.first_name, a.last_name, b.name as role 
                 FROM employee a join role b on a.role = b.role_id
                 WHERE a.status = 1"; 
$employee = mysqli_query($link, $employees);

foreach ($employee as $employeeInfo) {
    echo $employeeInfo['role'];
}

edited May 31 '20 at 07:01

answered May 31 '20 at 06:56

Hardood

503
1
5
15

score 0 · Accepted Answer · edited May 31 '20 at 07:05

0

Inner Joins should really work...

SELECT employee_id, first_name, last_name, role.name as role 
    FROM employee 
    INNER JOIN role ON employee.role = role.role_id 
    WHERE status = 1;

edited May 31 '20 at 07:05

Nigel Ren

56,122
11
43
55

answered May 31 '20 at 06:56

bloo

1,416
2
13
19

How to retrieve data from a one to many sql relationship using PHP

2 Answers2