How to display group_concat result seperately in html table cell?
On my previous question How to group array value duplicate and customize the display of the results of the array in html table? which has not been answered yet, I opt in other way to display the 5 rows only.
This is the database:
lid class_id class Q1-2 Q3-4 total item_group
----- ------- ----- ----- ----- ----- ----------
1 73 Leader 5000 50000 10000 33
1 77 Consultant 4000 4000 8000 33
1 83 Coordinator 3000 3000 6000 33
2 73 Leader 10000 10000 20000 33
2 76 Staff 4000 4000 8000 33
2 77 Consultant 5000 50000 10000 33
3 73 Leader 15000 15000 30000 33
3 78 Team Leader 4000 4000 8000 33
Here is the SQL query that I used:
$sql = "SELECT *, group_concat(AZ.lid) as lids, group_concat(AZ.total) AS totals FROM (
SELECT * FROM view_items WHERE lid='1'
UNION
SELECT * FROM view_items WHERE lid='2'
UNION
SELECT * FROM view_items WHERE lid='3'
) AS AZ WHERE item_group='33' GROUP BY class_id";
This is the SQL result:
class id class lid Q1-2 Q3-4 lids totals item_group
--------- ----- ------ ----- ----- ----- ------ ----------
73 Leader 1 5000 5000 1,2,3 10000,20000,30000 33
77 Consultant 1 4000 4000 1,2 8000,10000 33
83 Coordinator 1 3000 3000 1 6000 33
76 Staff 2 2 8000 33
78 Team Leader 3 3 8000 33
$proj_lid->lid is equals to 1 (Displayed in the P1 column only)
$row->lid is equals to 1 (Displayed in the P1 column only)
$limit is 3 (The count of P's)
This is the html table part:
<?php
for($iy=1; $iy<=$limit; $iy++){
$sql_lid = "SELECT * FROM view_items WHERE lid='$iy' GROUP BY lib_id ORDER BY lid ASC";
$query_lid = $this->db->prepare($sql_lid);
$query_lid->execute();
$res=$query_lid->fetch();
$lids = $res->lid;
$lid_arr[] = $lids ;
$sql_arr = "";
if($iy==1) $sql_arr .= "SELECT *, group_concat(AZ.lid) as lids, group_concat(AZ.total) AS totals FROM (";
$sql_arr.="SELECT * FROM view_items WHERE lid='$lids'";
if($iy!=$limit) $sql_arr .= " UNION ";
if($iy==$limit) $sql_arr.=" ) AS AZ WHERE item_group='33' GROUP BY class_id";
$sqlArr[] = $sql_arr;
}
$sql = implode("",$sqlArr);
$query = $this->db->prepare($sql);
$query = $this->db->prepare($sql);
$query->execute();
$result = $query->fetchAll();
foreach($result as $row){
if($row->item_group == "33"){
$q1 = ($row->q1-2)
$q3 = ($row->q3-4)
$total = $q1 + $q3;
?>
<tr>
<td><?php echo $row->class; ?></td>
<td><?php if($q1 != 0 && $proj_li->lid == $row->lid){ echo ($q1 < 0 ? "(".number_format(abs($q1),2).")" : number_format($q1,2));} else{ echo "-";} ?> </td>
<td><?php if($q3 != 0 && $proj_lid->lid == $row->lid ){ echo ($q3 < 0 ? "(".number_format(abs($q3),2).")" : number_format($q3,2));} else{ echo "-";} ?> </td>
<td><?php if($total != 0 && $proj_lid->lid == $row->lid ){ echo ($total < 0 ? "(".number_format(abs($total),2).")" : number_format($total,2));} else{ echo "-";} ?> </td>
<?php
$totals = explode(", ", $row->totals);
foreach ($totals as $rowstotals) {
}
for($i = 1; $i <= $limit; $i++){
if ($i != $proj_lid->lid && $proJ-lid->lid < $i){
?>
<td><?php if($rowstotals != 0){ echo ($rowstotals < 0 ? "(".number_format(abs($rowstotals),2).")" : number_format($rowstotals,2));} else{ echo "-";}?></td>
<?php
} else{ }
}
}
}
?>
</tr>
I managed to display the expected output
Expected Output (with 5 rows only, no duplicate class id)
But another problem occurred in displaying the group_concat values separately. I need the values in P2 and P3 to display only one result from the group_concat.
This should be the expected output
UPDATE: I found (maybe) a similar output to what I want to do (forgot where I saw this link) Link
This is what I had in mind:
SELECT lid, class_id,class, `Q1-2`,`Q3-4`,total,
CASE WHEN COUNT(*)>=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(total ORDER BY lid ASC SEPARATOR ' '),' ',2),' ',-1) END AS P2,
CASE WHEN COUNT(*)>=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(total ORDER BY lid ASC SEPARATOR ' '),' ',3),' ',-1) END AS P3
FROM (SELECT * FROM view_items WHERE lid='1' UNION
SELECT * FROM view_items WHERE lid='2' UNION
SELECT * FROM view_items WHERE lid='3' ) AS AZ
WHERE item_group='33'
GROUP BY class_id ORDER BY lid ASC;
I use SUBSTRING_INDEX function twice on a GROUP_CONCAT value that is 2 or more but in this example I stopped at 3 count. I also added ORDER BY lid ASC in GROUP_CONCAT to make sure that it will return value ordered by lid.