Pulling Data from Multiple Tables

[bwyant32]

Ok I have a fairly straight forward question. I am designing a baseball website and I am trying to add in a schedule, here is what I have:

 

Data Bases

1) Teams DB - Each team has a unique team ID, location, logo, and owner

2) Schedule DB - Each game has a unique game ID, and the schedule is set up like this: AWAY = Team ID, HOME = Team ID....

 

So I assume I am using the JOIN command to pull the schedule from both databases, any idea how the SQL command would look?

[Muddy_Funster]

why have you stored the information in two different databases?

 

also, what the SQL looks like depends entierly on what you want to return form the tables

[bwyant32]

I want to use whats in the schedule DB (the team IDs) and pull those IDs from the teams DB and give me the actual team names

[bwyant32]

The thing is there are 30 teams and I will be creating 30 team schedules so thats why I wanted to pull by team ID

[seanlim]

can you clarify if the data is in multiple "DBs" as stated in your question or "Tables" as stated in your title? they are actually quite different as Muddy_Funster was trying to point out.

 

Data can be easily joined when they are in the same DB on different tables, but it will probably get messy if they are in totally separate databases

[bwyant32]

My fault, they are all in the same DB just different tables. My apologies.

[seanlim]

If that's the case, you'll probably need something to the effect of...

 

SELECT * FROM schedules s LEFT JOIN teams h ON s.home = h.id LEFT JOIN teams a ON s.away = a.id

[bwyant32]

That worked perfectly... this is what I am running into right now:

http://www.stlbleague.com/test/tm.php?tid=1 (Team ID 1 is Arizona)

 

Take the first game listed, Game ID #5 it shows Away - Arizona and Home - Arizona. When it should be Arizona v. St. Louis. So for some reason it is not matching up the actual game IDs. Here is my code:

 

$id = $_GET['tid']; // get var from URL


/* Get data. */
$sql = "SELECT * FROM schedule s LEFT JOIN teams h ON s.home=h.tid LEFT JOIN teams a ON s.away=a.tid WHERE away='$id' OR home='$id'" ;
$result = mysql_query($sql);

?>

<table width="962" border="0" cellpadding="2" cellspacing="1" class="stats">
<tr class='theader'>
<td align='center'><a href='?sort=roster.player&order=<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?>'>ID</a></td>
<td align='center'><a href='?sort=roster.player&order=<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?>'>Away</a></td>
<td align='center'><a href='?sort=roster.tmabb&order=<?php echo $order == 'DESC' ? 'ASC' : 'DESC' ?>'>Home</a></td>
</tr>

<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["gid"];
$field2 = $row["full"];
$field3 = $row["full"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td>$field1</td><td align='center'>$field2</td><td align='center'>$field2</td></tr>";
}
echo "</table>";
?>

[seanlim]

Of course they are the same! You have:

 

$field2 = $row["full"];
$field3 = $row["full"];

 

I would be shocked if they were different!

 

Take a look at his thread that was just resolved: http://www.phpfreaks.com/forums/index.php?topic=356057.msg1682808#msg1682808

[bwyant32]

Of course they are the same! You have:

 

$field2 = $row["full"];
$field3 = $row["full"];

 

I would be shocked if they were different!

 

Take a look at his thread that was just resolved: http://www.phpfreaks.com/forums/index.php?topic=356057.msg1682808#msg1682808

 

Ok I see the AS command and that looks like it would solve the issue. How would I incorporate that into my code? I'm drawing the blank there.

[seanlim]

The AS keyword will allow you to "rename" a column, possibly into something more friendly.

 

I don't know what the name of your columns are, but the select statement should look something like:

 

SELECT s.*, h.name AS home_team_name, a.name AS away_team_name FROM...

 

This will select all columns in the schedules table, and allow you to read the name columns from the two joined tables.

 

In PHP, you can read it from the array as $row['home_team_name'] and $row['away_team_name'].

 

HTH

[Muddy_Funster]

The AS keyword will allow you to "rename" a column, possibly into something more friendly.

...

or possibly into a sensless single letter, whichever works for you.  using AS does the same thing as leaving a space and then adding in a stupid single letter, it creates an alias, a usable reffrence to the database, table or colum that you are querying.  how usable the refference is by other people - and yourself in 6 months time - all depends on how much sense the alias its self makes.