CODEWITHSUNDEEP
phpmysqlselectforeign-key-relationship
Manikandan
Manikandan

Reputation: 699

How to print the table using foreign key information?

Friends i have 3 table as follows

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),
);

These are my 3 tables. Now i need to view the employee table as below

employee_id | employee_name | designation_name | department_name |employee_salary

How do i get it?? Below is my error program

<?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";
}
?>

Upvotes: 1

Views: 1242

Answers (2)

ArnoHolo
ArnoHolo

Reputation: 317

You ask for employee_salary but in your table, the row is called 'salary'. Pay attention to that !

Moreover, you should use LEFT OUTER JOIN for your SQL query like this :

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

You have to change these lines as well :

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>";

Upvotes: 2

Munees Majid
Munees Majid

Reputation: 767

TRY WITH THIS

select employees.employee_id,
employees.employee_name,
designations.designation_name,
departments.department_name,
employees.salary as employee_salary
  from employees
  left join departments on employees.department = departments.department_id 
  left join designations on employees.designation = designations.designation_id

Upvotes: 1

Related Questions