php - 如何使用php从另一个表获取一个表字段值

我有一个数据库表Cranedetails（字段是：cf_did , cf_firstname, cf_cranetype）

cf_did   cf_firstname     cf_cranetype
1        Alexy            2,3
2        Thomas           11,6,3
3        Thomas           5,6,11,3
4        Thomasxc         1,6,9,4
5        Thomaseg         11,12,3

我还有另一个表Cranetype（cf_did,cf_cname）

cf_ctid     cf_ctname
   1       MOBILE CRANES
   2       ROUGH TERRAIN CRANES
   3       ALL TERRAIN CRANES
   4       CRAWLER CRANES
   5       YARD CRANES
   6       ARICULATING BOOM CRANES
   7       TRUCK CRANES: ARTICULATING BOOM
   8       TRUCK CRANES: LATTICE BOOM
   9       TRUCK CRANES: TELESCOPIC BOOM
   10      TRUCK LOADER CRANES
   11      ELEVATORS
   12      ELEVATOR – HYDRAULIC

我想显示这样的值，
例如：

id:    name:        cranetype

1      Alexy       ROUGH TERRAIN CRANES,ALL TERRAIN CRANES
2      Thomas      ELEVATORS,ARICULATING BOOM CRANES,ALL TERRAIN CRANES

等等。

如何从表cf_cranetype 2,3中获取表Cranedetails字段cranetype值（cf_ctnamefield）的名称（粗糙地形，所有地形）。

我是$ did = 1。并使用此代码

$sql=mysql_query("SELECT * FROM `cf_directory` WHERE `cf_did` = '$did'",$con);
$row = mysql_fetch_array($sql);$cn= $row['11'];
$sql2=mysql_query("SELECT * FROM  `cf_cranetype`  WHERE `cf_ctid`= '$cn'",$con);
<?php  while($row2 = mysql_fetch_array($sql2)){
     $cranename=$row2['cf_ctname']; ?>
<?php echo $cranename;?>

但输出是：

id:    name:        cranetype

1      Alexy       ROUGH TERRAIN CRANES

我想要：

id:    name:        cranetype

1      Alexy       ROUGH TERRAIN CRANES,ALL TERRAIN CRANES

最佳答案

您可以将GROUP_CONCAT()与FIND_IN_SET()一起使用：

SELECT
    cd.cf_did AS id,
    cd.cf_firstname AS name,
    GROUP_CONCAT(ct.cf_ctname SEPARATOR ', ') AS cranetype
FROM
    cranedetails cd,
    cranetype ct
WHERE
    FIND_IN_SET(ct.cf_ctid, cd.cf_cranetype)
GROUP BY
    cd.cf_did,
    cd.cf_firstname

输出为：

+----+----------+---------------------------------------------------------------------------------------+
| id | name     | cranetype                                                                             |
+----+----------+---------------------------------------------------------------------------------------+
|  1 | Alexy    | ROUGH TERRAIN CRANES, ALL TERRAIN CRANES                                              |
|  2 | Thomas   | ARICULATING BOOM CRANES, ELEVATORS, ALL TERRAIN CRANES                                |
|  3 | Thomas   | ALL TERRAIN CRANES, YARD CRANES, ARICULATING BOOM CRANES, ELEVATORS                   |
|  4 | Thomasxc | CRAWLER CRANES, ARICULATING BOOM CRANES, TRUCK CRANES: TELESCOPIC BOOM, MOBILE CRANES |
|  5 | Thomaseg | ALL TERRAIN CRANES, ELEVATORS, ELEVATOR – HYDRAULIC                                   |
+----+----------+---------------------------------------------------------------------------------------+
5 rows in set

如果只想按名称对结果进行分组（因为名称重复），只需从查询中删除cd.cf_did并添加DISTINCT即可过滤重复的cranetype值，即：

SELECT
    cd.cf_firstname AS name,
    GROUP_CONCAT(DISTINCT ct.cf_ctname SEPARATOR ', ') AS cranetype
FROM
    cranedetails cd,
    cranetype ct
WHERE
    FIND_IN_SET(ct.cf_ctid, cd.cf_cranetype)
GROUP BY
    cd.cf_firstname

结果将是：

+----------+---------------------------------------------------------------------------------------+
| name     | cranetype                                                                             |
+----------+---------------------------------------------------------------------------------------+
| Alexy    | ROUGH TERRAIN CRANES, ALL TERRAIN CRANES                                              |
| Thomas   | ELEVATORS, ALL TERRAIN CRANES, YARD CRANES, ARICULATING BOOM CRANES                   |
| Thomaseg | ELEVATORS, ELEVATOR – HYDRAULIC, ALL TERRAIN CRANES                                   |
| Thomasxc | TRUCK CRANES: TELESCOPIC BOOM, MOBILE CRANES, CRAWLER CRANES, ARICULATING BOOM CRANES |
+----------+---------------------------------------------------------------------------------------+
4 rows in set