PHP SQL Join Together Multiple Tables from Different Databases

php sql join together multiple tables from different databases

You will need a user that has access to all three databases.

You will JOIN them together by specifying full table name, something like this:

SELECT * FROM database_1.logs AS d1 LEFT JOIN database_2.users AS d2 
  ON d1.username = d2.username ORDER BY d1.timestamp DESC LIMIT 10

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 - Left join from two tables in different databases with different credentials using PDO

You can't join if you have to use separate PDO connections, so use nested loops and join the data in PHP.

$stmt_user = $pdo_db1Handle->query("SELECT id, username FROM t_users");
$stmt_score = $pdo_db2Handle->prepare("SELECT score FROM t_scores WHERE id_user = :userid");

$results = [];
while ($row_user = $stmt_user->fetch(PDO::FETCH_ASSOC)) {
    $scores = [];
    $stmt_score->execute(':userid' => $row_user['id']);
    while ($row_score = $stmt_score->fetch(PDO::FETCH_ASSOC)) {
        $scores[] = $row_score['score'];
    }
    $results[$row_user['username']] = $scores;
}

This will create an associative array whose keys are usernames and values are an array of their scores.

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

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.

Join two MySQL tables in different databases on the same server with Laravel Eloquent

This solution worked for me:

Model1::where('postID',$postID)
      ->join('database2.table2 as db2','Model1.id','=','db2.id')
      ->select(['Model1.*','db2.firstName','db2.lastName'])
      ->orderBy('score','desc')
      ->get();

---

Related Topics