Echo data from two different mysql tables using php

Question

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

Alex · Accepted Answer · 2016-06-21 13:50:04Z

1

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

answered Jun 21, 2016 at 13:50

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mario Over a year ago

Thank you! Works perfectly fine.

jiboulex · Accepted Answer · 2016-06-21 13:50:22Z

1

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.

answered Jun 21, 2016 at 13:50

jiboulex

3,0412 gold badges20 silver badges31 bronze badges

1 Comment

mario Over a year ago

Thank you! Works perfectly fine.

Collectives™ on Stack Overflow

Echo data from two different mysql tables using php

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related