使用PHP从Relational数据库获取数据时出错

Question

I have designed a simple relational database. When I am trying to get the data from the server it is throwing an error: (I have skipped some code to make it simple)

This is the SQL syntax I am using:

$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";

This is used inside 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>";

This code is giving an error, when I change $row["lead.id"] to $row["id"] it works but I need to mention the table name as I have same column names in almost all the tables.

is there any way to do it using the table name?

</div>

Answer 1

You have the on condition in wrong place and with incorrect and condition you should use on condition for each table

$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";

and for index try using alias avoiding table name and dot notation

  $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";