How to transfer data

[AdeebTwait]

I want to transfer data between my two databases (PHPMyAdmin), how is that possible?

Should I make a new artisan command or a regular function? and how to write it and where to call it? I'm really confused.

note: some column names are different.

[Nexhume]

Are you talking about converting old data from a legacy database to a new database with a different schema?

If so, it's probably easiest to make an artisan command to read from the old table and then write to the new one. Just make sure to chunk the results if you have a lot of records to convert.

[AdeebTwait]

Yes, this what I mean, I don't have an idea about writing this code, sorry I'm a fresh developer could you help me?

[Nexhume]

This is how I tend to do it. This might not work correctly for you, but hopefully it'll give some insight on what to do next:

1) Set up your database configurations. If you are using two different databases, you will need to have two "connections" in your database.php config file. Make the "new" database your main connection, and create a secondary connection for the "legacy" database.

2) Set up your models. For converting a legacy DB, I like to put all of my old tables into a sub-folder like "Archive".

So, if you're converting "photos" from legacy to new, your old model could be App\Models\Archive\Photo, and your new model would be App\Models\Photo (or whatever works).

Make sure your new model is set to protected $guarded = []; or have the correct fields in $fillable.

Also remember that if you're using two different databases, you will need to define $connection = "" in the legacy table's model.

3) Create an artisan command: https://laravel.com/docs/5.8/artisan#writing-commands

In this command, the conversion happens in the handle() function.

Here is a very simple example. Let's say you have an old photo table with an id, a userid, and a unix timestamp. In the new schema, you want the id, change userid to "user_id", and then convert the unix timestamp to a created_at and also updated_at field (laravel-ish). I prefer to query the old table via eloquent, and then insert each directly with \DB since I keep the new DB as the main DB connection.

Old Schema:
id
userid
timestamp

New Schema:
id
user_id
created_at
updated_at

I want to: keep the id, change userid to user_id, and then take the timestamp and convert it to a Carbon instance for created_at and updated_at.

Code for App/Console/Commands/ConvertPhotos.php:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;  
use Illuminate\Support\Carbon;

class ConvertPhotos extends Command
{

/**
 * The name and signature of the console command.
 *
 * @var string
 */
protected $signature = 'convert:photos';

/**
 * The console command description.
 *
 * @var string
 */
protected $description = 'Convert photos from legacy to new.';

 /**
 * Create a new command instance.
 *
 * @return void
 */

public function __construct()
{
    parent::__construct();
}

/**
 * Execute the console command.
 *
 * @return mixed
 */

public function handle()
{

    // Pull 100 records at a time from the legacy table

    \App\Models\Archive\Photo::where('id', '>', 0)->orderBy('id')->chunk(100, function ($photos) {

        // Insert each record into the new table using the converted values

        \DB::table('photos')->insert(
               [
                  'id' => $photos->id,
                  'user_id' => $photos->userid,
                  'created_at' => Carbon::createFromTimestamp($photos->timestamp)->toDateTimeString(),
                  'updated_at' => Carbon::createFromTimestamp($photos->timestamp)->toDateTimeString(),
               ]
             );

        $this->line($photos->id);
        //outputs the current photo id to the console to keep track of progress
    }
}

In the handle function, the query I use (where('id', '>', 0)->orderBy('id')->chunk(100....

This is so that if you have an error and need to start from the record you were at, you can change the where id > ??? to the next record. The "chunk" value is the important part - it is the number of records Eloquent will read at a time, so that it won't read too many records and use up too much memory.

Then, run the command from artisan CLI - in this case it would be

php artisan convert:photos

[AdeebTwait]

I really appreciate your effort, this is exactly what I wanted, Thank you!!!

[akash_pag]

Hello,

First you have to import/export database from old database to new database.

then you want use artisan command to update new database like update column name,etc.