CODEWITHSUNDEEP
phpmysql
Swee Hong
Swee Hong

Reputation: 539

Mysql loop data from 2 table

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.

Upvotes: 1

Views: 42

Answers (2)

Anton Ohorodnyk
Anton Ohorodnyk

Reputation: 883

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

Upvotes: 0

sfandler
sfandler

Reputation: 640

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

Upvotes: 2

Related Questions