How can I transfer content of one table to another database table using laravel

Question

I have two databases in the same server, I want to transfer the content of one table(posts) to another database table(buss_posts) using the laravel framework.

I update my question

This my post table on the first database

    Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->text('post_name');
            $table->text('post_content');
            $table->timestamp();
        });

This my buss_post table on the second database

    Schema::create('buss_posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->text('post_name');
            $table->text('post_content');
            $table->timestamp();
        });

and this my controller BlogController

class BlogController extends Controller
{
    public function run()
    {
       // how can I change between two tables at the different database
       // as you know I am connecting with one database           
    }
}

Answer 1

Assuming both tables have been created and are identical.

Two ways: 1. if both tables are connected to models. Using eloquent:

Model1::query()->orderBy('id')->chunk('1000', function($rows) {
 foreach($rows as $row) {
//create() expects array
   Model2::create($row->toArray());
 }
});

2. if they're tables without models. Using query builder:

\DB::table('table1')->orderBy('id')->chunk('1000', function($rows) {
 foreach($rows as $row) {
//convert object to array
   \DB::table('table2')->insert(json_decode(json_encode($row), true));
 }
});

Might take a while to complete depending on the number of rows. But it won't crash the system cos of the chunk. You might want to place in the code in the run method of a Seeder class.

Answer 2

I meet someone solved for me I should move to the config/database then write down these two connection:

'mysql' => [
            'driver' => 'mysql_1',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'your_first_database'),
            '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'),
            ]) : [],
        ],  

then in add seconed database :

'mysql' => [
            'driver' => 'mysql_2',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'your_second_database'),
            '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'),
            ]) : [],
        ],

and then you should create two models, in my case and put in each connection name like so :

Class BussPost extends model
{
   protected $connection = "mysql_1";
   protected $table = "buss_post";

}

another model in another database:

Class Post extends model
{
   protected $connection = "mysql_2";
   protected $table = "posts";

}

and then in your controller, you should do :

class BlogController extends Controller
{
    public function run()
    {
        $posts = Post::all();
        $buss_posts = BussPost::all();

      return [
         $posts,
         $buss_posts
      ];

    }
}

And update your .env file

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

DB_CONNECTION=mysql2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db1
DB_USERNAME=root
DB_PASSWORD=

That is all, I shard because if someone one day needs it, should use it

Answer 3

You can use laravel seed. You can generate the content of the table using a third party application [iseeder]: https://github.com/orangehill/iseed

then you can run php artisan db:seed --class=Buss_PostTableSeeder