Mysql loop data from 2 table

Question

I have 2 table as below:

Table 1: common_member

+-----------+-----------+
| uid       | username  |
+-----------+-----------+
| 1         | haha      |
| 2         | walao     |
| 3         | alamak    |
| 4         | hero      |
| 5         | theone    |
| 6         | nobody    |
+-----------+-----------+

Table 2: labour_slog

+--------------+-------------+--------------+-------------+
| uid          | slaveid     | masterid     | bytime      |
+--------------+-------------+--------------+-------------+
| 1            | 2           | 3            | 123456      |
| 4            | 5           | 6            | 456789      |
+--------------+-------------+--------------+-------------+

I fetch the data as script below:

$queryLabourSlog = DB::query("SELECT * FROM ".DB::table('labour_slog')." ORDER BY id desc");
    while($rowLabourSlog = DB::fetch($queryLabourSlog)) {
        $user_list_slog[] = $rowLabourSlog;
    };
    array_multisort($idss, SORT_DESC, $user_list_slog);

In my html, I use

<!--{loop $user_list_slog $value}-->{$value[uid]} on {$value[bytime]} forced hire {$value[masterid]}'s employee {$value[slaveid]}.<!--{/loop}-->

The html will display:

1 on 123456 forced hire 3's employee 2.
4 on 456789 forced hire 6's employee 5.

How do I join the Table 1's username data to get the loop display as below?

haha on 123456 forced hire alamak's employee walao.
hero on 456789 forced hire nobody's employee theone.

Thanks.

sfandler · Accepted Answer · 2015-11-09 13:37:04Z

2

SELECT table_slog.*, u1.`name`, u2.`name` FROM `table_slog` LEFT JOIN `common_member` AS u1 ON `table_slog`.`uid`=u1.`uid` LEFT JOIN `common_member` AS u2 ON `table_slog`.`slaveid`=u2.`uid` LEFT JOIN `common_member` AS u3 ON `table_slog`.`masterid`=u3.uid ORDER BY `id` DESC

answered Nov 9, 2015 at 13:37

sfandler

6408 silver badges25 bronze badges

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

Comments

Anton Ohorodnyk · Accepted Answer · 2015-11-09 13:55:58Z

0

Something like that:

SELECT `uid_main`.`username` AS `main_username`,
  `uid_main`.`uid` AS `main_uid`,
  `uid_master`.`username` AS `master_username`,
  `uid_master`.`uid` AS `master_uid`,
  `uid_slave`.`username` AS `slave_username`,
  `uid_slave`.`uid` AS `slave_uid`,
  `ls`.`bytime` AS `bytime`
FROM `labour_slog` AS `ls`
  LEFT JOIN `common_member` AS `uid_main` ON (`ls`.`uid` = `cm`.uid)
  LEFT JOIN `common_member` AS `uid_master` ON (`ls`.`master_id` = `cm`.uid)
  LEFT JOIN `common_member` AS `uid_master` ON (`ls`.`slave_id` = `cm`.uid)
ORDER BY `cm`.`uid` DESC

edited Nov 9, 2015 at 13:55

answered Nov 9, 2015 at 13:36

Anton Ohorodnyk

8836 silver badges20 bronze badges

Collectives™ on Stack Overflow

Mysql loop data from 2 table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related