[RESOLVED] Joining Tables from 2 Databases

areid

Hello, I need some help joining two tables from two diffrent db's

I have look around here and on mysql.com, and have seen some examples, but it's not working for me.

What I would like to do is, get all data from table1 in db1 and if any data is in table2 from db2.

Both tables are set up exactly the same, with the same field names. And I am using MySQL.

Any help would be greatly appreciated!

nemonoman

You need a log in that gives select rights to both databases.
Log in.

The format for the query is:

SELECT column1, column2
FROM db1.table1 UNION
SELECT name
FROM db2.table2

areid

Ok so I'm not getting it to work

Here is my code:

$dbh = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Unable to connect to SQL server");
$my_db = @mysql_select_db($db) or die("Unable to select database");

SELECT tblnames.eID, tblnames.LastName, tblnames.FirstName, tblnames.autono 
FROM pan.tblnames UNION
SELECT tbl_emp.eID, tbl_emp.LastName, tbl_emp.FirstName  

FROM signature.tbl_emp;

Also, I'm not sure how I create a login that logs into two databases at once.

nemonoman

Since you are specifying the databases in your query, you don't REALLY need to say $my_db = @mysql_select_db($db) or die("Unable to select database");

You could say what results you are getting: Post any error codes:

I'm assuming you are including the query in a wrapper:

$result=mysql_query("SELECT tblnames.eID, tblnames.LastName, tblnames.FirstName, tblnames.autono

FROM pan.tblnames UNION
SELECT tbl_emp.eID, tbl_emp.LastName, tbl_emp.FirstName

FROM signature.tbl_emp; ");

Right??

if(!$result){echo mysql_error();}

You will need to be a user with access to both databases. The admin function for a database lets you create users, establish their firewall access (which servers can talk to the local database), and the privileges of the user on the database. You can do all these functions with phpMyAdmin if you are using a MySQL database.

If you don't know what this means, find somebody around you who does, or read the user manual carefully.

areid

My login does have access to all databases.

The only thing that I get is a regular MySQL fetch arrayError.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/db/test.php on line 14
You have an error in your SQL syntax near 'UNION SELECT tbl_emp.eID, tbl_emp.LastName, tbl_emp.FirstName FROM signature.t' at line 2

nemonoman

Is it possible that you are using an older version of MySQL? UNION is implemented in MySQL 4.0.0.

areid

Damn, I am running an older version....3.23.58. I guess that means its times to upgrade huh!

nemonoman

Your workaround would be to se[arately fetch the rows from both tables and merge the arrays in PHP, or to store the results in a temporary MySQL table.

areid

Ummm those might work. Ok well thanks for your help. If that doesnt work, then I'll just have to upgrade.