I have a dynamic MySQL pivoted table that works well, it outputs student marks according to the assessements that student has written. However, I have a challenge with modifying the output of the data. For example

1. if the student got less than 50% the table should change the color of that mark to red, and it green if it is more than 50%

2. The student mark should have the % sign after the digit e.g 50% not 50

How do I add the above modifications to the table?

In the current t

Below is the code

$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,
      ''', 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, ',
   
    ROUND(student_subject_averages.ca_average) as CA,
    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>";

      // printing table headers
      for($i=0; $i<mysqli_num_fields($res); $i  )
      {
          $field = mysqli_fetch_field($res);
          echo "<th class='background'>{$field->name}</th>";
      }
      echo "</tr>\n";

      // printing table rows
      while($row = $res->fetch_row())
      {
      
        if ($row['5'] <= $pass_rate) {
        $class = 'class=text-danger';
     
    } else {
        $class = 'class=text-info';
    }
    echo "<tr>";
       

   
            if ($cell === NULL) { $cell = '-'; }
            
         
            echo "<td $class>$cell</td>";
          }
          echo "</tr>\n";
      }
      $res->free();
      echo "</table>";

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

CodePudding user response：

This should give some direction:

<?php
  // Connect to the database and fetch the data
  // ...

  // Iterate over the data and create the table rows
  foreach ($data as $row) {
    // Check the value of the mark and determine the CSS class
    if ($row['mark'] < 50) {
      $class = 'mark-red';
    } else {
      $class = 'mark-green';
    }

    // Generate the HTML for the table row
    echo '<tr>';
    echo '<td>' . htmlspecialchars($row['student_name']) . '</td>';
    echo '<td >' . $row['mark'] . '%</td>';
    echo '...';
    echo '</tr>';
  }
?>