CODEWITHSUNDEEP
mysqllaravelconnection
Nayana
Nayana

Reputation: 147

Copy values from one database to another using laravel

I'm using two different databases, one called "example" and another "example_hist". I'm trying to create a cron job that verifies the database "example" in table "users" if there is any user that has a state "disabled". If there is, it should copy the user row to the table "users" inside the database "example_hist"

My problem is that I can't figure out how I can do this.

I already did the connection:

 '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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'mysql2' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST_2', '127.0.0.1'),
            'port' => env('DB_PORT_2', '3306'),
            'database' => env('DB_DATABASE_2', 'forge'),
            'username' => env('DB_USERNAME_2', 'forge'),
            'password' => env('DB_PASSWORD_2', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

in my .env I have:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=example
DB_USERNAME=root
DB_PASSWORD=pass

DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=example_hist
DB_USERNAME_2=root
DB_PASSWORD_2=pass

I already inserted the command in the kernel and this is the code I have now:

 public function handle()
    {
        $users = DB::table('users')->where('state', 'Disabled')->get();
 

        $user_hist = DB::connection('mysql2')->select('users');
      //should be something here that copies the row from $users 
    }

Upvotes: 0

Views: 2201

Answers (2)

Teekay
Teekay

Reputation: 114

Create one more field as copied which will be default false, and it will be true once its copied to other database

Run query whose state is disabled and not copied

 $users = DB::table('users')->where('state', 'Disabled')
        ->where("copied", "=", 0)->get();

Run users loop

foreach ($users as $user){

            //Now using connection insert into another table of another db
            DB::connection('mysql2')->insert('insert into users (name, email, mobile, password, created_at, state) values (?,?,?,?,?,?)',  array($user->name , $user->email , $user->mobile,   $user->password, $user->created_at,  $user->state ) );


            //Make user copied status to true so next time it wont copied again
            $user->copied = true;
            $user->save();
        }

Upvotes: 3

Nayana
Nayana

Reputation: 147

Well, with the help of @Teekay I could manage to find myself the right solution. This worked for me. Hope this can help anyone with the same problem.

 public function handle()
    {

    $users = DB::table('users')->where('state', 'Disabled')->where("copied",'false')->get();

    if($users){

    foreach ($users as $user){

     DB::connection('mysql2')->insert('insert into users (name, email, mobile, password, created_at, state) values (?,?,?,?,?,?)',  array($user->name , $user->email , $user->mobile,   $user->password, $user->created_at,  $user->state ) );

     DB::table('users')->where('state', 'Disabled')->where("copied", 'false')->update(['copied' => 'true']);

    }
}
}

Upvotes: 0

Related Questions