pulling two sets of information with one while

anthill

I'm having a bit of a "thick" day today. I swear I've done this before but forgotten how. Lets say I have a database full of names and another full of address (in no particular order). How would I pull the information from the two databases so the names and the addresses are displayed in the right order with the right person. To make it easier, there is an ID that relates to the names and addresses.

E.g.

DB1 - table.names

ID | Name
1 | John
2 | Peter

DB2 - table.addresses

ID | Address
2 | London
1 | Birmingham

So the one while statement will pull the information from them both. Although I think I should be using a for statement? I'm not sure.

to give me:

<table>
<tr>
<th>name</th>
<th>address</th>
</tr>
<tr>
<td>Peter</td>
<td>London</td>
</tr>
<tr>
<td>John</td>
<td>Birmingham</td>
</tr>
</table>

Thanks for your time

Ant

LarsLai

try this untested:

echo "<table><tr><th>name</th><th>address</th></tr>";
$sql = "SELECT *
  FROM db1.names n
 RIGHT OUTER JOIN db2.addresses a
    ON n.ID = a.ID";
$result = mysql_query($sql, $your_db_connection);
while ($row = mysql_fetch_array($result)) {
 echo "<tr><td>$row[0]</td><td>$row[1]</td></tr>";
}
echo "</table>";

anthill

Thanks for your reply.

I've never come across or used RIGHT OUTER JOIN so i'm not very proficient with this statment. I've tried it but it just seems to pull the two rows from the first table in the first database. Is this the only way of doing it?

Cheers

Ant

P.S. It works fine if i put another mysql query within the while loop but i'm guessing this isn't good practice.

mattotto77

Would have thought

Select * from table 1, table2 where table1.id = table2.id order by whatever