How to make login with Dynamic Database?

Question

I am making project on laravel 5.7. where i have to manage login with dynamic database. where one master database and another is sub database. So when user give login detail that first check on master database if login not found on master then it will look email on App_clients table on master_db and find database name of that email id and check login .if it gone success then move to dashboard. and every time till logged in sub database ( client_2) database will active.

Main goal of this concept is that there two different logins.

1) Login for Master Database that will be only for Software Owner. 2) Another Login is for Client who are using This software.

So when client will login then their database name will come form App_client table using their email. So Laravel Database Config will change and set new database for their use and database will be active til client logged in else default database will active .

For Example

Database:: Master_db , Client_2_db , Client_3_db ,etc. email:abc@ex.com and password :1234 is stored in Client_2_db.

First it will check on Master_db. it will fail. then it's email will look on Master_db.App_clients and will get it database name that stored on Master_db.App_clients. So after that will try to login from Client_2_db.

So for this i am using this code on myLoginController.

   if( $request->client == '1111111'  )
    {
        Config::set('database.default', 'mysql');
        DB::reconnect('mysql');
        $loginCheck=  Auth::attempt( ["email" =>$request->username , "password" => $request->password ] );
        if( $loginCheck  )
        {
            // Store Collage ID on session variable.
            $client = SettingClient::where('client_CODE',$request->client_code)->first();
            $request->session()->put('client', $client->ID );
            $request->session()->put('cclient_code', $client->client_CODE );
            $request->session()->put('client_name', $client->client_NAME );
            $request->session()->put('database_name', 'col_master' );
            return redirect('dashboard');
        }
        goto InvalidLoginFound;

    }
    elseif (  $request-> client_code != '1111111'  )
    {
           $clientCheck =  AppClient::where("client_code" , $request->client_code )->orderBy('id','desc')->first();
        if( !$clientCheck   )
        {
            goto clientCodeNotFound ;
        }
        DB::purge('mysql');
        Config::set("database.connections.mysql", [
            "driver" => "mysql",
            "host" => env('DB_HOST'),
            "database" => $clientCheck->database_name,
            "username" => env('DB_USERNAME'),
            "password" => env('DB_PASSWORD'),
            "engine"=>"InnoDB",
        ]);
        Config::set('database.default', 'mysql');
        DB::reconnect('mysql');

        $loginCheck=  Auth::attempt( ["email" =>$request->username , "password" => $request->password ] );
        if( $loginCheck  )
        {
            // Store client ID on session variable.
            $client = SettingClient::where('client_CODE',$request->client_code)->first();
            if( !$client )
            {
                goto clientCodeNotFound;
            }
            $request->session()->put('client', $client->ID );
            $request->session()->put('login_id', $request->username  );
            $request->session()->put('password',  $request->password  );
            $request->session()->put('client_code', $client->client_CODE );
            $request->session()->put('client_name', $client->client_NAME );
            $request->session()->put('database_name', $clientCheck->database_name );
            return redirect('dashboard');
        }

        $request->session()->flush();
        Auth::logout();
        goto InvalidLoginFound;

    }

also created a middle-ware for this that manage database on each request.

 class DynamicDatabaseMiddleware
{
    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        if( session()->get('login_id')  )
        {
            DB::purge('mysql');
            Config::set("database.connections.mysql", [
                "driver" => "mysql",
                "host" => '127.0.0.1',
                "database" => session()->get('database_name')?session()->get('database_name'):'',
                "username" => 'root',
                "password" => '',
                "engine"=>"InnoDB",
            ]);
           // Config::set('database.default', 'mysql');
            DB::reconnect('mysql');


        }
        return $next($request);
    }
}

on Karnel.php

