#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5

    Php + mysqli - 1 query - 2 databases


    Hi,

    I was wondering if it's possible to create a set of results from a single query interrogating two (or more) databases, using mysqli.

    I'm hoping that someone will tell me that it's posssible, is easy, and will outline it for me.

    As far as I can see, it isn't possible as mysqli_connect() declares the database that is to be used, but in all likelihood it'll turn out that what I'm asking is bread-&-butter to you clever chaps.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,542
    Rep Power
    595
    You are correct. It isn't possible with a single query.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Thanks gw1500se,

    You've saved me hours trying to work out how to do something that can't be done.

    Originally Posted by gw1500se
    You are correct. It isn't possible with a single query.
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Two database SERVERS, or two DATABASES on the same server? You can do cross-database joins with mysql, but only if they're two data stores on the same mysql server.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Two DATABASES, on the same server. I know that it's possible with mysql but as mysqli selects the database in the course of creating a connection using mysqli_connect(), rather than separately using mysql_select_db(), I thought that it couldn't be done.

    Could you please enlighten me, ManiacDan?

    Originally Posted by ManiacDan
    Two database SERVERS, or two DATABASES on the same server? You can do cross-database joins with mysql, but only if they're two data stores on the same mysql server.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Just fully qualify the query:

    SELECT field1, field2 FROM database1.table1 JOIN database2.table2 ON whatever = something;
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    ManiacDan, I can do the query ok, but it's the connection that is causing me confusion -

    Code:
    $con=mysqli_connect($servername,
    $database1_username,
    $database1_password,
    $database1);
    Code:
    $query = ("SELECT database1.person.streetname AS streetname, 
    database2.person.shoesize AS shoesize 
    FROM database1.person 
    JOIN database2.person 
    ON database1.person.uid=database2.person.uid");
    			
    if ($stmt = mysqli_prepare($con, $query)) {
    
    mysqli_stmt_execute($stmt);
    
    etc etc
    The connection "$con" states database1 so mysqli_prepare() uses database1 - at what stage does it connect to database2, and how does it get the connection details that enable it to connect to database2?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,542
    Rep Power
    595
    With the fully qualified names in the query, as long at the databases are on the same server, it will go through the initial connection even though the database is different. This assumes, of course that the MySQL grants are set correctly on database2 for the user running httpd.

    Comments on this post

    • foreverforever agrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Choosing the database auto-fills the fully-qualified database name for you, otherwise you'd get the "no database selected" error. You've already connected to the server. Selecting the database is for your convenience.

    Comments on this post

    • foreverforever agrees
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    gw1500se & ManiacDan - glad to read that it can be achieved, and therefore I can do it.

    Many thanks to you both.

IMN logo majestic logo threadwatch logo seochat tools logo