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

Question

I have a database table cranedetails(fields are : 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  

I have another table 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

I want to display values like this, eg:

id:    name:        cranetype

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

and so on.

How to get table cranedetails field cf_cranetype values (2,3) name from table cranetype cf_ctnamefield (ROUGH TERRAIN CRANES,ALL TERRAIN CRANES).

I am sect $did=1. and use this code

$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;?>

but the output is:

id:    name:        cranetype

1      Alexy       ROUGH TERRAIN CRANES

I want:

id:    name:        cranetype

1      Alexy       ROUGH TERRAIN CRANES,ALL TERRAIN CRANES

Answer 1

You can use simply GROUP_CONCAT() together with FIND_IN_SET() for that:

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

Output is:

+----+----------+---------------------------------------------------------------------------------------+
| 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

If you want to group the result only by name (because there are duplicated names), just remove cd.cf_did from the query and add DISTINCT to filter duplicated cranetype values, i.e:

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

Result will be:

+----------+---------------------------------------------------------------------------------------+
| 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