Cross server join in PHP sql

Question

I have two sql hosts on which I want to run my sql query. It should look something like this: Assuming two hosts -> host1.DB1 & host2.DB2

select a.*, b.* from host1.DB1.table as a join host2.DB2.table on a.key=b.key

So far, I have read many answers on this forum as well as on google but no luck any where..!!

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

AFAIK this isn't possible as you can only run the query through one connector, which can only connect to one database server at a time. You'll need to run one query then run a second query and merge the results in PHP.

The only thing that comes remotely close is if you're connecting to two databases on the same host. This answer covers that.

An example of this would be:

$rowsA = $rowsB = [];
$mysqliA = new mysqli($hostA, $usernameA, $passwordA, $databaseA);
$mysqliB = new mysqli($hostB, $usernameB, $passwordB, $databaseB);

$query = "SELECT id, A1, A2, A3 FROM tableA";
$resultA = $mysqliA->query($query);
while($row = $resultA->fetch_assoc())
{
    $rowsA[$row['id']] = $row;
}

$query = "SELECT id, B1, B2, B3 FROM tableB";
$resultB = $mysqliB->query($query);
while($row = $resultB->fetch_assoc())
{
    $rowsB[$row['id']] = $row;
}

$all_rows = array_merge_recursive($rowsA, $rowsB);

Check out this eval.in example of the array_merge_recursive working.

edited May 23 '17 at 12:21

Community

1
1

answered May 15 '15 at 15:59

Styphon

10,304
9
52
86

Thanks..!! Any suggestion on how to do it? I am new to this. As far as I know, array_merge won't help because it appends the datasets. TIA.. – Prateek Saxena May 15 '15 at 16:06
`array_merge` should work fine with the two results. – Styphon May 15 '15 at 16:12
Sorry, it's `array_merge_recursive` you want, [see this eval.in](https://eval.in/361521) as an example. – Styphon May 15 '15 at 16:16
Will try it and get back with results..!! – Prateek Saxena May 15 '15 at 16:18
@PrateekSaxena I've added an example in my answer for you. – Styphon May 15 '15 at 16:21
In the code you have shared, you are adding 'id' column to an array, will $all_rows give me id, A1, A2, A3, B1, B2, B3? – Prateek Saxena May 15 '15 at 16:24
Yes, if the same array key is present then it keeps only one of them. – Styphon May 15 '15 at 16:24
Hey, one last thing. How do I print the result of this in tabular form? I have tried foreach. I have checked that $all_rows have values using print_r. – Prateek Saxena May 16 '15 at 06:09
Styphon, Any suggestion on my precious comment? – Prateek Saxena May 17 '15 at 09:09
I suggest you try `foreach` again. It's what you need. – Styphon May 17 '15 at 15:06

score 1 · Answer 2 · edited May 23 '17 at 11:43

Depending on how many records you want to join, it make take a lot of time to do that via code.

If yor're the DBA or the DBA is willing to help you, why don't you try with a linked server??

With a linked server, you can run the query in either of the servers with PHP, so you code will look something like this:

$query = "SELECT a.id_col, b.col, a.other_col 
FROM [SERVER1].[your_db].[dbo].your_table
INNER JOIN your_other_table b
ON a.id_col = b.id_col";

That way, you can run your query from either server.

If you're using SQL Server, you can do it like this or with SSMS, like this.

I've never created a linked server in MySQL, but in this link you can find how to do it.

Hope it helps.

Cross server join in PHP sql

2 Answers2