php - 使用 PHP 从关系数据库获取数据时出错

Question

我设计了一个简单的关系数据库。当我试图从服务器获取数据时，它抛出一个错误:(我跳过了一些代码以使其简单)

这是我使用的 SQL 语法:

$sql = "SELECT lead.id, lead.name, lead.phone, lead.email, treatment.name, source.name, status.name FROM lead join treatment join source join status on treatment.id = lead.treatment_id and source.id = lead.source_id and status.id = lead.status_id";

这在 HTML 中使用:

echo "
<tr>
<td>".$row["lead.id"]."</td>
<td>".$row["lead.name"]."</td>
<td>".$row["lead.email"]."</td>
<td>".$row["treatment.name"]."</td>
<td>".$row["source.name"]."</td>
<td>".$row["status.name"]."</td>
</tr>";

此代码出错，当我将 $row["lead.id"] 更改为 $row["id"] 时它可以工作，但我需要提及表名几乎所有表中的列名都相同。

有什么办法可以使用表名吗？

Answer 1

你在错误的地方和不正确的条件下打开了条件你应该为每个表使用条件

$sql = "SELECT 
          lead.id
        , lead.name
        , lead.phone
        , lead.email
        , treatment.name
        , source.name
        , status.name 
      FROM lead 
      join treatment on treatment.id = lead.treatment_id
      join source on source.id = lead.source_id
      join status on status.id = lead.status_id";

对于索引尝试使用别名避免表名和点符号

  $sql = "SELECT 
            lead.id as lead_id
          , lead.name as lead_name
          , lead.phone as lead_phone
          , lead.email as lead_email 
          , treatment.name as treatment_name_
          , source.name  as  source_name
          , status.name  as status_name
        FROM lead 
        join treatment on treatment.id = lead.treatment_id
        join source on source.id = lead.source_id
        join status on status.id = lead.status_id";