protected $middlewareGroups = [
    'web' => [
        \App\Http\Middleware\EncryptCookies::class,
        \Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse::class,
        \Illuminate\Session\Middleware\StartSession::class,
        // \Illuminate\Session\Middleware\AuthenticateSession::class,
        \Illuminate\View\Middleware\ShareErrorsFromSession::class,
        \App\Http\Middleware\VerifyCsrfToken::class,
        \Illuminate\Routing\Middleware\SubstituteBindings::class,
        \App\Http\Middleware\SessionDataMiddleware::class,
        \App\Http\Middleware\StoreExceptionMiddleware::class,


        \App\Http\Middleware\DynamicDatabaseMiddleware::class, 
    ],

after this is working but problem is that when i try to check login-in Auth::user() then i always get login detail from Master_db( col_master ) form from client_db. why? i do't know. please correct this code.

use this method for connecting multiple db's https://stackoverflow.com/questions/31847054/how-to-use-multiple-databases-in-laravel — Gaurav Gupta, Mar 27 '19 at 05:32
Gaurav ji you did not understand my requirement . please read my question... — pankaj, Mar 27 '19 at 09:42
my database name of sub database is coming from master database when client want to login every time record will come form App_client table... — pankaj, Mar 27 '19 at 09:44
i have updated my question. so please check and reply. can you share me your email id pankajwork017@gmail.com. i will share real code to you. — pankaj, Mar 27 '19 at 09:54
please help me , i have no time , gupta ji if you can join me anydex then i can demonstrate my problem.. — pankaj, Mar 27 '19 at 09:59

Moubarak Hayal · Answer 1 · 2019-03-27T11:43:23.260

0

you are on the right path. Just not there yet.

you need to add the whole login request in the middleware, and for each database connection you want to check, you put a switch or try/catch (with -> continue) or an if/elseif statement to go throw evry database connection you have and if the user is found in 1 of them then it will connect him and go to the next request if not then it will show an error. notice that in your statements (database connection switch) you need to purge your connection and connect again to a new database username/pass. you can use (

        DB::purge('mysql');

 config(['database.connection.mysql' => 
'driver' => 'mysql',
'host' => ''.
'database' =>,
etc.....
]));
        DB::reconnect('mysql');
        Schema::connection('mysql')->getConnection()->reconnect();

)

Good luck

edited Mar 27 '19 at 11:43

answered Mar 27 '19 at 10:45

Moubarak Hayal

191
1
7

sir now i am facing problem with **Auth::user()**. when i try to use **Auth::user()** on another **api** then it is not working.. and giving record from **Master_DB** **users** table. Suppose client database is Client_2 then after login Auth::user() shoud return result from Client_2 DB but returning data from Master_DB. – pankaj Mar 27 '19 at 18:59
when different api calls then it get data from master db ->users table. – pankaj Mar 27 '19 at 20:33
then you need to check the connection in your model and make it also dynamic so that it changes with the change of the database based on the login. – Moubarak Hayal Mar 28 '19 at 07:55
The model that makes he database crud actions. so lets say you have 4 databases 1 master and 3 slaves or backeup or something. but all 4 have the same tabels yet different data. you need to add the connection name in your model and you are good to go. protected $connection= 'mysql'; protected $table = 'tabel name that the model is using ex : users'; – Moubarak Hayal May 01 '19 at 08:57
can i do this like this way **$connection= Session->get('connection')** – pankaj May 03 '19 at 09:55
**please refer this question to other for give proper solution ..** – pankaj May 07 '19 at 18:10
well, i can't check for sure why it is not working, but you don't need to set the connection based on the session because you already established a connection in your middle-ware you only need to call it, like I said. – Moubarak Hayal May 08 '19 at 08:24
problem is **Auth::id()** giving user id from table that mention on **env file**. ? if all table accessing record from current database that mentioned on **config::()** – pankaj May 13 '19 at 16:35

pankaj · Answer 2 · 2019-05-03T09:53:38.347

I searched a lot and try many solutions but no any solution work my era. Now i am answer of my Question.

i just make a function that search DB_NAME in .ENV file then replace that. when my Client Login db found then it replace old db name with new on .ENV. After login it again replace default database name on logout.

   protected function updateEnv($key, $newValue, $separator='')
    {

        $path = base_path('.env');
        // get old value from current env
        $oldValue = env($key);

        // was there any change?
        if ($oldValue === $newValue) {
            return;
        }

        // rewrite file content with changed data
        if (file_exists($path)) {
            // replace current value with new value
            return  file_put_contents(
                $path, str_replace(
                    $key.'='.$separator.$oldValue.$separator,
                    $key.'='.$separator.$newValue.$separator,
                     file_get_contents($path)
                )
            );
        }
    }

// for login

  $this->updateEnv('DB_DATABASE',$appClient->db_name,'');

but still a problem . it return first time null and on again refresh it work fine.. why ? i do not know.

This solution not work on more than 1 login from different location...

This is not good if you are using api login. if you have 2 or more logins at the same time then your application will crash cause you are changing the same connection for the users. — Moubarak Hayal, Mar 28 '19 at 09:07

How to make login with Dynamic Database?

2 Answers2