CODEWITHSUNDEEP
phpmysqlsql-serverarraysjoin
Jovano
Jovano

Reputation: 321

Can I join two tables from different databases?

I've got two different database tables which must (inner) join each other. One table comes from a MSSQL database and one is from a MySQL database. I'm almost sure it is not possible to join these tables on the database level, so I'll have to do this in PHP (the script language I am using).

Just to make some things clear, it's about getting order information. My MSSQL database has columns such as order_id, order_description, delivery_date , etc. My MySQL database has the same order ID with a state (such as "started", "paused", "ready", "delivered"). What I want is all the information joined in one table.

I was thinking about getting all the information from the MSSQL database and then SELECTing the state for each group from the MySQL database, but that's a huge performance issue: if I have 1000 rows I'll have to do 1000 SELECTs.

Something what would really be perfect is to do a simple SELECT on both databases, put the results in two different arrays and merge the arrays. I know there is a PHP function called array_merge_recursive(), but that just sticks the two arrays together instead of joining them on the primary key (order_id).

What is the best way to get both of the database tables in one overview?

Upvotes: 1

Views: 3075

Answers (2)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

If you want to use the keys from two arrays, which are equal, and only replace the values, you simply need array_merge().

If the input arrays have the same string keys, then the later value for that key will overwrite the previous one. If, however, the arrays contain numeric keys, the later value will not overwrite the original value, but will be appended.

Values in the input array with numeric keys will be renumbered with incrementing keys starting from zero in the result array.

However, taking the information from both databases and writing it into an array is still the same as taking the info with SELECT and using the fetched rows as matches in another SELECT.

Upvotes: 1

Michael Harmon
Michael Harmon

Reputation: 746

I would look at creating a linked server to the MySQL Server from SQL Server. Then, you can code a join between the two tables as though they were on the same server. If you create a view, you can do a select on the view from your application. I would think it would be faster than doing the "join" in your application. It should certainly be easier to code.

To code the view, you would use a four-part naming convention to refer to the MySQL server:

Example:

select *
from mysqlsrv.mydb1..mytbl join
mssql.mydb2.dbo.myOtherTbl

Upvotes: 1

Related Questions