Is it possible to perform a PHP query to two different DBs (MSSQL and MySQL) on different servers at once?

Question

I have two databases:

• first one is MySQL
• second one is MSSQL

I need to get some information from the MSSQL DB, however the result (Select) would be limited according to a parameter (date) coming from the MySQL one. The DBs are located in different servers.

Do I have to perform two queries for this or is possible to do it just in one ?

Thanks!

Answer 1

It would be possible to have the MySQL server defined as a linked server object in MS SQL, and therefore possible to query only the MS SQL server, calling the MySQL linked server in the query. This adds administrative overhead though and you might lose good error checking on the MySQL host if it was down.

It makes much more sense to me to just setup two connection resources and query them individually.

Answer 2

You obvisouly need two differents connections, therefore 2 queries because the resource of these connections is not the same (anyway, the function used to perform the query won't be the same, unless you are using a abstraction layer to connect to the different servers, but still, you will need 2 connection resources).

Answer 3

No, you cannot. There's no way of asking a pool of servers about a single query. You can, though, recycle the query like this:

$sql = "SELECT * FROM table";
$result = array();
foreach($serversPool as $server){
  $result []= query($server, $sql);
}

Answer 4

$con = mysql_connect(SQL_HOST,SQL_USER,SQL_PASS);
$con1 = mssql_connect(SQL_HOST1,SQL_USER1,SQL_PASS1);

mysql_query($mysql_query);
mssql_query($mssql_query);

this should work, but you need to use two different connections and queries.

Answer 5

Afaik, unless you use some sort of abstraction you cant, the underlying protocols are different. I agree with Micheal. This is how i implemented it.

//php5-sybase : Module for Sybase and MSSQL
//freetds-dev  : Implements Tabular DataStream protocol for linux which is used by Sybase and MSSQL
putenv('FREETDSCONF=/etc/freetds/freetds.conf');

function dbOpenConnection(){
        //create mssql connection
        $db = mssql_connect('host','user','pass');
        $msdb = mssql_select_db('db_name',$db);

        //create mysql connection
        $conn = mysql_connect('db_server','user','pass');
        mysql_select_db('db_name',$conn);

        return array($db, $msdb, $conn); 
}

And your freetds conf:

# A typical Microsoft server                                                                                                            
[BData]
        host = xxx.xxx.xxx.xxx
        port = 1433
        tds version = 8.0

There is no way that you could do that in one query. you have to do it in two.

If you want it in one statement write a nested query. which is something like

SELECT * FROM ms_sql_table WHERE some_var = (result_from_nested_mysql_subquery)