php - 从mysql的两个表中检索数据

Question

我有两张 table

表1

 HTNO          SUBJECTCODE          INTERNALS          EXTERNALS            TOTAL
   1               s1                   20                  58                 78
   1               s2                   15                  20                 35
   1               s3                   10                  60                 70 
   2               s1                   10                  20                 30
   2               s2                   12                  30                 42
   2               s3                   15                  55                 70
   .
   .
   .
   so on up to N

表2

  SUBJECTCODE             SUBJECT NAME
       s1                    MATHS
       s2                   SCIENCE
       s3                    SOCIAL

我会给学生一个表格让他们输入 hallticket 号码

如果学生在表格中输入 1 那么结果应该是

   Subjectcode        SubjectName        Internals       Externals        Total
       s1                Maths              20              58              78
       s2               Science             15              20              35
       s3                Social             10              60              70

上面应该是输出

但在这里我无法从结果中的 Table2 中检索 SubjectName

这是我正在使用的代码

    <?PHP
    $userInputEntities = htmlentities($userInput);
    echo $userInputEntities;

    $username = "admin";
    $password = "123456";
    $database = "test";
    $server = "localhost";
    $db = new PDO ("mysql:host=$server;dbname=$database", "$username", "$password");

    if ($db) {
    $id = $_GET['id'];
    $SQL = $db->prepare("SELECT * FROM Table1 WHERE htno = :id");
    $SQL -> execute(array(':id'=>$id));
    $n = $SQL->rowCount();
    echo "
    <center><table class='dynamic styled with-prev-next' data-table-tools='{'display':true}' align=center>
    <thead>
    <tr>
    <TH class='table-header dark' scope='col'>SUBJECT CODE</TH>
    <TH class='table-header dark' scope='col'>SUBJECT NAME</TH>
    <TH class='table-header dark' scope='col'>INTERNALS</TH>
    <TH class='table-header dark' scope='col'>EXTERNALS</TH>
    <TH class='table-header dark' scope='col'>TOTAL</TH>

    </tr></thead><center>";          

    while ($db_field = $SQL->fetch(PDO::FETCH_ASSOC)) {


    echo "<tr><tbody>";
    echo "<td align=center>" . $db_field['SubjectCode'] . "</td>";

     echo "<td align=center>" . $db_field['Internals'] . "</td>";
   echo "<td align=center>" . $db_field['Externals'] . "</td>";
   echo "<td align=center>" . $db_field['Total'] . "</td>";

   echo "</tbody></tr>";

   }

使用此代码我无法获得学生特定科目代码的科目名称实际上我还没有编写任何代码来从 Table2 中检索主题名称，我不知道如何编写它

请帮帮我

Answer 1

试试这个:

$SQL = $db->prepare(
 "SELECT T2.Subjectcode, T2.SubjectName, T1.Internals, T1.Externals, T1.Total FROM Table1 as T1
      JOIN Table2 as T2
         ON T1.SUBJECTCODE = T2.SUBJECTCODE
   WHERE T1.HTNO = :id");