如何在PHP中修改MySQL动态PIVOT表的输出数据

需求说明 #

需要对现有动态PIVOT表实现两处修改：

• 分数低于50%时标记为红色，高于50%时标记为绿色
• 所有分数后添加百分号（如显示50%而非50）

修改方案 #

1. 为分数添加百分号 #

在SQL查询中直接为所有分数字段拼接百分号，覆盖动态生成的测评列、CA和Average列：

• 动态测评列：修改GROUP_CONCAT中的MAX(IF)逻辑，用CONCAT将分数与%拼接
• CA和Average列：用CONCAT包裹ROUND后的计算结果

2. 按分数值设置文本颜色 #

在PHP输出表格时，遍历每个单元格，通过字段名区分分数列与非分数列，对分数提取数值后判断样式：

• 分数<50：使用Bootstrap的text-danger类（红色）
• 分数>50：使用text-success类（绿色）
• 非分数列（如科目、教师、评语等）保持默认样式

修改后的完整代码 #

$db=mysqli_connect(env("DB_HOST"),env("DB_USERNAME"),env("DB_PASSWORD"),env("DB_DATABASE")) or die ("Connection failed!");
$result = $db->multi_query("SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(assessements.id = ''',
      assessements.id,
      ''', CONCAT(marks.mark, ''%''), NULL)) AS ',
      replace(assessement_name, ' ', '')
        )
      ) INTO @sql
from c_a__exams  INNER JOIN assessements ON assessements.id=c_a__exams.assessement_id 
 INNER JOIN marks ON marks.assessement_id=c_a__exams.assessement_id WHERE c_a__exams.term_id=".$term.";
SET @sql = CONCAT('SELECT 
    subjects.subject_name as Subject, 
    ', @sql, ',
   
    CONCAT(ROUND(student_subject_averages.ca_average), ''%'') as CA,
    CONCAT(ROUND(student_subject_averages.student_average), ''%'') as Average,
    
    (CASE WHEN student_subject_averages.student_average BETWEEN report_comments.from AND report_comments.to THEN report_comments.comment END) AS Comment,
     (CASE WHEN student_subject_averages.student_average BETWEEN report_comments.from AND report_comments.to THEN report_comments.symbol END) AS Symbol,
  
     concat(salutation,lastname) Teacher
     from marks 
    INNER JOIN assessements ON assessements.id = marks.assessement_id
    INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
    INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
    INNER JOIN users ON users.id = marks.teacher_id
     INNER JOIN student_subject_averages ON student_subject_averages.student_id = marks.student_id
                  INNER JOIN report_comments 
                   WHERE marks.student_id = ".$student." AND `assessements`.`term_id` = 1 AND marks.active=1 AND student_subject_averages.teaching_load_id=marks.teaching_load_id AND report_comments.user_type=1 and report_comments.section_id='1' AND student_subject_averages.student_average BETWEEN report_comments.from AND report_comments.to
    GROUP BY
    marks.student_id,student_subject_averages.student_id,
    subjects.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;");

if ($err=mysqli_error($db)) { echo $err."<br><hr>"; }
if ($result) {
  do {
  if ($res = $db->store_result()) {
      echo "<table class='table table-sm table-bordered' width=100% border=0><tr>";

      // 存储字段名，用于后续判断分数列
      $fieldNames = [];
      // 打印表头
      for($i=0; $i<mysqli_num_fields($res); $i++)
      {
          $field = mysqli_fetch_field($res);
          $fieldNames[] = $field->name;
          echo "<th class='background'>{$field->name}</th>";
      }
      echo "</tr>
";

      // 打印表格行
      while($row = $res->fetch_assoc())
      {
          echo "<tr>";
          foreach($row as $colName => $cell) {
              // 处理空值
              $displayCell = $cell === NULL ? '-' : $cell;
              $styleClass = '';
              
              // 排除非分数字段，避免错误应用样式
              $nonScoreColumns = ['Subject', 'Comment', 'Symbol', 'Teacher'];
              if(!in_array($colName, $nonScoreColumns)){
                  // 提取分数的数字部分
                  $scoreValue = floatval(str_replace('%', '', $displayCell));
                  if($scoreValue < 50){
                      $styleClass = 'class="text-danger"';
                  } elseif($scoreValue > 50){
                      $styleClass = 'class="text-success"';
                  }
                  // 若需处理等于50的情况，可在此添加逻辑
              }
              
              echo "<td $styleClass>$displayCell</td>";
          }
          echo "</tr>
";
      }
      $res->free();
      echo "</table>";

    }
  } while ($db->more_results() && $db->next_result());
}
$db->close();

关键修改点说明 #

1. SQL查询部分：

   • 动态测评列：将marks.mark替换为CONCAT(marks.mark, ''%'')，直接在查询结果中生成带百分号的分数
   • CA和Average列：用CONCAT(ROUND(...), ''%'')替代原有的ROUND(...)，确保平均值也带百分号

2. PHP输出部分：

   • 改用fetch_assoc()获取关联数组，通过字段名精准判断是否为分数列
   • 定义nonScoreColumns数组过滤非分数字段，避免对科目、评语等内容错误应用颜色样式
   • 提取分数中的纯数值，根据阈值设置对应的Bootstrap文本颜色类

内容的提问来源于stack exchange，提问作者Ation