php - 如何将值添加到动态分配的标题中

Question

我正在处理这个场景:

Students Table
IDNUMBER
NAME
LEVEL
COURSE
SECTION


Exams Table
IDNUMBER
SUBJECT_ID 
SCORE


Student_Subject Table
SUBJECT_ID
LEVEL
COURSE
SECTION

SUBJECTID_1 SUBJECTID_2 SUBJECTID_3 ... 标题是根据 Student_Subject 表中的 Students 表 LEVEL、COURSE 和 SECTION 动态生成的，然后我从 Student_Subject 表中收集 SUBJECT_ID 以从 Exams 表中获取 SCORE。

期望的输出

IDNUMBER NAME   SUBJECTID_1 SUBJECTID_2 SUBJECTID_3 ...
123456  JACK    6.5     8.5     9.0 

我能够获取标题、学生列表及其分数，但我将它们放在错误的位置。

IDNUMBER NAME   SUBJECTID_1 SUBJECTID_2 SUBJECTID_3 ...
123456  JACK    9.0     8.5     6.5 

这是我到目前为止所拥有的。

 <?php
 include('../connect.php');

// Finds the Names

$sql="SELECT * FROM student WHERE course='$course' AND yearlevel='$year' AND section     ='$section' " ;

$result = mysql_query($sql);
echo $title.' SECCIÓN: '.'"'.$section.'"'; 
echo "<table border='1' cellpadding='1' id='resultTable'><tr><th>#</th><th>NIE</th>    <th>NOMBRE COMPLETO</th>";


// Finds subjects

$sql_subjects="SELECT subject FROM studentsubject WHERE course='$course' AND   level='$year' AND section ='$section'" ; 
$result_subjects = mysql_query($sql_subjects);
$s = '1';
while($subjects = mysql_fetch_array($result_subjects))
  {
  // Muestra los códigos de las materias.
  echo "<th style='text-align:center'>".$subjects['subject']."</th>";

  $s++;
  }


  echo "</tr>";
  $i='1';
  while($row = mysql_fetch_array($result))
  {
   //Diplays  student info
    echo "<tr>";
  echo "<td style='text-align:center'>".$i."</td>";
  echo "<td>".$row['idnumber'];
  $student = $row['idnumber'];
  echo "<td>".$row['lname'].','.$row['fname']."</td>";

  $sql_grades="SELECT subject,score FROM exam WHERE idnum='$student' AND term='1'" ; 
  $result_grades = mysql_query($sql_grades);
  $g = '1';
  while($grades = mysql_fetch_array($result_grades))
  {
  // Displays the grades
  echo "<td style='text-align:center'>".$grades['score']."</td>";

   $g++;
  }

  echo "</tr>";
  $i++;
  }
 echo "</table>";

我做错了什么？预先感谢您的回复/支持。

大家好!发布这个问题后，SO 向我提供了一些与我的问题相关的建议文章，阅读了一些文章后，我能够获得我的项目所需的输出。
这就是我想到的

// Finds students info
$sql="SELECT * FROM student WHERE course='$course' AND yearlevel='$year' AND section ='$section' " ;

$result = mysql_query($sql);
echo $title.' SECCIÓN: '.'"'.$section.'"'; 
echo "<table border='1' cellpadding='1' id='resultTable'><tr><th>#</th><th>NIE</th>   <th>NOMBRE COMPLETO</th>";


// Finds the  subjects

 $sql_subjects="SELECT subject FROM studentsubject WHERE course='$course' AND level='$year' AND section ='$section'" ; 
 $result_subjects = mysql_query($sql_subjects);
 $s = '1';
 $fsubject = array();
  while($subjects = mysql_fetch_array($result_subjects))
   {
   // Stores subject ID for later use.  
   $fsubject[]  = $subjects;
   // Writes Table Headings  for "subjects"
  echo "<th style='text-align:center'>".$subjects['subject']."</th>";

  $s++;
   }

 echo "</tr>";
 $i='1';
 while($row = mysql_fetch_array($result))
  {
     //Writes students info
   echo "<tr>";
   echo "<td style='text-align:center'>".$i."</td>";
   echo "<td>".$row['idnumber'];
   $student = $row['idnumber'];
   echo "<td>".$row['lname'].','.$row['fname']."</td>";


 //Finds subjects score based on subjectID

foreach($fsubject as $subject){
   //Stores subjectID 
    $test = $subject["subject"];
   // echo $test;

  // 
  // finds de exam scores based on SubjectID = $test in this case
     $sql_grades="SELECT subject,score FROM exam WHERE idnum='$student' AND term='1'    and subject='$test' "  ; 
   $result_grades = mysql_query($sql_grades);
   $g = '1';
   while($grades = mysql_fetch_array($result_grades))
   {

   // Displays scores
   echo "<td style='text-align:center'>".$grades['score']."</td>";

   $g++;
   }

   }

  echo "</tr>";
   $i++;
   }
 echo "</table>";
  ?> 

有更好的方法吗？正在学习，不知道这样会不会给服务器造成很大的负载。

感谢您对JOIN的建议。我从中学到了很多东西，但这对我来说太过分了，不知道如何在这种情况下实现它。

Answer 1

由于您当前的代码的分数顺序相反，您可以尝试使用 ORDER BY Score DESC -

$sql_grades="SELECT subject,score FROM exam WHERE idnum='$student' AND term='1' ORDER BY score DESC" ;

但是，你应该学习如何使用JOIN ，因为您的问题是您当前的代码中主题和分数值之间没有关系。