querying different databases

Question

Maybe this is totally wrong to do, but...

I need to do a query like this:

SELECT * FROM DATABASE1, DATABASE2 WHERE DATABASE1.users.name = DATABASE2.users.name

If I do it with phpmyadmin it works, but on php the page blocks.

The way I connect to mysql i'm using,

$result = new mysqli(server, user, pass, database)

I can see why it doesn't work, I'm connecting to only one database, but how does phpmyadmin do it then? How can I do it?

I've seen other posts but I want to CROSS the info on the 2 databases, and I havent found anything like that.

[EDIT] An actual example of my code would be:

if($type == "past"){// Hago una query u otro dependiendo de si quiero pasados o futuros
             $query = "SELECT DISTINCT E.* FROM quehaceshoy_testing.Events E, quehaceshoy_testing.Tickets TK, quehaceshoy_testing.TypeTickets TT, quehaceshoy_testing.Tickets_Reservados2 TR, AirTicket.TICKET T WHERE ((T.Email='$this->email' AND T.idCompra = TR.idCompra AND TR.idTypeTicket_TypeTickets = TT.idTypeTicket AND E.IDEvent = TT.idEvent_Events)OR (E.email = '$this->email')) AND  E.dateFinish < '".$fecha."' ORDER BY E.dateFinish DESC";
        }
        else{
             $query = "SELECT DISTINCT E.* FROM quehaceshoy_testing.Events E, quehaceshoy_testing.Tickets TK, quehaceshoy_testing.TypeTickets TT, quehaceshoy_testing.Tickets_Reservados2 TR, AirTicket.TICKET T WHERE ((T.Email='$this->email' AND T.idCompra = TR.idCompra AND TR.idTypeTicket_TypeTickets = TT.idTypeTicket AND E.IDEvent = TT.idEvent_Events)OR (E.email = '$this->email')) AND  E.dateFinish >= '".$fecha."' ORDER BY E.dateFinish DESC";
        }
        //echo $query;
        //$result = $this->makeQuery($query, 'RESULT');
        if($conn = db_connect()){

                    $result = $conn->query($query);
                    if(!$result){
                      //echo '<p>Unable to get list from database.</p>';
                      //echo $conn->error;
                      return false;
                    }

        }

and db_connect is just:

function db_connect()
{
   $result = new mysqli('localhost', $user, $pass, 'quehaceshoy_testing'); 
   $result->set_charset("utf8");

   if (!$result)
      return false;
   return $result;
}

This query makes the page to "load" indefinetly.

Thanks.

I think the question is clear, he wants to get values from more tables, that are in different databases. Example query works in phpmyadmin, but not in mysqli php code. — Kyborek, Apr 29 '12 at 15:29
thank you @Kyborek I think it's fairly clear. Two databases ergo also 2 different tables, but both tables, from the different databases contain info that I want to match, it's possible in phpmyadmin, so it has to be posisble using PHP but I can't figure out how to do it in my code. — subharb, Apr 29 '12 at 15:31
Possibly a duplicate of http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage and http://stackoverflow.com/questions/235264/how-to-connect-to-2-databases-at-the-same-time-in-php — verisimilitude, Apr 29 '12 at 15:35
Pl check both the above threads of discussion I posted above. You'd get your answer. You cannot query two databases with the same connection object. :) — verisimilitude, Apr 29 '12 at 15:35
Not really, since I want to cross them, as in the example. Making 2 conections would mean making a query for each of the values in table 1, from the first DB and then look for them in the second DB. I dont believe phpmyadmin does that, that's why I believe there's has to be another way. — subharb, Apr 29 '12 at 15:43
Voting to close as not a real question. The query example apparently not a real one and there is not a single hint on what "doesn't work" does actually mean. — Your Common Sense, Apr 29 '12 at 15:44
According to this http://www.dottedidesign.com/node/14 it "just works" using SELECT db1.table.column, db2.table.column FROM db1.table, db2.table WHERE etc... Maybe just select one database or the other and give it a try. — pdizz, Apr 29 '12 at 15:59
Please be precise when you say the "page blocks". Is it that a blank page gets displayed? Or your browser cranks up into an infinite loop... — verisimilitude, Apr 29 '12 at 16:22
@DavidShaikh - yes, this is possible. Can we see some actual code, pasted into your question? — halfer, Apr 29 '12 at 16:36

score 2 · Answer 1 · answered Apr 29 '12 at 16:17

I test-wrote a script below and it seems to be working fine without failing....

    <?php 
          $con = mysql_connect("localhost","root","");
           mysql_select_db("test");

          $con1 = mysql_connect("localhost","root","");
          mysql_select_db("test1");

           $query = "SELECT * FROM test1.`manager` INNER JOIN test.employee   
                    ON test1.`manager`.id= test.employee.mgr";
          $result = mysql_query($query);
          while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
           printf("ID: %s  Name: %s", $row[0], $row[1]);  
          }
      ?>

May be you'll need to post your code here. Is it that your data set is large? Please post your code so that we may check and guide.

querying different databases

1 Answers1