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
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
Use sub queries to fetch this for reference http://dev.mysql.com/doc/refman/5.7/en/subqueries.html
For me, it looks like you are storing a string value (or VARCHAR) of integer values (your cranetype ids) joined by comma.
If so, I would consider redesigning your database to have a table e.g.
In the cranedetails, make an entry for every cranetype associated with a cranename. The cf_nid and cf_ctid shoul be, of course, foreign key relations to the respective tables.
With your current design, you would have to explode the string value of your cf_cranetype to get an array of integers (or strings, looking like integers), and your second query had to look like this: "SELECT * FROM cf_cranetype WHERE cf_ctid IN '" . join($YOUR_ARRAY, ',') . "'"