2017-03-17 01:34
浏览 139

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

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">
                        <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>
                <tbody><!-- Loop for the subjects -->

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 -->

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

if ($CONN->connect_error){
    echo("Error de conexion");
    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>"; 
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"

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doufei4418
    doufei4418 2017-03-17 01:40

    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.

    点赞 评论
  • dounai6613
    dounai6613 2017-03-17 01:38

    You have two names in your select. The period and the table name before it is discarded. So, do something like this:

    SELECT s.id, s.name as s_name, s.description, s.hours, c.name 

    Then you can look for s_name in the result set rather than s.name.

    If you really, really want s.name, you can do that as well:

    SELECT s.id, s.name as `s.name`, s.description, s.hours, c.name 

    Note that you need the backticks, because periods are not usually allowed in identifiers.

    点赞 评论