How to change table prefix when a user logs in to account?

Question

I am in new laravel, In my application When a user logs into his account I want to change the default database prefix from the env file. I setup wildcard subdomain for every users. When every user logs in I want to change the database prefix according to the sub domain.

So is it possible to change the laravel prefix globally if a user logs in Laravel?. Please suggest me if there is any solution. Thanks is advance.

how does it work? You have a centralised db which obviously is used to log in as an user. You could use multiple databases defined in .env and in config/databases.php with \DB::connection to specific DB but this is not a clean solution in my opinion — Alpy, Mar 14 '19 at 09:50
@Alpy: Yes you are correct, But what I am trying to say is I need to set table prefix dynamically. every users have different set of tables, But we cannot define all those prefix in .env file. let say an example if user.app.com is the domain then after login change the default prefix to user. Do you understand? (I edited the question) — Midlaj, Mar 14 '19 at 10:04

score 0 · Answer 1 · answered Mar 14 '19 at 10:15

Of course you can:

config/database.php

'mysql' => [
        'driver' => 'mysql',
        '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', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'mysql_earth' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_EARTH_DATABASE', 'earth'),
        'username' => env('DB_EARTH_USERNAME', 'earth'),
        'password' => env('DB_EARTH_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

  'mysql_moon' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_MOON_DATABASE', 'moon'),
    'username' => env('DB_MOON_USERNAME', 'moon'),
    'password' => env('DB_MOON_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=forge
DB_USERNAME=forge
DB_PASSWORD=forgepw

DB_EARTH_DATABASE=earth
DB_EARTH_USERNAME=erth
DB_EARTH_PASSWORD=earthpw

DB_MOON_DATABASE=moon
DB_MOON_USERNAME=moon
DB_MOON_PASSWORD=moonpw

Use in your Controller example put your conditions on the connection and then you can have the query in each database:

$db = \DB::connection('mysql');
$db = \DB::connection('mysql_earth');
$db = \DB::connection('mysql_moon');

        $products = $db->table('products')
            ->distinct()
            ->select("*" )
            ->orderBy('products.id','asc')
            ->get();

Thanks for your answer. I understood but here we have only single Database. The database contain different set of table with different prefix. And when the user logs we need to change the prefix dynamically. — Midlaj, Mar 14 '19 at 10:30
then you have to query the tables with the the constant prefix which you set when the user is logging — Alpy, Mar 14 '19 at 10:36

How to change table prefix when a user logs in to account?

1 Answers1