How to get the name of another table when the Ids match?

Question

Well, I’m a beginner in php (although I’m trying to learn), and at the moment I’m stuck in my last step finishing a php file. I have a table called "Team", where there are columns called "ID" and "Name" and another table called "Recordbyopponent", where there is a column called "Oppid" that corresponds to the ID of the other table.

That’s my code that works normally

$sql = "SELECT * FROM `RecordByOpponent`  WHERE `TeamID` = " . $TeamID . " "; 

$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["OppID"]. "</td><td>" . $row["TotalWins"] . "</td><td>" . $row["TotalLosses"] . "</td><td>" . $row["Points"] . "</td></tr>";
}
echo "</table>";
} else { echo "0 results"; }
$conn->close();
?>

While my code works and I get the ID of the searched, I need to get the name of the Time and not the ID. The Ids in the two tables match.

How can it be solved? I can’t understand, even though I’m trying ...

Answer 1

If I understand you want to capture the data that is also in another table, you will have to use some JOIN to join the tables, your query would look something like this:

$sql = "
  SELECT * FROM `RecordByOpponent`
  INNER JOIN `Team` ON `Team`.`ID` = `RecordByOpponent`.`TeamID`
  WHERE `RecordByOpponent`.`TeamID` = " . $TeamID . "     
"; 

The idea is to unite the table Recordbyopponent to the Table Team, through the Ids, after all this is their relationship between the tables. Since you are learning the technique now, I suggest studying also about ALIAS in SQL, getting simpler your search.

So where you display in the table, just replace the ID field by the team name (Assuming it’s the city name)

echo "<tr><td>" . $row["CityName"]. "</td><td>" . $row["TotalWins"] . "</td><td>" . $row["TotalLosses"] . "</td><td>" . $row["Points"] . "</td></tr>";

It is also a technique, you do not call everything with * but manually select the columns you need (also using alias to avoid duplicity). It’s good practice for you to learn =]

Answer 2

Try it this way:

$sql = "SELECT Name.Team, *.RecordByOpponent FROM `RecordByOpponent`, `Team`  
    WHERE `TeamID` = " . $TeamID . " " AND `TeamID` = `ID`;