CODEWITHSUNDEEP
phpmysqljoin
mario
mario

Reputation: 61

Echo data from two different mysql tables using php

I'm trying to display data from two different mysql tables on my website using php.

My aim is to display a match schedule with information from two tables. The first one includes all the teams plus their ID, the second includes all details on the games. I now want to "replace" the "home_id" and "away_id" fields with the respective team names from the first table.

The tables look as follows:

table "teams"
id  name
-----------
 1  Team 1
 2  Team 2
 3  Team 3
 4  Team 4
 ...

table "matchschedule"
id   home_id   away_id   goals_home   goals_away   date
 1      1         2          0             2       2016-05-05
 2      3         4          2             1       2016-05-06
 ...

With the following query I'm getting the required data within phpmyadmin:

SELECT 
    date, home.name, sp.goals_home, away.name, sp.goals_away 
FROM 
    matchschedule sp 
INNER JOIN 
    teams home on sp.home = home.id 
LEFT JOIN 
    teams away on sp.away = away.id

However, when I implement this query into my website and add the code below to display the data the fields "home.name" and "away.name" are always empty. What do I need to change in order to get the team names displayed?

while($row = mysql_fetch_array($query)) {
  ?>

  <table border=1>
    <tr>
      <?php
        echo "<td>".$row['date']."</td>";
        echo "<td>".$row['home.name']."</td>";
        echo "<td>".$row['goals_home']."</td>";
        echo "<td>".$row['away.name']."</td>";
        echo "<td>".$row['goals_away']."</td>";
      ?>
    </tr>
  </table>

Final result (with missing info for both team names):

2016-05-05  []  0  []  2
2016-05-06  []  2  []  1

Upvotes: 0

Views: 84

Answers (2)

jiboulex
jiboulex

Reputation: 3031

Based on your table strcture, you should do the following :

SELECT 
    date, home.name AS homeName, sp.goals_home, away.name AS awayName, sp.goals_away 
FROM 
    matchschedule sp 
INNER JOIN 
    teams home on sp.home_id = home.id 
LEFT JOIN 
    teams away on sp.away_id = away.id

So basically replace sp.home by sp.home_id and sp.away by sp.away_id and change the fields name as you have the same fields name in your tables.

Upvotes: 1

Alex
Alex

Reputation: 17289

The issue is that when in query you request home.name mysql return to php column with title name and you can not reach that column using home.name anymore. And same for away.name.

That's why you need to set proper name for returned columns.

Change your query to:

SELECT 
    date, home.name home_name, sp.goals_home, away.name away_name, sp.goals_away 
FROM 
    matchschedule sp 
INNER JOIN 
    teams home on sp.home = home.id 
LEFT JOIN 
    teams away on sp.away = away.id

and call those columns in php like:

    echo "<td>".$row['date']."</td>";
    echo "<td>".$row['home_name']."</td>";
    echo "<td>".$row['goals_home']."</td>";
    echo "<td>".$row['away_name']."</td>";
    echo "<td>".$row['goals_away']."</td>";

Upvotes: 1

Related Questions