Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Left Outer Join from two databases NOT Working

    I have two databases, pageDB and memberData

    I need to do a left outer join to add a member name to the pages the member created. When a page is created a member id is added to the pages table. The connections are made and everything works if I compare do a Left outer join for two tables inside the same database. Here's the working code that joins teasers assigned to pages:
    PHP Code:
    mysql_select_db($database_memberData$memberData);
    mysql_select_db($database_pageDB$pageDB);

    $sql "
         SELECT *
        FROM pages
        LEFT OUTER JOIN lftColTeasers
    ON pages.id =  lftColTeasers.currentPageID
        "
    ;
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            echo 
    $rec['pageName'], ' -- '$rec['currentPageID'],'<br>'"\r\n";
    ?> 
    If I try this I get nothing:

    PHP Code:
    mysql_select_db($database_memberData$memberData);
    mysql_select_db($database_pageDB$pageDB);

    $sql "
         SELECT *
        FROM pages
        LEFT OUTER JOIN members
    ON pages.memberID =  members.id
        "
    ;
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            echo 
    $rec['pageName'], ' -- '$rec['memberName'],'<br>'"\r\n";
    ?> 
    I've also discovered that only the one of the mysql_select_db methods is honored. IOW if I have the pageDB commented out a simple SELECT * FROM members will work, but the moment I remove the comments the query returns no info because there is no members table in the pageDB. IOW this does not work:
    PHP Code:
    mysql_select_db($database_memberData$memberData);
    mysql_select_db($database_pageDB$pageDB);

    $sql "
         SELECT *
        FROM members
        "
    ;
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            echo 
    $rec['memberName'], '<br>'"\r\n";
    ?> 
    But This does:
    PHP Code:
    mysql_select_db($database_memberData$memberData);
    //mysql_select_db($database_pageDB, $pageDB);

    $sql "
         SELECT *
        FROM members
        "
    ;
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            echo 
    $rec['memberName'], '<br>'"\r\n";
    ?> 
    I've looked all over and can't find a solution that will work.

    And, before you ask, it is not possible to move the members table to the pagesDB. These Databases must stay separate.
    Last edited by rgEffects; 11-02-2013 at 01:00 AM.

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,090
    Thanks
    51
    Thanked 506 Times in 493 Posts
    afaik you can't use mysql_select_db twice on the same connection - you can only use one db OR select another to switch to. Selecting a db won't (afaik) keep it open while you select another.

    If it is possible then I would assume you'd need a reference to it like the sql connection itself. I am poking at shadows though as I've never needed to even try what you're doing. According to php.net you can use a link identifier for this so you would need to open two mysql connections - theoretically:



    What you could try in your sql (good luck) is to use format <db>.<table>.<column> in your SQL instead of just the table and column. I'm not hopeful of this working but I know some other database systems in other languages support this.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Maybe a better approach would be to generate an array from each query and then do a comparison of two arrays in PHP. What I need to end up with is a list of pages and the members that have created the pages. So simple in theory, so difficult in practice.

    Member - Page

    Bob - 1
    Carol - 5
    Ted - 9
    Allice - 4

    Query 1 gives me Bob, Carol, Ted, Allice

    Query 2 gives me 1, 2, 3, 4, 5, 6, 7, 8, 9 because some pages were not created by members so the memberID column in the pages table is admin instead of member for site.

    Maybe I could do something like this if I could generate an array that would stick around:
    PHP Code:
    <?php
    mysql_select_db
    ($database_memberData$memberData);
    $sql " SELECT *FROM members";
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            
    /*create a $member array but I can't figure that out*/

    mysql_select_db($database_pageDB$pageDB);
    $sql " SELECT *FROM pages WHERE memberID <> 'admin' ";
        
        
    $run mysql_query($sql);
            while (
    $rec mysql_fetch_assoc($run))
            
    /*create an $page array but I can't figure that out*/

    //combine arrays something like this, but I can't figure that out either
      
    if ($member['id'] == $page['memberID']) echo $member['name'], ' - 'page['id'];
    ?>
    The code above will give me two lists, one of members, the other of pages that are associated with member id's, I just need to tie the member name to the member id from the pages record set. I'm probably over thinking this.

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,958
    Thanks
    2
    Thanked 304 Times in 296 Posts
    It seems you have already asked this or a similar question in a previous thread.

    If the two database are on two different database servers, no you cannot do this in one query, for the reasons listed in your previous thread.

    In this thread, you have only stated you have two database. If they are on the same database server, yes you can do this in one query as long as the database user you are making the connection with has permission to access both databases on that one server.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    tangoforce (11-02-2013)

  • #5
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,090
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    It seems you have already asked this or a similar question in a previous thread.

    If the two database are on two different database servers, no you cannot do this in one query, for the reasons listed in your previous thread.
    A very good point. I've just read that topic now you mention this and see that rg has clearly decided to leave tha vital information out hoping that we'll magically come up with an impossible answer

    rgeffects: Fo-Lu has clearly explained this to you in your previous thread. If you don't like what he says, starting a new topic will not change the impossible. You have data on two different servers and cannot access them both via one SQL query.

    Now I know your real setup that you chose not to mention, you can completely disregard my previous post as my advice was assuming your databases were on the same database server.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,090
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Actually reading Fou's reply has given me an idea..

    You could in theory, open a connection to server 2, grab the data you want, insert it into a memory table on the 1st databse server and then join that to your existing table. As soon as the script ends the memory table is gone too but it's there long enough for you to join everything together to get it how you want it.

    Complex, probably a bit more work but it's doable that way.
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • Users who have thanked tangoforce for this post:

    rgEffects (11-02-2013)

  • #7
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    tangoforce, I changed the setup from the previous thread so I was not really asking the same question. The post was originally made on only 2 hours of sleep in the last 24 so I'm sorry I left out critical informatiion.

    The two databases are now on the same server. I moved them both to a common server after answers to the previous thread told me it was impossible. Sorry I forgot to mention that. For legal reasons I can't put any member information in the Page database except the member id.

    I've seen multiple posts about accessing two databases on the same server but I can't get any of them to work.

    I like the temporary table idea which is kind of what I was going for on my previous post except I was trying to generate an array from the query that I could join. I have 2 connections for two data bases and I can call up any member information and any page information at the same time, I just can't seem to get them to match up.

    Originally I was just trying to write a loop that matched the member info to the member id on the page info but you can't put a loop inside another loop so when I got to the first page with a memberID the loop stopped and only would slow one member with their page.

    Got other fish to fry this afternoon. I'll get back to this problem on Sunday.
    Last edited by rgEffects; 11-02-2013 at 11:04 PM.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,958
    Thanks
    2
    Thanked 304 Times in 296 Posts
    Now that they are on the same database server, just make one database connection (must have permission to access both databases) and list the - database_name.table_name instead of just the table_name in the query.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

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