We help IT Professionals succeed at work.

How do I add timestamps to a table in Laravel?

Bruce Gust
Bruce Gust asked
on
I migrated a table from another database into a Laravel application.

First of all, it looks like all Laravel tables include a "created_at" and an "updated_at" column that need to be added in order for any kind of update or insertion to occur without an error, yes?

I'm assuming that's the case.

So, here's what I attempted to do for a migration that added those timestamps:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AlterProfessionalTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //
		Schema::table('professionals', function (Blueprint $table) {
            //
			$table->timestamps();
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
		$table->dropTimestamps();
    }
}

Open in new window



I got this error:

   Symfony\Component\Debug\Exception\FatalThrowableError  : syntax error, unexpected 'public' (T_PUBLIC), expecting ')'

  at C:\wamp64\www\new_nomas\database\migrations\2020_01_31_205053_alter_professional_table.php:27
    23|      * Reverse the migrations.
    24|      *
    25|      * @return void
    26|      */
  > 27|     public function down()
    28|     {
    29|         //
    30|                 $table->dropTimestamps();
    31|     }


Where did I blow it?
Comment
Watch Question

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Hey Bruce,

Simple typo - you've not closed off your closure:

public function up()
{
    Schema::table('professionals', function (Blueprint $table) {
        $table->timestamps();
    }); <!- you're missing this bit :)

}

Open in new window

Bruce GustPHP Developer

Author

Commented:
Chris, that did it!

But I've got one more question, if you don't mind.

Here's my corrected migration code:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AlterProfessionalTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //
		Schema::table('professionals', function (Blueprint $table) {
            //
			$table->timestamps();
		});
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
		$table->dropTimestamps();
    }
}

Open in new window


But when I run php migrate, I get this:

$ php artisan migrate
Migrating: 2020_01_02_171926_add_user_id_to_pages_table
Migrated:  2020_01_02_171926_add_user_id_to_pages_table (0.07 seconds)
Migrating: 2020_01_29_224553_create_practioners_table
Migrated:  2020_01_29_224553_create_practioners_table (0.05 seconds)
Migrating: 2020_01_29_231018_create_professional_table

   Illuminate\Database\QueryException  : SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'professionals' alread
y exists (SQL: create table `professionals` (`id` bigint unsigned not null auto_increment primary key, `cert_year` int not nul
l, `cert_num` varchar(191) not null, `lname` varchar(191) not null, `fname` varchar(191) not null, `addr1` varchar(191) not nu
ll, `addr2` varchar(191) not null, `city` varchar(191) not null, `region_state` varchar(191) not null, `country` varchar(191)
not null, `postal` varchar(191) not null, `discipline` varchar(191) not null, `phone` varchar(191) not null, `email` varchar(1
91) not null, `license` int not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4
collate 'utf8mb4_unicode_ci')

  at C:\wamp64\www\new_nomas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:665
    661|         // If an exception occurs when attempting to run a query, we'll format the error
    662|         // message to include the bindings with SQL, which will make this exception a
    663|         // lot more helpful to the developer instead of just the database's errors.
    664|         catch (Exception $e) {
  > 665|             throw new QueryException(
    666|                 $query, $this->prepareBindings($bindings), $e
    667|             );
    668|         }
    669|

  Exception trace:

  1   PDOException::("SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'professionals' already exists")
      C:\wamp64\www\new_nomas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:459

  2   PDOStatement::execute()
      C:\wamp64\www\new_nomas\vendor\laravel\framework\src\Illuminate\Database\Connection.php:459

  Please use the argument -v to see more details.

Open in new window


...which makes no sense. Obviously, the table exists, I'm just trying to add a couple of columns to the "professionals" table.

What am I doing wrong?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Hey Bruce,

In the stack trace you've shown, it looks like it's running all your migrations, so the error is being thrown by the create_professional_table migration, and not the AlterProfessionalTable migration.

Have you deleted any of the records from your migrations table in the Database. Laravel uses that table to decide which migations to run, so if any records have been deleted, then it will try and re-run your migrations. This will obviously cause problems if you left the tables in place.

Usually, if you want to step back migrations you use php artisan migrate:rollback. This will call the down() method in the migration and delete the migration from the DB.

Also, in your migration, you need to wrap the dropTimestamps into a closure in your down() method:

Schema::table('professionals', function (Blueprint $table) {
    $table->dropTimestamps();
});

Open in new window

Bruce GustPHP Developer

Author

Commented:
Chris!

Good morning!

Changed the closure problem with the "dropTimestamps."

Thank you!

Also, found this:

$ php artisan migrate --path=database/migrations/2020_01_31_205053_alter_professional_table.php

One migration at a time...

BOOM!

Thank you!