3

I am using two database connections in my Laravel project.

I need to modify tables in both databases.

I have set up everything and now I am playing around and trying to get data from the first and the second database, but the docs hardly give information on how to do so, I only found this:

$users = DB::connection('foo')->select(...);

Are there more resources somewhere? I was not able to find anything.

So far I understand that I cant use eloquent anymore if I use multiple databases? At least I cant use eloquent for the second database, which is not default.

For testing, I have created the same tables on both databases with different data. But I cant query the data, I always get this error:

Undefined property: Illuminate\Database\MySqlConnection::$User

Here are my tests:

 $users1 = DB::connection('mysql_live')->select('SELECT * FROM users');
 info($users1);

 $users2 = DB::connection('mysql')->User::all();
 info($users2);

I also tried: 

$users1 = DB::connection('mysql_live')->User::all();

Best would be documentation on how to correctly use DB::connection and actually select, edit, insert data, etc.

EDIT

  • How do I actually f.e. select all users from both databases?
  • For the second database, I will never need to create new columns or tables, I will only need to update data, probably eloquent is not needed there, which means I always need to execute raw SQL, correct? If I use eloquent I need to create the models as well?
Sayed Mohd Ali
  • 2,156
  • 3
  • 12
  • 28
Roman
  • 3,563
  • 5
  • 48
  • 104

3 Answers3

9
$users1 = DB::connection('mysql_live')->table('users')->get()->toArray();

For the second query you can do the same. There is no reason to use eloquent model for that. But if you want to then:

Inside your User model you must define your connection

 protected $connection = 'mysql'; //which is the default as well

And also you must define the fields that you want to be retrieved like:

protected $fillable = ['email','username'];

Really important is to hide sensitive fields like password etc etc. There you can use another array:

protected $hidden = ['password'];

Then you can use the eloquent like:

$users = User::get();
pr1nc3
  • 8,108
  • 3
  • 23
  • 36
  • Thanks a lot, very helpful. I just figured out aswell that I dont need to change anything for the default DB. – Roman Jul 16 '19 at 09:18
  • Glad i helped :) – pr1nc3 Jul 16 '19 at 09:18
  • My problem is, how do I know this syntax: `->table('users')->get()->toArray();`. The laravel docu is really great, but the part on multiple databases is poor, there is only this one line: `$users = DB::connection('foo')->select(...);`. But I can figure that out step by step. – Roman Jul 16 '19 at 09:20
  • So basically this one casts your output of the users table into an array so you can use it. You can store it into a variable and access it as an array. This is a collection you can read more about it here: https://laravel.com/docs/5.8/collections#method-toarray – pr1nc3 Jul 16 '19 at 09:22
2

This is possible with the setConnection method

$someModel = new SomeModel;
$someModel->setConnection('foo');
$something = $someModel->find(1);
return $something;
AgeDeO
  • 3,137
  • 2
  • 25
  • 57
0

Try this code,

first you need to set the config 

Config::set('database.connections.mysql_live.database', 'dbname');
Config::set('database.connections.mysql_live.host','localhost');

then 

 $result=DB::connection('mysql_live')->table('users')->get();
JIJOMON K.A
  • 1,290
  • 3
  • 12
  • 29