如何合并字段并在php和sql中显示输出 [英] How to merge fields together and display output in php and sql

问题描述

我不知道如何解释，但我想做的是将两个数据库字段合并在一起并使用 php 输出新字段.例如:

I don't know how to explain this but what I want to do is kind of merge two database field together and output the new field using php. For example:

Answer Table:

AnswerId: 10
AnswerContent: Manchester

AnswerId :11
AnswerContent: Leeds

AnswerId:12
AnswerContent:Birmingham

StudentAnswer Table:

Student: Bob
StudentAnswer:10

Student: Jim
StudentAnswer:11

我想要做的是，当我运行将在下面使用我的 php 编码显示的查询时，对于 StudentAnswer 字段，我希望它显示答案的名称而不是答案 ID.如何实现?

What I want to do is that when I run the query which I will show below with my php coding, for the StudentAnswer field I want it to display the name of the answer rather than the answer id. How can this be achieved?

代码如下:

<?php
  if (isset($_POST['submit'])) {

    $query = "
      SELECT * FROM Question q
        INNER JOIN StudentAnswer sa ON q.QuestionId = sa.QuestionId
        JOIN Answer a ON sa.QuestionId = a.QuestionId  
      WHERE
        ('".mysql_real_escape_string($sessionid)."' = '' OR q.SessionId = '".mysql_real_escape_string($sessionid)."')
      AND
        ('".mysql_real_escape_string($questionno)."' = '' OR q.QuestionNo = '".mysql_real_escape_string($questionno)."')
      AND
        ('".mysql_real_escape_string($studentid)."' = '' OR sa.StudentId = '".mysql_real_escape_string($studentid)."')
      AND(CorrectAnswer = '1')
      ORDER BY $orderfield ASC";

    $num = mysql_num_rows($result = mysql_query($query));
    mysql_close();

?>

<p>
  Your Search:
  <strong>Session ID:</strong> <?php echo (empty($sessionid)) ? "'All Sessions'" : "'$sessionid'"; ?>,
  <strong>Question Number:</strong> <?php echo (empty($questionno)) ? "'All Questions'" : "'$questionno'"; ?>,
  <strong>Student Username:</strong> <?php echo (empty($studentid)) ? "'All Students'" : "'$studentid'"; ?>,
  <strong>Order Results By:</strong> '<?php echo $ordername; ?>'
</p>
<p>Number of Records Shown in Result of the Search: <strong><?php echo $num ?></strong></p>
<table border='1'>
  <tr>
  <th>Session ID</th>
  <th>Question Number</th>
  <th>Question</th>
  <th>Correct Answer</th>
  <th>StudentAnswer</th>
  <th>Correct Answer Weight</th>
  <th>Student Answer Weight</th>
  <th>Student ID</th>
  </tr>
  <?php
    while ($row = mysql_fetch_array($result)) {
      echo "
  <tr>
  <td>{$row['SessionId']}</td>
  <td>{$row['QuestionNo']}</td>
  <td>{$row['QuestionContent']}</td>
  <td>{$row['AnswerContent']}</td>
  <td>{$row['StudentAnswer']}</td>
  <td>{$row['Weight%']}</td>
  <td></td>
  <td>{$row['StudentId']}</td>
  </tr>";
    }
  ?>
</table>

<?php
  }
?>

谢谢

推荐答案

我想你可以使用:

SELECT q.*, a.AnswerContent FROM Question q
   INNER JOIN StudentAnswer sa ON q.QuestionId = sa.QuestionId
   INNER JOIN Answer a ON sa.QuestionId = a.QuestionId
WHERE ....

我的意思是，您应该返回答案的文本(您仍在加入表格，因此您确实拥有该字段).

I mean, you should return the text for the answer (you're still joining tables, so you do have that field).

这篇关于如何合并字段并在php和sql中显示输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！