INNER JOIN后表中的字段未正确显示 - 未定义的索引

Question

Well, I've been trying to deal with this problem the whole afternoon/evening, and I know I'm close... still can't get it right :/

I have a database (created with phpMyAdmin as I'm learning to use it), it has subjects and careers, and fields like id, name, description, etc.

Table Subjects has an id AND a foreign key from table Careers (the id for Careers). I've done that in phpMyAdmin using the relation view.

Then... I have a main php file for the main page (like a "home"), and in there I've included a link to another php file which shows the table with all the subjects (Name of the subject, Description, Hours, Career, and Actions)

This is the table part in the Home.php:

<table class="table table-striped table-bordered table-hover tabla">
                <thead>
                    <tr>
                        <th class="thead_texto">Subject</th>
                        <th class="thead_texto">Description</th>
                        <th class="thead_texto">Hours</th>
                        <th class="thead_texto">Carrer</th>
                        <th class="thead_texto">Actions</th>
                    </tr>
                </thead>
                <tbody><!-- Loop for the subjects -->
                    <?php
                      include("../extras/tablaSubjects.php");
                    ?>
                </tbody>
</table>

And this is the php file that deals with showing the subjects and informations from the database:

<!-- This comes from another file that have the info for the conection -->
<?php
    include("conexion.php");

//Conection
$CONN = new mysqli($serverName, $username, $password, $dataBase);

//Checking
if ($CONN->connect_error){
    echo("Error de conexion");
}
else{
    echo "Conectado";
}

//Showing the subjects and information

//Query to select
$SQL = "SELECT s.id, s.name, s.description, s.hours, c.name FROM subjects m JOIN careers c ON (s.career_id = c.id)";
$resultado = $CONN->query($SQL);

//Part of the tbody where the loop will be
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
    echo "<tr>";  
        echo "<td>{$row['s.name']}</td>";
        echo "<td>{$row['description']}</td>";
        echo "<td>{$row['hours']}</td>";
        echo "<td>{$row['name']}</td>";
        echo '<td><a class="btn btn-primary" value="'.$row['id'].'">Edit</a>    <a class="btn btn-danger" value="'.$row['id'].'">Delete</a> </td>';
    echo "</tr>"; 
}
}else{
echo "<tr><td>No data<td></tr>";
}
?>

It actually works fine... except when trying to show the name of the SUBJECTS :/ In this part:

echo "<td>{$row['s.name']}</td>"; --> this gives an error, "Undefined index"
        echo "<td>{$row['description']}</td>";  --> OK, info from SUBJECT
        echo "<td>{$row['hours']}</td>"; --> OK, info from SUBJECT
        echo "<td>{$row['name']}</td>";  --> This displays the name of the CAREER :/

I can't seem to understand WHY it displays all the data from SUBJECT and even the name of the CAREER but not the name of the SUBJECT :/ If I write name on it, it still displays the name of the career. If I write "subject.name", still says "Undefined index"

Answer 1

The table prefix isn't included in the keys of the associative array returned by fetch_assoc(), they just have the column names. So if you select both s.name and c.name, there will just be one $row['name'], which contains the last one in the SELECT list.

To get both, you should give one of them an alias.

$SQL = "SELECT s.id, s.name, s.description, s.hours, c.name AS career_name FROM subjects s JOIN careers c ON (s.career_id = c.id)";

Then you can use $row['name'] for s.name and $row['career_name'] for c.name.