如何在html表格单元格中分别显示group_concat结果? [英] How to display group_concat result seperately in html table cell?
问题描述
关于我的上一个问题 这是SQL结果: 这是html表的一部分: 我设法显示了预期的输出 但是在单独显示group_concat值时出现了另一个问题.我需要 这应该是预期的输出 更新:我发现(也许)与我想做的事情类似的输出(忘了我在哪里看到此链接)
链接 这就是我的想法: 我在 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: Here is the SQL query that I used: This is the SQL result: This is the html table part: I managed to display the expected output But another problem occurred in displaying the group_concat values separately. I need the values in 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: I use 这篇关于如何在html表格单元格中分别显示group_concat结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！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) <?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>
预期的输出(仅5行，没有重复的班级ID) P2和P3中的值以仅显示来自group_concat的一个结果.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;
GROUP_CONCAT值等于或大于2的情况下两次使用了SUBSTRING_INDEX函数，但是在此示例中，我停止了3个计数.我还在GROUP_CONCAT中添加了ORDER BY lid ASC，以确保它将返回lid排序的值. 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
$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";
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)<?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>
Expected Output (with 5 rows only, no duplicate class id)P2 and P3 to display only one result from the group_concat.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;
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.
