Retrieving info from 2 MySQL databases

[bart]

I am trying to retrieve from 1 database the specific names of teams (no problem).

Then, what i want to do is for each of the team names, pull info from another mysql database where the details match the team name.

How can i do this?

I have tried...

$sql1 = "SELECT * FROM $team_table";
$result1 = mysql_query($sql1);

while ($team_data = mysql_fetch_array($result1))
{
$id = $team_data["id"];
$name = $team_data["name"];

$sql2 = "SELECT * FROM table2 WHERE team_name = '$name'";
$result2 = mysql_query($sql2);

while ($query_data = mysql_fetch_array($result2))
{
$client = $query_data["client"];
etc...

echo "etc etc etc";
}
}

All i get is 1 row output not all the info I want.

Any ideas?

[bastien]

not another DB?

"select team_table.teamID, teamname_table.name from
team_table, teamname_table where team_table.teamID = teamname_table.teamID"

[bart]

OK - I have changed my code to be...

$sql = "SELECT $team_table., $undercollection_table. FROM $team_table, $undercollection_table WHERE $team_table.team_name = '$undercollection_table.SalesManager'";
$result = mysql_query($sql);
if (!$result) {
mysql_err();
exit; }

but how do I know specify what each column in each table is to be?

is it simply a case of
while ($query_data = mysql_fetch_array($result))
{
$name = $query_data["team_table.name"];
echo "ect etc etc...";
}
?

Sorry if it should be obvious!

[Spectro]

AFAIK, the "$query_data["team_table.name"];" won't work.
It's because 'team_table.name' is returned only as 'name'. I'm not sure tough.

I think you'd set up your selects as show below:

select db_a.column_a as acolumna, db_a.column_b as acolumnb, db_b.column_a as bcolumna, db_b.column_b as bcolumnb from db_a, db_b where ...

But i'd be wrong... you'd test it anyway.

[bastien]

select also as you are using $team_table, this makes PHP look for a variable called team_table, is this what you want to do? if not and the table name is TEAM_TABLE then remove the '$' signs to indicate this