php - 如何使用外键信息打印表？

Question

friend 们我有3个表如下

create table departments
(
  department_id int(20) AUTO_INCREMENT primary key,
  department_name varchar(30) not null
);

create table designations
(
  designation_id int(20) AUTO_INCREMENT primary key,
  designation_name varchar(30) not null
);
create table employees
(
 employee_id int(20) AUTO_INCREMENT primary key,
 employee_name varchar(30) not null,
 department int,
 designation int,
 salary int(20) not null,
 FOREIGN KEY (department) REFERENCES departments(department_id),
 FOREIGN KEY (designation) REFERENCES designations(designation_id),
);

这是我的 3 个表。现在我需要查看员工表，如下所示

员工编号 |员工姓名 |名称_名称 |部门名称 |员工工资

如何获取？？下面是我的错误程序

<?php
$database_connection = new mysqli("localhost","root","","employee_application");
if($database_connection->connect_error)
     die ("connection failed".$database_connection->connect_error);

$sql_query = "select employees.employee_name,employees.designation,employees.department,employees.employee_salary,departments.department_id,designations.designation_id
              from employees,departments,designations
              where employees.department = departments.department_id and employees.designation = designations.designation_id";

$result=$database_connection->query($sql_query);
if($result->num_rows > 0){
    echo "<table>";
    while($rows = $result->fetch_assoc()){
        echo "<tr>";
        echo "<td>".$rows["id"]."<td>";
        echo "<td>".$rows["name"]."<td>";
        echo "<td>".$rows["department"]."<td>";
        echo "<td>".$rows["designation"]."<td>";
        echo "<td>".$rows["salary"]."<td>";
        echo "<tr>";
    }
    echo "</table>";
}
else{
echo "Table is empty";
}
?>

Answer 1

您要求 employee_salary 但在您的表中，该行称为“薪水”。请注意!

此外，您应该像这样对 SQL 查询使用 LEFT OUTER JOIN :

SELECT e.employee_id, e.employee_name, e.salary, des.designation_name, dep.department_name
FROM employees e
LEFT OUTER JOIN designations des ON e.designation = des.designation_id
LEFT OUTER JOIN departments dep ON e.department = dep.department_id

您还必须更改这些行:

echo "<td>".$rows["employee_id"]."<td>";
echo "<td>".$rows["employee_name"]."<td>";
echo "<td>".$rows["department_name"]."<td>";
echo "<td>".$rows["designation_name"]."<td>";
echo "<td>".$rows["salary"]."<td>";