6

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

php_dvp
  • 133
  • 1
  • 4
  • 14

2 Answers2

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.

The Alpha
  • 143,660
  • 29
  • 287
  • 307
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

Borna
  • 538
  • 4
  • 19
  • this is just join two same database tables, is that possible to make it join from different database table. – php_dvp Nov 28 '16 at 04:11
  • oh sorry my mistake i thought 2 table in same database..ok i will try if i can i will provide u – Borna Nov 28 '16 at 04:16
  • thanks for your fast response, i have tried this, is not working. do you have others way? – php_dvp Nov 28 '16 at 06:06
  • what is the error you r getting?? can kindly share?? – Borna Nov 28 '16 at 08:48
  • you may check out this video https://www.youtube.com/watch?v=Kgl3FzqP1Ps&feature=youtu.be – Borna Nov 28 '16 at 11:32
  • Hi Borna, thanks for your help, but i still getting error. my first sql is my localhost sql, and another one is from other server. Both is working on printing individually, but they way u show me is not working for me, do i need to do other setting? – php_dvp Nov 29 '16 at 02:59
  • no there is no other setting... what is your another server?? – Borna Nov 29 '16 at 03:44
  • can you take a screen shot of your error and post here?? – Borna Nov 29 '16 at 04:02