PHP 增删改查 : show data from 2 related tables in a single while loop

Question

我已经制作了一个 CRUD 应用程序来收集有关患者的信息。

数据库中有 2 个表:users 和 medical_records。 “users”表包含向 medical_records 表广告医疗记录的医疗人员，换句话说，就是数据。

用户 ID (uid) 是医疗记录表中的外键。我想在 medical_records 表中显示医生的全名。

为此，我首先编写了这个查询:

$sql = "SELECT `first_name`, `last_name` FROM users WHERE id =`" . $row['uid'] . "`";

结果就是想要的:

SELECT `first_name`, `last_name` FROM users WHERE id =`4;`

但是像这样回显全名 echo $row['first_name'] 。 $row['last_name']; 导致错误:未定义索引:path\to\medical_records_all_table.php 中的first_name。

我尝试从单个 while 循环内的两个表中提取数据的整个上下文是:

<table id="medical_records" class="table table-bordered table-striped" width="100%">
  <thead>
    <tr>
      <th>Nume complet</th>
      <th>Judet</th>
      <th>Data internarii</th>
      <th>Ora internarii</th>
      <th>Data operatiei</th>
      <th>Ora operatiei</th>
      <th>Fisa adaugata de</th>
      <th>Actiuni</th>
    </tr>
  </thead>
  <tbody>
    <?php
      $sql = "SELECT `mid`, `uid`, `nume_complet`, `judet`, `data_internarii_mamei`, `ora_internarii_mamei`, `data_operatiei`, `ora_operatiei` FROM medical_records";

      $result = mysqli_query($con, $sql);
      if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
          $mid = $row['mid'];
          ?>
    <tr id="<?php echo $mid; ?>">
      <td>
        <a href="view_record.php?mid=<?php echo $mid; ?>">
        <?php echo ucwords($row['nume_complet']); ?>
        </a>
      </td>
      <td><?php echo $row['judet']; ?></td>
      <td><?php echo $row['data_internarii_mamei']; ?></td>
      <td><?php echo $row['ora_internarii_mamei']; ?></td>
      <td><?php echo $row['data_operatiei']; ?></td>
      <td><?php echo $row['ora_operatiei']; ?></td>
      <td>
        <?php
          echo $sql = "SELECT `first_name`, `last_name` FROM users WHERE id =`" . $row['uid'] . ";`";
          echo $row['first_name'] . $row['last_name'];
        ?>
      </td>
      <td class="actions">
        <ul class="list-inline text-center">
          <li>
            <a title="Vezi fisa" href="view_record.php?mid=<?php echo $mid; ?>"><span class="glyphicon glyphicon-eye-open"></span></a>
          </li>
          <li>
            <a title="Editeaza" href="edit_record.php?mid=<?php echo $mid; ?>"><span class="glyphicon glyphicon-edit"></span></a>
          </li>
          <li>
            <a title="Sterge" class="delete-icn" href="#" data-mid="<?php echo $mid; ?>"><span class="glyphicon glyphicon-trash"></span></a>
          </li>
        </ul>
      </td>
    </tr>
    <?php } mysqli_free_result($result); } ?>
  </tbody>
</table>

我错过了什么？

Answer 1

尝试

$sql = "SELECT `first_name`, `last_name` FROM users WHERE id =`" . $row['uid'] . ";`";
$result = mysqli_query($con, $sql);
while ($row = mysqli_fetch_assoc($result)) {
 echo $row['first_name'] . $row['last_name'];
}

而不是

echo $sql = "SELECT `first_name`, `last_name` FROM users WHERE id =`" . $row['uid'] . ";`";
 echo $row['first_name'] . $row['last_name'];

最好的方法是在第一个 SQL 中连接 user 和medical_records 表，如下所示。

$sql = "SELECT m.mid, m.uid, m.nume_complet, m.judet, m.data_internarii_mamei, m.ora_internarii_mamei, m.data_operatiei, m.ora_operatiei, u.first_name, u.last_name FROM medical_records m join users u on m.uid = u.id";

以下不需要

echo $sql = "SELECT `first_name`, `last_name` FROM users WHERE id =`" . $row['uid'] . ";`";