0

So I have hundreds of database and I want to change what database to use according to what value I pass in

For example I have many company branch that has branch_code (000,001,002,003, etc. up to 200). They all have the same tables but was just created in different databases. Also assuming that only the DB Name is different. My question is how can I access a different database when there's hundreds of them? Should I change the .env file dynamically? Should I make a function that changes the connection whenever the user chooses another branch, and if so how?

And yes, I have tried to connect two databases by configuring and adding a new database connection in config/database.php but I don't know how to do that with hundreds of them. Is there like a quicker way to do it, maybe add a for loop in the database.php? Or maybe somehow changing the .env DB_DATABASE variable.

Steven
  • 518
  • 2
  • 4
  • 12
  • Why do you have all of them in a separate database, surely you would be better off with 1 database with branch_code as a core part of any key. If you make any database changes, you will currently have to apply it to all of these databases - with the chance of missing one or two being easy and may cause all sorts of problems. – Nigel Ren Sep 28 '19 at 08:42
  • @NigelRen I wasn't actually the one who made the database, it was the company who I am currently working for. Also it would make sense since this company has many child branch so to not maybe clutter the database they separated it unto many. And I can't make changes to it, since my boss said maybe it would damage the database. – Steven Sep 28 '19 at 14:35

1 Answers1

1

Actually no there is not other way than setting it in .env becuase even if you want to make it dynamic you have to set like 100 ifs for that if you just want to see 100 settings without writing 100 lines of code you can set a loop in this file :

app/config/database.php

like below :

return array(

    'default' => 'mysql',

    'connections' => array(

        # Primary/Default database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'database1',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # You loop here
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => '127.0.0.1',
            'database'  => 'database2',
            'username'  => 'root',
            'password'  => 'secret'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

thats the easiest way to do that . so here as instructed below and then in your eloquent you should specify what database connection you want to use for that query !! :

You can set in Env file like below :

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=secret

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=database2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=secret

and then you can go into your condif/database.php and do the setting as below :

'mysql2' => [
    'driver'    => env('DB_CONNECTION_SECOND'),
    'host'      => env('DB_HOST_SECOND'),
    'port'      => env('DB_PORT_SECOND'),
    'database'  => env('DB_DATABASE_SECOND'),
    'username'  => env('DB_USERNAME_SECOND'),
    'password'  => env('DB_PASSWORD_SECOND'),
],

and then in your eloquent you can use as below :

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

Hope this can help you note : you can refer to this link for further info 

https://fideloper.com/laravel-multiple-database-connections
Farshad
  • 1,830
  • 6
  • 38
  • 70
  • That kinda sucks having to do it like that all manually instead of just having a built-in function for that, especially in the .env file. If there's no other way what can I do then. – Steven Sep 28 '19 at 14:52