Undefined index when using inner join SQL statement

user_6360664 0
Viewed 467

Hi I'm trying to write an SQL statement to populate a table but I keep getting an error:

Undefined index: st.Name, and Undefined index: s.Name.

I don't get why I'm getting that because I have selected them in the SQL statement. I'm not very good at SQL so help would be appreciated.

<?php
ini_set("display_errors", 1);
try{
    $stmt = $conn->prepare(
        "SELECT st.Name, s.Name
        From Sports AS s INNER JOIN Choices AS c
        ON s.Sport_ID = c.Sport_ID INNER JOIN Student_Choices AS sc
        ON sc.T1_Choice = c.Choice_ID INNER JOIN Students AS st
        ON st.Username = sc.Username
    ");
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo '<tr>
                <td>'.$row['st.Name'].'</td>
                <td>'.$row['s.Name'].'</td>
         </tr>
        ';
    }
}
catch(PDOException $e)
{
    echo "error".$e->getMessage();
}
?>
pdo join mysql sql
1 Answers

There are multiple issues. Firstly, your presumption is wrong that you will be able to access these column values using the <table/alias-name>.<column/alias-name>. Instead, they are accessible only using, either the column name, or the defined alias.

Now, in this case, you have two columns with the same name. So, you should define different Aliases for them, to avoid ambiguous behaviour. I have defined them student_name and sport_name.

Once you have defined the Aliases, you can now access those column value(s), using the Alias name only.

        $stmt = $conn->prepare(
          "SELECT st.Name AS student_name, 
                  s.Name AS sport_name 
          From Sports AS s INNER JOIN Choices AS c
          ON s.Sport_ID = c.Sport_ID INNER JOIN Student_Choices AS sc
          ON sc.T1_Choice = c.Choice_ID INNER JOIN Students AS st
          ON st.Username = sc.Username
        ");
        $stmt->execute();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
          echo '<tr>
              <td>'.$row['student_name'].'</td>
              <td>'.$row['sport_name'].'</td>
            </tr>
          ';
        }
user_24693080
Related