How to copy existing table in Laravel?

Question

I want to know how to copy existing table in Laravel? I am new to Laravel. I am trying to make an online exam application. I want to create a table for each user and copy all the contents of the exam table to this newly created table.

This is the script that I wanted to do. It works fine when not in laravel. What is the laravel way to do this?

$check = mysql_query("CREATE TABLE IF NOT EXISTS ".$testname." ( id INT, user VARCHAR(30), questn VARCHAR(30), ans VARCHAR(30))");

mysql_query ("INSERT IGNORE INTO ".$testname." SELECT * FROM test");

Adynamia

Post is as an answer if it works, it's not helpful for others in a comment, doesn't look clear. – Coruscation

Answer 1

You can duplicate a DB table in Laravel using the DB::statement method like this:

\DB::statement('CREATE TABLE new_table LIKE old_table');
\DB::statement('INSERT new_table SELECT * FROM old_table');

This will duplicate the structure, indexes and data of old_table to new_table.

Blanche

Is there a faster way, to just copy table and not by doing inserts? – Hydroquinone

Answer 2

Here is the solution (Someone helped me to solve this):

$db = DB::connection(); 

$sql = "CREATE TABLE IF NOT EXISTS ".$testname." ( id INT, user VARCHAR(30), questn VARCHAR(30), ans VARCHAR(30))"; 
$db->statement($sql); 

$sql = "INSERT IGNORE INTO ".$testname." SELECT * FROM test"; 
$db->statement($sql);

Adynamia

You can except your answer :-) – Coruscation

Answer 3

$user = DB::connection('mysql')->select('select * from usersdata');

foreach ($user as $record) {
    $user = DB::connection('mysql1')->table("tablename")->insert(get_object_vars($record));

Porker

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. – Confederacy

Answer 4

You can duplicate the table structure and data by the following

use Illuminate\Support\Facades\DB;

if (!Schema::hasTable('new_table_name')) { // make sure table does not exists already
    // creates table structure
    DB::statement('CREATE TABLE new_table_name LIKE old_table_name');
    //inserts table data
    DB::statement('INSERT new_table_name SELECT * FROM old_table_name');
    // Add addtional column if you watch
    if (!Schema::hasColumn('old_table_name', 'deleted_at')) {
        Schema::table('new_table_name ', function (Blueprint $table) {
            $table->softDeletes();
        });
    }
}

Chaumont