Having trouble with using multiple database in laravel 5.7?

Question

I'm having a problem with Laravel 5.7, it don't recognize a second database connection. Database 2 has a 'countries' table that Database 1 doesn't. But laravel insist with connect the Database 1. This is the error and actual code, please help! Thanks in advance

ERROR

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database_1.countries' doesn't exist (SQL: select * from countries)

MODEL

class Country extends Model {

    use SoftDeletes;

    protected $connection = 'mysql_2';
    protected $table      = 'countries';
    protected $fillable = ['...'];

}

CONTROLLER / DEBUGGINB

class CountriesController extends Controller {

    public function index(){

       //  neither works, this

       $countries = DB::connection("mysql_2")->select('select * from countries');

       // or this

       $countries = Country::all();

       dd($countries); --> both give ERROR
}

DATABASE.PHP

    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [

        'mysql' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE', 'database_1'), //forge
            'username'      => env('DB_USERNAME', 'root'), // forge
            'password'      => env('DB_PASSWORD', ''),
             .......
        ],

        'mysql_2' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE', 'database_2'),
            'username'      => env('DB_USERNAME', 'root'),
            'password'      => env('DB_PASSWORD', ''),
            .......
       ],

.ENV file

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION=mysql_2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_2
DB_USERNAME=root
DB_PASSWORD=

and CACHE is updated:

php artisan config:cache

  Configuration cache cleared!
  Configuration cached successfully!

You can't have multiple values in the .env file for the same variable name. You need a DB_CONNECTION_2, DB_HOST_2, etc. to have different values. — ceejayoz
– ceejayoz, Commented Oct 6, 2019 at 14:37
thanks Matti, your comments its perfect in the same line that Steven, thanks a lot! — techienomics
– techienomics, Commented Oct 6, 2019 at 14:59
Possible duplicate of How to use multiple databases in Laravel — Rashed Hasan
– Rashed Hasan, Commented Oct 6, 2019 at 15:52

Steven · Accepted Answer · 2019-10-06 14:33:51Z

2

In your database.php file try to change

'mysql_2' => [
            'driver'        => 'mysql',
            'host'          => env('DB_HOST', '127.0.0.1'),
            'port'          => env('DB_PORT', '3306'),
            'database'      => env('DB_DATABASE2', 'database_2'), // THIS IS THE ONE THATS CHANGED
            'username'      => env('DB_USERNAME', 'root'),
            'password'      => env('DB_PASSWORD', ''),
            .......
       ],

.ENV FILE

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION=mysql_2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE2=database_2 // We change 'DB_Database' to 'DB_Database2'
DB_USERNAME=root
DB_PASSWORD=

answered Oct 6, 2019 at 14:33

Steven

5382 gold badges4 silver badges12 bronze badges

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

3 Comments

techienomics Over a year ago

thanks Steven, works greats! You know why this first code works but the second don't? // this work $countries = DB::connection('mysql_test_2')->select('select * from countries'); // this not $countries = Country::select('*')->get(); dd($countries); Thanks a lot!

Steven Over a year ago

Glad to help! Don't forget to mark as best answer, :)

Steven Over a year ago

Did you import Country model? Tried your second example and it just works in my app. Also if you're going to get all the columns on that table, rather than typing select(*) you can just directly type get() that is if you're going for the eloquent method. Use select() if you're selecting specific columns. e.g ($countries = Country::select('id','countryName')->get();)

Rashed Hasan · Accepted Answer · 2019-10-06 14:57:32Z

Another process you can try -

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database_1
DB_USERNAME=root
DB_PASSWORD=

CUSTOM_DATABASE=database_2
CUSTOM_USERNAME=root
CUSTOM_PASSWORD=

database.php

'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        ...................................
    ],
'custom' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('CUSTOM_DATABASE', 'forge'),
        'username' => env('CUSTOM_USERNAME', 'forge'),
        'password' => env('CUSTOM_PASSWORD', ''),
        ..........................................
    ],

YourModel.php

protected $connection = 'custom';
protected $fillable = [......];

If you use protected $connection in your model then you can use eloquent as usual. If so, then you can run your query like this -

$countries = Country::all();

And in your migration file -

public function up()
{
    Schema::connection('custom')->create('your-table-name', function (Blueprint $table){
        $table->bigIncrements('id');
        ...........................
        $table->timestamps();
    });
}

Collectives™ on Stack Overflow

Having trouble with using multiple database in laravel 5.7?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related