Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    105

    Multiple Database Connections in PHP

    Is there a way to join data from two different database connections (e.g. MSSQL and Informix)?

    For example,
    Code:
    Select * from
    informixdb.sometable
    inner join
    mssql.someothertable
    on ...
    We currently use something similar in Crystal Reports but it also appears to make separate queries and then the application joins them. I think I could be able to come up with a function that can join recordsets from two database queries using a likely very inefficient series of while and foreach loops, associative multilevel arrays, and a few regexes, but before I attempted this I wanted to know if anyone had an idea of how this could be done either natively or with some open source already existing.

    Thanks!

  2. #2
    when you query a db, you do something like *dbtype*_query() in php no ? like mssql_query();, informix_query(); and so ... how would you be able to mix 2 types.

    I think you're going to have to do the long way

  3. #3
    Join Date
    Sep 2004
    Posts
    105
    I thought so. But I'm up for the challenge.

  4. #4
    If you have to do a lot of request for this project (using both db), I would consider to merge the databases or use MSSQL or Informix for both database. Is there a reason why you use two different db ?

  5. #5
    Join Date
    Sep 2004
    Posts
    105
    Quote Originally Posted by maxime1983
    If you have to do a lot of request for this project (using both db), I would consider to merge the databases or use MSSQL or Informix for both database. Is there a reason why you use two different db ?
    Basically it's because I work for a mega-huge corporation and there are different DB groups that manage the different data sources. Come on, you know life can't be that simple.

    Anyway, I decided to give myself until Wednesday to see if I can figure out an algorithm to do this.

  6. #6
    For now, the only way I see is to do the first request and get the data. Then, you construct the second request with the data you got from the first request.

    There is maybe another way to handle that but I don't know what it is!

    Good luck...

  7. #7
    Join Date
    Oct 2002
    Location
    Hong Kong
    Posts
    165
    In MS SQL server, you can setup a linked server. This way, you can query the MS SQL server which can do the join for you with another database (from another server) if you call it inside a stored procedure.. its a bit tricky, but i've done it before (but that was from 2 seperate MS SQL servers), not sure it supports different database engines as well. But you can try.

  8. #8
    Join Date
    Sep 2004
    Posts
    105
    I'm making progress with this. I've been doing PHP for about two years but this is the first class I've written.

    Heres what I can do so far:

    Connect to a mySQL server
    Connect to a SQL server
    Run queries on both types and treat the results as virtual tables
    Inner join the two virtual tables with conditions via the USING construct.
    Use a rudimentary WHERE clause. Conditions are treated inline, and right now I support AND and OR (but no parentheses, and it'll probably stay that way). I also support the =, !=, <>, <, >, <=, and >= operators (but right now not IN or BETWEEN).
    Sort them with ASC and DESC. It doesnt work properly when sorting by the primary key (i.e. if its already sorted) as Im using some open-source code I got from php.net to sort the multi-dimensional array. Ive also only tested it with one column so far but have coded it for multiple conditions.
    When you refer to columns, you dont have to prepend the table name. If the same column name exists in both tables, it will associate it with the first table (the one between FROM and JOIN).

    Today I added:

    Select specific columns, and return them with aliases using AS.
    Can now refer to column names case-insensitive.


    When I do it with the tables I need for my application, I'll have to add the GROUP BY clause, so that should be tons of fun. I can't test it with the real data because I'm waiting for the powers that be to let my IP through the firewall.

    I'll post the class when I'm all done. This is how it's invoked:

    PHP Code:
    $q = new Query($virtualSqlStatement);
    $q->mysql($mysqlHostname$mysqlUsername$mysqlPW$mysqlDBName$mysqlQuery [, $tableAliasOne]);
    $q->mssql($mssqlHostname$mssqlUsername$mssqlPW$mssqlDBName$mssqlQuery [, $tableAliasTwo]);
    $result $q->exec(); 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •