更简单的 MYSQL 语句解决方案，并从 My 数据中构建 PHP 关联数组 - Simpler solution to MYSQL statements and building PHP associative array out of My data

Simpler solution to MYSQL statements and building PHP associative array out of My data

我正在尝试获得第一次被诊断患有癌症的患者，癌症名称和类型。我已经设法检索了它们，但我相信一定有一种更简单的方法可以做到这一点。

$id = array_values(mysqli_fetch_array($conn->query("SELECT diagnosis_id from ddpt where patientnhs_no = '$nhsno' order by diagnosis_id asc limit 1")))[0];
    //get the date of the first diagnosis using the diagnosis id 
    $date = array_values(mysqli_fetch_array($conn->query("select date from diagnosis where diagnosis_id ='$id'")))[0];
    //get the id of the cancer name
    $ctype_id = array_values(mysqli_fetch_array($conn->query("select ctype_id from diagnosis where diagnosis_id ='$id'")))[0];
    //get the id of the histology name
    $hname_id = array_values(mysqli_fetch_array($conn->query("select hname_id from diagnosis where diagnosis_id ='$id'")))[0];
    //get the name of the cancer type
    $cname = array_values(mysqli_fetch_array($conn->query("select name from cancertype where ctype_id ='$ctype_id'")))[0];
    //get the name of the histology 
    $ctype = array_values(mysqli_fetch_array($conn->query("select hist_name from histologynames where hname_id ='$hname_id'")))[0];

您可以在

一个查询中选择多个列，甚至可以使用join：

$query = $conn->query("SELECT diagnosis_id, date, ctype_id, hname_id, name, hist_name
               FROM ddpt
               JOIN diagnosis ON diagnosis.diagnosis_id = ddpt.diagnosis_id
               JOIN cancertype ON cancertype.ctype_id = diagnosis.ctype_id
               JOIN histologynames ON histologynames.hname_id = diagnosis.hname_id
               WHERE patientnhs_no = '$nhsno'
               ORDER BY ddpt.diagnosis_id ASC LIMIT 1");
$result = array_values(mysqli_fetch_array($query));

代码本身需要优化，但对于基本用法来说没关系。

通过在查询中连接表，在一个语句中获取您需要的所有内容，

"select column1,column2,column3... from table1,table2,table3";

然后使用 foreach 和 if 语句循环访问。

表诊断中的 3 项可以在 1 个查询中完成。这个想法的基础是这样的：

$result = mysql_query("
    SELECT date, ctype_id, hname_id 
    FROM diagnosis 
    WHERE diagnosis_id ='$id
'");
$row = mysql_fetch_array($result, MYSQL_ASSOC)
$date = $row["date"];
$ctype_id = $row["ctype_id"];
$hname_id = $row["hname_id"];