PHP MySQL Select multiple tables

Question

Is it posibble to select 3 tables at a time in 1 database?

Table 1: employee
              --> employee_id
              --> first_name
              --> last_name
              --> middle_name
              --> birthdate
              --> address
              --> gender
              --> image
              --> salary

Table 2: logs
              --> log_id
              --> full_name 
              --> employee_id
              --> date
              --> time
              --> status

Table 2: logout
              --> log_id
              --> full_name 
              --> employee_id
              --> date
              --> time
              --> status

I wanted to get the value of employee table where $id of selected. Then the $id also get the value of log.time, log.date, logout.time, and logout.date.

I already try using UNION but nothing happens.

What do you mean by "nothing happens"? Could you post the exact query that you're having problems with? — Ignas R
– Ignas R, Commented Jun 2, 2010 at 6:05

Dan Heberden · Accepted Answer · 2010-06-02 06:06:09Z

5

Something like:

SELECT * FROM `employee` as `e` ( LEFT JOIN `logs` ON `e`.`employee_id` = `logs`.`employee_id`) LEFT JOIN `logout` ON `e`.`employee_id` = `logout`.`employee_id` WHERE `e`.`employee_id` = $id

You want to join the tables together using their related column, the employee id. This sql statement first joins employee to logs, then that result is joined to logout.

answered Jun 2, 2010 at 6:06

Dan Heberden

11.1k3 gold badges35 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Entendu · Accepted Answer · 2010-06-02 06:46:08Z

3

Up to you to put it in your app, but the query looks like this. Dan's query will not return exactly what you said you're looking for.

SELECT e.*, l.time, l.date, lo.time, lo.date 
  FROM employee e
  LEFT JOIN logs l 
    ON l.employee_id = e.employee_id
  LEFT JOIN logout lo
    ON lo.employee_id = e.employee_id
  WHERE e.employee_id = {your id here}

answered Jun 2, 2010 at 6:46

Entendu

1,24510 silver badges12 bronze badges

2 Comments

Jorge Over a year ago

But how can I get the value of l.time? If i use $row['time']; only the lo.time is showed.

Entendu Over a year ago

In the first line, change "lo.time" to "lo.time AS logout_time". Now you should be able to access $row['logout_time']

Martijn Engler · Accepted Answer · 2010-06-02 08:18:15Z

1

Jordan,

To expand on the query Entendu gave (I can't reply to that), you can use aliases:

SELECT e.*, l.time AS l_time, l.date AS l_date, lo.time AS lo_time, lo.date AS lo_date

FROM employee e LEFT JOIN logs l ON l.employee_id = e.employee_id LEFT JOIN logout lo ON lo.employee_id = e.employee_id WHERE e.employee_id = {your id here}

This way you can call $row['l_time'] to get the value of l.time

answered Jun 2, 2010 at 8:18

Martijn Engler

1062 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Select multiple tables

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related