One Select from multiple different databases

Question

Hello everyone I am trying to get data from multiple databases by one select which i would like to sort and apply conditions on

I have database connects saved in my primary database like this

$GLOBALS['connects'] = array();
$GLOBALS['prefixes'] = array();
$sql = mysql_query("SELECT * FROM shops ORDER BY ordering ASC",$connect);
while($data = mysql_fetch_assoc($sql)){
    $password = simple_decrypt($data['password']);
    $GLOBALS['connects'][$data['database']] = mysql_connect($data['host'],$data['user'],$password) or die ('bad data');
    $GLOBALS['prefixes'][$data['database']] = $data['prefix']; 
    mysql_select_db($data['database'], $GLOBALS['connects'][$data['database']]) or die ('bad select');  
}

Then i make query from global array variable

$query = 'SELECT * FROM ';
$limit = count($GLOBALS['connects']);
$i = 0;
foreach($GLOBALS['connects'] as $key => $val){
    if(($i+1)==$limit){
        $query .= $key.'.'.$GLOBALS['prefixes'][$key].'orders';
    } else {
        $query .= $key.'.'.$GLOBALS['prefixes'][$key].'orders, ';
    } 
    $i++;  
}

The result is

"SELECT * FROM shop1.pr_orders, shop2.pl_orders, shop3.ps_orders, shop4.pt_orders"

now i would like to make it work also add WHERE condition and ORDER BY

right now it should show every order but instead it returns false

HINT:

lets pretend chosen tables from databases have NOTHING in common

score 0 · Answer 1 · answered Aug 07 '14 at 08:24

0

lets say that Table1 is on Database1 and Table2 in Database2 and you want to join them in a single SELECT do this :

Select * FROM Database1.Table1 JOIN Database2.Table2 ON Table1.Id=Table2.Id

answered Aug 07 '14 at 08:24

Youness

1,468
1
9
19

they have nothing in common they are completely different they have only similar structure mostly. – user3175393 Aug 07 '14 at 08:27
i just answerd the qustion : "One Select from multiple databases" – Youness Aug 07 '14 at 08:28

score 0 · Answer 2 · edited May 23 '17 at 12:20

Your constructed SELECT statement can only work if the tables reside in different databases on the same MySQL server (the same host) and the connected user has access rights to all of them.

But as I understand, You are trying to SELECT from tables which reside in different MySQL servers. Then things goes more complicated - there are discussions about that:

Another approach is to separately connect to each server, SELECT records from the tables and then merge by hand results in PHP - but I am not sure it is that you want.

One Select from multiple different databases

2 Answers2