显示外键数据（名称等），而不仅仅是 HTML 表中的外键

debugcn 发表于 Dev

安迪·特里

作为作业的一部分，我正在为 Drs Practice 创建一个医疗应用程序。

我有一个表格，它显示与访问表的信息，该表格链接在此处的图像中。

目前该表正在显示像这样的外键的 Id 值

例如，我如何显示文本值而不是患者姓名为 9，我想显示表格其余部分的实际姓名等。

所以它会显示为

patient name | doctor | condition | medication prescribed | Date
Andrew Terry | Dr Iqbal | Depression | Fluoxetine | 20th April 2018

当我解决这个问题时，我也想添加一个搜索，我可以在其中搜索特定时间段内具有特定条件的患者。

当前代码如下

    <?php // Include config file
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
?>
<?php // Include header File
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");
?>
<?php
if (!$db) {
    die ('Failed to connect to MySQL: ' . mysqli_connect_error());  
}

$sql  = "SELECT * FROM visit JOIN doctor
            ON visit.doctor_id = doctor.doctor_id 
            JOIN patient
            ON visit.patient_id = patient.patient_id
            WHERE patient.patient_id BETWEEN 1 AND 9
            ORDER BY date DESC";


$query = mysqli_query($db, $sql);

if (!$query) {
  die ('SQL Error: ' . mysqli_error($db));
  }
?>

<body>
<?php // Navigation File
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/nav.php");?>
<div class="container"><br><br>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>

  <h2>APPOINTMENTS</h2>

  <p>Most Recent Appointments:</p>            
  <table class="table table-striped">
  <thead>
      <tr>
        <th>Patient Name</th>
        <th>Doctor</th>
        <th>Condition</th>
        <th>Medication Prescribed</th>
        <th>Date</th>
        <th></th>
      </tr>
    </thead>
    <tbody>
    <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query))
        {
            echo '<tr>
                    <td>'.$row['patient_id'].'</td> 
                    <td>'.$row['doctor_id'].'</td> 
                    <td>'.$row['con_id'].'</td> 
                    <td>'.$row['drugs_id'].'</td> 
                    <td>'.$row['date'].'</td> 
                    <td><a href="deleteapp.php?id='.($row['visit_id']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
                    </tr>';    
            $no++;
        }?>
    </tbody>
  </table>
  <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  <a href="../" class="btn btn-info pull-right">Admin Area</a>
</div>

</body>
</html>

我已经在@kamal 帮助之后修改了代码。但现在我收到了未定义的通知错误。

代码如下

<?php // Include config file
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/config.php");
?>
<?php // Include header File
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/header.php");
?>
<?php
if (!$db) {
    die ('Failed to connect to MySQL: ' . mysqli_connect_error());  
}

$sql  = "$sql  = "SELECT patient.fName AS pfname, patient.sName AS psname, doctor.sName AS doc, conditions.con_name AS con, drugs.medication AS meds, visit.visitdate FROM visit 
            JOIN doctor ON visit.doctor_id = doctor.doctor_id
            JOIN patient ON visit.patient_id = patient.patient_id
            LEFT JOIN conditions ON visit.con_id = conditions.con_id
            LEFT JOIN drugs ON visit.drugs_id = drugs.med_id
            WHERE patient.patient_id BETWEEN 1 AND 100
            ORDER BY date DESC";


$query = mysqli_query($db, $sql);

if (!$query) {
  die ('SQL Error: ' . mysqli_error($db));
  }
?>

<body>
<?php // Navigation File
include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/nav.php");?>
<div class="container"><br><br>
<?php include("$_SERVER[DOCUMENT_ROOT]/freddies/inc/logo.html"); ?>

  <h2>APPOINTMENTS</h2>

  <p>Most Recent Appointments:</p>            
  <table class="table table-striped">
  <thead>
      <tr>
        <th>Patient Name</th>
        <th>Doctor</th>
        <th>Condition</th>
        <th>Medication Prescribed</th>
        <th>Date</th>
        <th></th>
      </tr>
    </thead>
    <tbody>
    <?php
        $no     = 1;
        $total  = 0;
        while ($row = mysqli_fetch_array($query))
        {
            echo '<tr>
                    <td>'.$row['pfName'].'</td> 
                    <td>'."Dr ".$row['doc'].'</td> 
                    <td>'.$row['con'].'</td> 
                    <td>'.$row['meds'].'</td> 
                    <td>'.$row['date'].'</td> 
                    <td><a href="deleteapp.php?id='.($row['visit_id']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
                    </tr>';    
            $no++;
        }?>
    </tbody>
  </table>
  <a href="newapp.php" class="btn btn-success pull-left">New Appointment</a>
  <a href="../" class="btn btn-info pull-right">Admin Area</a>
</div>

</body>
</html>

我现在得到以下信息。在此处输入图片说明

卡迈勒·乔希

我假设以下答案中的表格很少。

这里你的查询应该像评论中提到的@Barmar 一样：

SELECT fName,date,visit_id,patient.patient_name, doctor.name AS doctor_name, condition.name AS condition_name, drugs.name AS drugs_name FROM visit JOIN doctor
        ON visit.doctor_id = doctor.doctor_id 
        JOIN patient
        ON visit.patient_id = patient.patient_id
        LEFT JOIN condition ON visit.con_id = condition.id
        LEFT JOIN drugs ON visit.drugs_id = drugs.id
        WHERE patient.patient_id BETWEEN 1 AND 9
        ORDER BY date DESC

然后在你的 PHP 代码中，你应该在 while 循环中更改列名，如下所示：

echo '<tr>
                <td>'.$row['patient_name'].'</td> 
                <td>'.$row['doctor_name'].'</td> 
                <td>'.$row['condition_name'].'</td> 
                <td>'.$row['drugs_name'].'</td> 
                <td>'.$row['date'].'</td> 
                <td><a href="deleteapp.php?id='.($row['visit_id']).'" class="btn btn-danger pull-right btn-xs">Delete</a></td>
                </tr>';

我希望这会帮助你继续前进。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。