Select data from two different databases

[krzys]

I have 2 databases (with different user names and different
passwords), and I need to join column_1 from table_A (on first
database: database_I) on column_2 from table_B (on second database:
database_II).
How query should likes? I want to do this in php - where should I
specify different user names and diffrent passwords for databases?


<?php
$dbhost="server _name\instance_ name,port_numbe r";
$dbuser1="user1 ";
$dbuser2="user2 ";
$dbpass1="passw ord1";
$dbpass2="passw ord2";
$dbname1="datab ase_I";
$dbname2="datab ase_II";
$db1=mssql_conn ect($dbhost,$db user1,$dbpass1) ;
$db2=mssql_conn ect($dbhost,$db user2,$dbpass2) ;
mssql_select_db ($dbname1,$db1) ;
mssql_select_db ($dbname2,$db2) ;
$query="SELECT. ..";


?>

[Gordon Burditt]

Re: Select data from two different databases

>I have 2 databases (with different user names and different

>passwords), and I need to join column_1 from table_A (on first
>database: database_I) on column_2 from table_B (on second database:
>database_II) .

It's fairly easy to access more than one database in PHP - just
open a connection to more than one, and use the resource for the
appropriate connection for each query. That, however, doesn't imply
being able to use tables from both in the same query.

>How query should likes? I want to do this in php - where should I
>specify different user names and diffrent passwords for databases?

A query must be directed to a particular database. If that database
is capable of doing a join with another database (some cannot), you
have to use whatever syntax is needed to do that. This probably
requires giving one database the login parameters for the OTHER
database.

For example, if you were using (sufficiently recent) MySQL, you
could create a Federated table on one server which references the
data stored on the other server, and then run the query to join the
local and the Federated table on that server. (You have to be sure
that the Federated storage engine is enabled).

I don't know how you do it in MSSQL, or if it is possible.

[Luuk]

Re: Select data from two different databases

krzys schreef:

I have 2 databases (with different user names and different
passwords), and I need to join column_1 from table_A (on first
database: database_I) on column_2 from table_B (on second database:
database_II).
How query should likes? I want to do this in php - where should I
specify different user names and diffrent passwords for databases?
>
>
<?php
$dbhost="server _name\instance_ name,port_numbe r";
$dbuser1="user1 ";
$dbuser2="user2 ";
$dbpass1="passw ord1";
$dbpass2="passw ord2";
$dbname1="datab ase_I";
$dbname2="datab ase_II";
$db1=mssql_conn ect($dbhost,$db user1,$dbpass1) ;
$db2=mssql_conn ect($dbhost,$db user2,$dbpass2) ;
mssql_select_db ($dbname1,$db1) ;
mssql_select_db ($dbname2,$db2) ;
$query="SELECT. ..";
>
>
?>

<?php
$dbhost="server _name\instance_ name,port_numbe r";
$dbuser1="user1 ";

$dbpass1="passw ord1";

$dbname1="datab ase_I";
$dbname2="datab ase_II";
$db1=mssql_conn ect($dbhost,$db user1,$dbpass1) ;

mssql_select_db ($dbname1,$db1) ;

$query="SELECT ... FROM database_I.tabl e1, database_II.tab le2 WHERE ....";

[Jessica Griego]

Re: Select data from two different databases


"Gordon Burditt" <gordonb.kqgjb@ burditt.orgwrot e in message
news:iOSdnffOcJ CBdITUnZ2dnUVZ_ v3inZ2d@posted. internetamerica ...

I have 2 databases (with different user names and different
>>passwords), and I need to join column_1 from table_A (on first
>>database: database_I) on column_2 from table_B (on second database:
>>database_II ).

>
It's fairly easy to access more than one database in PHP - just
open a connection to more than one, and use the resource for the
appropriate connection for each query. That, however, doesn't imply
being able to use tables from both in the same query.
>

>>How query should likes? I want to do this in php - where should I
>>specify different user names and diffrent passwords for databases?

>
A query must be directed to a particular database. If that database
is capable of doing a join with another database (some cannot), you
have to use whatever syntax is needed to do that. This probably
requires giving one database the login parameters for the OTHER
database.
>
For example, if you were using (sufficiently recent) MySQL, you
could create a Federated table on one server which references the
data stored on the other server, and then run the query to join the
local and the Federated table on that server. (You have to be sure
that the Federated storage engine is enabled).
>
I don't know how you do it in MSSQL, or if it is possible.

Yes, you can do it with MSSQL...that was exploited a few years back with the
advent of MSDE. The default install used 'admin' with a blank password...and
users were too stupid or lazy to change it. So, remote into a MSSQL port as
admin, enumerate all MSSQL servers on the network, replicate yourself to
them, do your harm. I digress, yes, it is easily done in MSSQL. I'd only
create a view of the remote/secondary table(s) involved in the other server.
From there, connect to the one and pull your view into a query of it's
related, local table.