[SOLVED] Joining Databases

[madfouked]

Right Hello all, I have been racking my brains for days now and cant figure this one out. Everytime I think I have I fail again.

 

I have no solid code to show you but I'll try and explain in the best way I can what I am trying to acheive.

 

I have ten teams in a table called teams_users all with a int number 1 - 10 coloum called id

 

I have another table called fixtures with coloums g1ht g1at g2ht g2at (Game1HomeTeam Game1AwayTeam is what the g1ht and g1at stands for) all the way though to g5ht and g5at. Each coloum relates has a number 1 - 10 relating to the id number in the teams table.

 

Now when I want to fetch and echo the table, I cant figure out how to have the entrys on the fuxture table that say 2 display the team name from the id coloum 2 from the teams table.

 

Hope anyone can help, not asking anyone to write the code just point me in the right direction or explain a good why.

 

The nearest I got was using the str_replace statement, but as close as I was I'm sure I was wayyyyyy off.

 

Cheers in advance also

 

Chris

[madfouked]

PS

 

Also if you have a better idea on setting up the fixture database, that could save this hassel either way I'm just looking for a fix.

 

Cheers

Chris

 

PS PS

 

This is how the page looks now http://www.peterleeffl.co.uk/fixtures.php

 

As you can see the fixtures are just as numbers and I replaced on team with the str_replace statement but just the last result from the fixtures query.

 

Anyway I'll continue to think will let you know my out come.

[Barand]

Fixtures - have each match in a separate row

[pre]

team                    fixture

----------              ----------

id        ------+        id

name            |        date

                +------< home_team

                +------< away_team

[/pre]

 

to get the team names you then have join twice to the team table using different table aliases

 

SELECT f.date, t1,name, t2.name

FROM fixture f

INNER JOIN team t1 ON f.home_team = t1.id

INNER JOIN team t2 ON f.away_team = t2.id

 

As you have it now you will have to join to the team table 10 times!

[madfouked]

Hey thanks for the reply,

 

I thought I was going to have to go down the JOIN road. Think I got it but I cant figure out how the echo the result.

 

This the code I have come up with. The difference in the database is that I have number the weeks 1,2,3,4 etc etc rather than a date.

 

So now my databases are.

 

teams_users --> id, name  (with the other info not needed for this)

fixtures --> id, home_team, away_team, week

 

and this is the code that produces http://www.peterleeffl.co.uk/fixtures.php

 

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could Not Connect To MySQL: ' . mysql_error() );

mysql_select_db (DB_NAME) OR die ('Could Not Select The Database: ' . mysql_error() );

$query2 = "SELECT * FROM fixtures AS f INNER JOIN teams_users AS t1 ON f.home_team = t1.id INNER JOIN teams_users AS t2 ON 

f.away_team = t2.id ORDER BY f.week ASC";

$result2 = @mysql_query ($query2);

if ($result2) {

while ($row2 = mysql_fetch_array($result2, MYSQL_BOTH)) {

     echo '$row2[teams_users.name] vs $row2[teams_users.name]';} } else {

echo '<p>This Is Fucked</p><p>' . mysql_error() . '</p>'; }

 

[Barand]

<?php
$query2 = "SELECT f.week, t1.name as hometeam, t2.name as awayteam 
        FROM fixtures AS f 
        INNER JOIN teams_users AS t1 ON f.home_team = t1.id 
        INNER JOIN teams_users AS t2 ON f.away_team = t2.id 
        ORDER BY f.week ASC";

$result2 = @mysql_query ($query2);

if ($result2) {

    while ($row2 = mysql_fetch_array($result2, MYSQL_BOTH)) {

         echo $row2['hometeam'] . ' vs ' . $row2['awayteam']; 
    }
    
} else {

    echo '<p>This Is Fucked</p><p>' . mysql_error() . '</p>'; 
}
?>

[madfouked]

Nice One, Life Saver, Well not so much life saver but hero. Thats the first time I aint been able to work something out for myself. So I hope I can be a help to others around here in time.

 

Cheers Again, I had previously tried AS hometeam AS awayteam. But I had put them in the wrong place. Anyway great I learned something new.

 

Problem Solved