Laravel Multi Database Connection Join table

Question

Datebase 1 = db1 Database 2 = db2

I have two databases connection, I wanted to run a eloquent or DB which joining connection1 and connection 2 table(db1.users and db2.users), and check connection1 id = connection 2 or not.

can someone guide me how to do that?

Appreciate that if someone could guide me what to do.

db1 user id name ori_id

db2 ori_user id name

select db2.ori_user.name from db1.user join db1.user on db1.user.ori_id = db2.ori_user.id

yes, i can see. Is that any where can join two table from different database? — php_dvp
– php_dvp, Commented Nov 28, 2016 at 4:13

The Alpha · Accepted Answer · 2016-11-28 04:43:42Z

4

Yes, it's possible as long as they are on the same server, for example:

$result = \DB::table('db1.users')
    ->join('db2.users', 'db2.users.id', '=', 'db1.users.id')
    ->select('db1.users.id as db1_id', 'db1.users.email as db1_email', 'db2.users.*')
    ->get();

Make sure the user has access/privilege to use both databases.

edited Nov 28, 2016 at 4:43

answered Nov 28, 2016 at 4:38

The Alpha

147k30 gold badges294 silver badges313 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rav Over a year ago

OP is asking for two separate databases.

The Alpha Over a year ago

Yes, I'm concerned about that and answered that exactly. Have you even checked my answer or you didn't understand?

Borna · Accepted Answer · 2016-11-28 11:32:19Z

0

in database.php

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'customers'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'customers2'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],

here customers is database1 and customers2 is database2

in controller

 $data=DB::select('select customers2.ori_user.name from customers.user join customers2.ori_user on customers.user.ori_id = customers2.ori_user.id');

or

$data=DB::select('your_query');

Youtube link:https://www.youtube.com/watch?v=Kgl3FzqP1Ps&feature=youtu.be

edited Nov 28, 2016 at 11:32

answered Nov 28, 2016 at 4:08

Borna

5485 silver badges20 bronze badges

8 Comments

php_dvp Over a year ago

this is just join two same database tables, is that possible to make it join from different database table.

Borna Over a year ago

oh sorry my mistake i thought 2 table in same database..ok i will try if i can i will provide u

php_dvp Over a year ago

thanks for your fast response, i have tried this, is not working. do you have others way?

Borna Over a year ago

what is the error you r getting?? can kindly share??

Borna Over a year ago

you may check out this video youtube.com/watch?v=Kgl3FzqP1Ps&feature=youtu.be

|

Collectives™ on Stack Overflow

Laravel Multi Database Connection Join table

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related