CODEWITHSUNDEEP
phpmysql
Raphael Jayme
Raphael Jayme

Reputation: 53

Inner join on PHP

I have two tables (matches, teams). In the matches table I have id, team1, team2 (where team1 and team2 are the team's ID). In the teams table I have id and name.. How do I print in php the matches but showing the team's name instead of their ID?

My code(Printing only teamid vs teamid): 

<?php
    require_once 'conf/conf.php';
    require_once 'functions.php';
    $query = mysql_query("SELECT time1 as t1id, time2 as t2id
                        FROM matches
                        INNER JOIN teams
                        ON
                        teams.id = matches.time1
                        ");
    //echo mysql_num_rows($query);
    while ($row = mysql_fetch_assoc($query)) 
    {
        $t1 = $row["t1id"];
        $t2 = $row["t2id"];     
        echo ($t1 . " vs. " . $t2 . "<br>");
    }
?>

Upvotes: 0

Views: 131

Answers (3)

Jacek Krysztofik
Jacek Krysztofik

Reputation: 1366

As I said in a comment to your question, join the table twice:

SELECT t1.name, t2.name
FROM matches.m
INNER JOIN teams AS t1 ON (t1.id = m.team1)
INNER JOIN teams AS t2 ON (t2.id = m.team2)

Upvotes: 0

ffflabs
ffflabs

Reputation: 17481

try this

require_once 'conf/conf.php';
require_once 'functions.php';
$query = mysql_query("SELECT teams1.id as t1id,teams1.name as name1,  teams2.id as t2id,  teams2.name as name2
                    FROM matches
                    INNER JOIN teams as teams1 ON teams1.id = matches.time1
                    INNER JOIN teams as teams2 ON teams2.id = matches.time2
                    ");
//echo mysql_num_rows($query);
while ($row = mysql_fetch_assoc($query)) 
{
    $t1 = $row["name1"];
    $t2 = $row["name2"];     
    echo ($t1 . " vs. " . $t2 . "<br>");
}

Upvotes: 2

Leng
Leng

Reputation: 2998

You can do something like this (assuming the teams table has a name column):

<?php
    require_once 'conf/conf.php';
    require_once 'functions.php';
    $query = mysql_query("SELECT time1 as t1id,
                                 time2 as t2id,
                                 t1.name AS t1name,
                                 t2.name AS t2name
                        FROM matches
                        INNER JOIN teams AS t1
                        ON t1.id = matches.time1
                        INNER JOIN teams AS t2
                        ON t2.id = matches.time2");
    //echo mysql_num_rows($query);
    while ($row = mysql_fetch_assoc($query)) 
    {
        $t1 = $row["t1name"];
        $t2 = $row["t2name"];     
        echo ($t1 . " vs. " . $t2 . "<br>");
    }
?>

Upvotes: 4

Related Questions