mysql UNION SELECT from tables in different database php

[gazza110]

Hi,

I am having some trouble joining two tables together that exist in seperate databases.

In PHP you can connect to one database but not two??

I have a database that contains customer and their login (they log in with email and password).

I wanted to link this to another site (a helpdesk site) which has its own database for logins. Rather than the customer re-registering on the helpdesk site and having 2 profiles, I wanted to use the existing table for the login to this site.

I wanted to use a UNION SELECT to join the logins from DatabaseA.Customers to the login details in DatabaseB.Members

I have been trying to figure this out for over a week and need some HELP!!!

Any ideas???

Thanks in advance.

G.

 

[feherke]

Hi

In PHP you can connect to one database but not two??

Just to answer your question : no. The number of allowed connections will be limited by the database server, the operation system or the machine resources, not by PHP.

Anyway, your problem is definitely not related to PHP. For the [tt]union[/tt] the database server has to reach to the other database, and that will never happen through the client application's connection.

Connect to one of the databases and execute the SQL statement :

[b]select[/b]
id[teal],[/teal]name[teal],[/teal]password

[b]from[/b] user_table

[b]union[/b] [b]all[/b]

[b]select[/b]
id[teal],[/teal]name[teal],[/teal]password

[b]from[/b] [highlight]other_database[teal].[/teal][/highlight]user_table

Note that I suppose both databases are managed by the same server.


Feherke.

http://free.rootshell.be/~feherke/

 

[gazza110]

Hi

thanks for the reply.

Unfortunatly the databases are NOT on the same server. With my hosting company you can only set-up single databases with their own login and password associated.

I was looking at a procedure of something like ...

<?php  
$DB1Conn = mysql_connect ("localhost", "DB1username", "DB1password");  
@mysql_select_db($DB1) or die( "Unable to select database");  
$DB1result = mysql_query( "SELECT customer_id as 'id', email as 'username', email, password FROM customertable" )  
mysql_close($DB1Conn);


$DB2Conn = mysql_connect ("localhost", "DB2username", "DB2password");  
@mysql_select_db($DB2) or die( "Unable to select database");  
$DB2result = mysql_query( "SELECT id, username, email, password FROM memberstable" )  
mysql_close($DB2Conn);

?>

Once the 2 tables were stored as $DB1result and $DB2result then somehow using a UNION to join the two together and replace the SQL on the user lookup to point to this new value.

Hope this makes sense?? Can anyone help - PLEASE!!!!

Thanks,
Gary.

 

[jpadie]

i do not believe that this can be done as a single query within php. however the solution is easy enough

$DB1result = mysql_query( "SELECT customer_id as 'id', email as 'username', email, password FROM customertable" ) 
$uResults = array();
while ($row = mysql_fetch_assoc($DB1result)):
$uResults[] = $row;
endwhile;

$DB2result = mysql_query( "SELECT id, username, email, password FROM memberstable" )  
while ($row = mysql_fetch_assoc($DB2result)):
$uResults[] = $row;
endwhile;

//the union recordset is now in $uResults as an array of associative arrays.

 

[feherke]

Hi

Anyway, I see no reason to do all that. Is pointless to query two entire user tables just to get information about one user. And also pointless to query the second table while is possible to already have the result from the first table.

[teal]<?php[/teal]  

[navy]$DBConn[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_connect[/color] [teal]([/teal][green][i]"localhost"[/i][/green][teal],[/teal] [green][i]"DB1username"[/i][/green][teal],[/teal] [green][i]"DB1password"[/i][/green][teal]);[/teal]  
[navy]@mysql_select_db[/navy][teal]([/teal][navy]$DB1[/navy][teal])[/teal] [b]or[/b] [b]die[/b][teal]([/teal] [green][i]"Unable to select database"[/i][/green][teal]);[/teal]  
[navy]$DBresult[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal] [green][i]"SELECT customer_id as 'id', email as 'username', email, password FROM customertable [highlight]where email='$username_from_login_form'[/highlight]"[/i][/green] [teal]);[/teal]
[highlight palegreen][navy]$row[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_fetch_assoc[/color][teal]([/teal][navy]$DBresult[/navy][teal]);[/teal][/highlight]
[COLOR=darkgoldenrod]mysql_close[/color][teal]([/teal][navy]$DBConn[/navy][teal]);[/teal]

[highlight pink][b]if[/b] [teal](![/teal] [navy]$row[/navy][teal])[/teal] [teal]{[/teal][/highlight]
  [navy]$DBConn[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_connect[/color] [teal]([/teal][green][i]"localhost"[/i][/green][teal],[/teal] [green][i]"DB2username"[/i][/green][teal],[/teal] [green][i]"DB2password"[/i][/green][teal]);[/teal]  
  [navy]@mysql_select_db[/navy][teal]([/teal][navy]$DB2[/navy][teal])[/teal] [b]or[/b] [b]die[/b][teal]([/teal] [green][i]"Unable to select database"[/i][/green][teal]);[/teal]  
  [navy]$DBresult[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal] [green][i]"SELECT id, username, email, password FROM memberstable [highlight]where username='$username_from_login_form'[/highlight]"[/i][/green] [teal]);[/teal]
  [highlight palegreen][navy]$row[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_fetch_assoc[/color][teal]([/teal][navy]$DBresult[/navy][teal]);[/teal][/highlight]
  [COLOR=darkgoldenrod]mysql_close[/color][teal]([/teal][navy]$DBConn[/navy][teal]);[/teal]
[highlight pink][teal]}[/teal][/highlight]

[b]if[/b] [teal]([/teal][navy]$row[/navy][teal])[/teal] [teal]{[/teal]
  [b]echo[/b] [green][i]'Welcome user '[/i][/green][teal],[/teal][navy]$row[/navy][teal][[/teal][green][i]'username'[/i][/green][teal]];[/teal]
[teal]}[/teal] [b]else[/b] [teal]{[/teal]
  [b]echo[/b] [green][i]'Login failed'[/i][/green][teal];[/teal]
  [b]exit[/b][teal];[/teal]
[teal]}[/teal]

However I do not think this will lead to anything useful. The other tables probably references the user tables and they have to be joined by the user id. That will be hard to solve and slow to run through PHP.


Feherke.

http://free.rootshell.be/~feherke/