Mysqli join tables from 2 different databases
This question has nothing to do with mysqli (or any other API).
To do a join with a table from different database, a user which connects to mysql, have to have SELECT rights for both databases.
Having this done, just select one of databases in your connection statement and address another using usual dot syntax:
SELECT * FROM t1 JOIN db2.t2
To answer your question literally,
Can I use this 2 connections to run a query which joins 2 tables?
You can't
Join between tables in two different databases?
Yes, assuming the account has appropriate permissions you can use:
SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;
You just need to prefix the table reference with the name of the database it resides in.
How to join two tables from two different databases using mysqli query?
In my experience the easiest this can be achieved is that the database user has privileges on both database schemas. So this way you could achieve what you require by using the database schema name in the queries. The only thing is that you have to be very careful not to omit it. So, with apostrophes and everything the query could end up looking something like:
SELECT * FROM `database1`.`table1`
JOIN `database2`.`table2`
ON `database1`.`table1`.`column1` = `database2`.`table2`.`column2`
The most important part is the user is granted privileges on both schemas.
Also, as Jose pointed below, the databases must be on the same database server.
How to join two tables from different databases
You cannot as long as by join
you mean using SQL JOINS
. It is, because SQL query is always sent to specific database or server.
You need to send two independent queries, one for each database and then provide some logic in PHP to join them either by array_merge
(full join) or some more advanced scripting.
Can't help more without seeing some code of your own.
PHP Mysql joins across databases
EDIT: So what becomes of these lines?:
$conn =
mysql_connect('localhost','username','password');
@mysql_select_db('database_name',$conn)
The mysql_select_db is optional. As it just selects the active db to use.
http://us3.php.net/manual/en/function.mysql-select-db.php
And yes.. this is an old app, hence
the mysql instead of mysqli. Suppose I
could convert it.
IMHO I don't think using MYSQL has anything to do with being slow, from my own research into it, there is not much speed improvement with MYSQLi vs MYSQL. Real world tests don't show that being a big performance driver. I've honestly stuck with MYSQL for all my apps.
Querying data by joining two tables in two database on different servers
You'll need to use sp_addlinkedserver
to create a server link. See the reference documentation for usage. Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server. I.E:
-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
ON tab1.ID = tab2.ID
Once the link is established, you can also use OPENQUERY
to execute a SQL statement on the remote server and transfer only the data back to you. This can be a bit faster, and it will let the remote server optimize your query. If you cache the data in a temporary (or in-memory) table on DB1
in the example above, then you'll be able to query it just like joining a standard table. For example:
-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')
-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID
Check out the documentation for OPENQUERY to see some more examples. The example above is pretty contrived. I would definitely use the first method in this specific example, but the second option using OPENQUERY
can save some time and performance if you use the query to filter out some data.
Related Topics
Strip HTML Tags and Its Contents
Jquery UI Saving Sortable List
Cakephp Cake_Core_ Cache Was Unable to Write 'Cake_Dev_En-Us'
Mongodb Get _Id as String in Find Query
Maximum Length of Generated Hash When Using Password_Hash
PHP Function to Replace a (I)Th-Position Character
Warning Problem: Expects Parameter 1 to Be MySQLi_Result
Read and Write to a File While Keeping Lock
How to Insert Multiple Rows in PHP Pdo MySQL
How to Stop Curl from Using 100 Continue
How to Count the Consecutive Duplicate Values in an Array
Undefined Offset When Using PHP Explode()
Parse Error: Syntax Error, Unexpected '.', Expecting ',' or ';'
PHP for ; Foreach Variable Scope