Working With Tables in Laravel 5.2

Pre-Requisites

Before we can get started building our blog, we'll first need to set up a database on our machine that we can play around with for testing purposes.

I typically use XAMPP on my local machines as it allows me to create MySQL tables using phpMyAdmin. I'd recommend, if you haven't already, downloading this and setting it up.

Once you've set up phpMyAdmin create a table with which we can build our blog. Create a user and give that user permission and then add this to your .env file within the root directory of your project.

DB_HOST=127.0.0.1
DB_DATABASE=blog_db
DB_USERNAME=your_user
DB_PASSWORD=your_password

Test this connection out by performing php artisan migrate and if everything is configured correctly you should now see a users table within that database.

Creating our Database Tables:

Over these tutorials we will be implementing the following components:

  • A Blog System with Tags
  • A commenting system so that users can respond to our posts.
  • User authentication using Facebook, Twitter and Google Plus
  • An Admin Section where we can perform all CRUD on our posts.
  • An API that we can consume and utilize for future components.

For each of these things we’ll want to define some form of schema that can be used to store our data in our database. I’m purposefully leaving a few fields out of the initial design as it’ll let me demonstrate the power of migrations further down the line.

Posts

Name Type Traits
id integer unique, increments
Title string n/a
description text n/a
Author String N/A
Body Text N/A
created_at timestamp N/A
updated_at timestamp N/A
published_at timestamp N/A

Comments

Name Type Traits
id integer unique, increments
body text n/a
author string n/a
created_at timestamp N/A
updated_at timestamp N/A
published_at timestamp N/A

Tags

Name Type Traits
id integer unique, increments
name string n/a
created_at timestamp N/A
updated_at timestamp N/A
## Post_Tag
Name Type Traits
post_id integer n/a
tag_id integer n/a
created_at timestamp N/A
updated_at timestamp N/A

Creating our Migrations

php artisan again comes in useful when creating migrations. For each of the tables we’ve defined above, we are going to want to create a migration that will create a table in the database.

Navigate to the root directory of the project and type the following commands in:

php artisan make:migration create_posts_table
php artisan make:migration create_comments_table
php artisan make:migration create_tags_table
php artisan make:migration create_post_tag_table

This should create 4 new php classes under database/migrations and it’s in these 4 new files that we are going to define our tables schema.

# Defining our Schemas

In each of these 4 new files we need to define the table structure for all of our tables. You’ll notice that the users table has already been defined for us.

If you open up the CreatePostsTable class that will have been created from the first php artisan command then you should see something like the code below. The only difference is I've added the basic create and drop table functionality in the up and down methods. Migration classes like the one below contain 2 functions, an up function and a down function. The up function creates tables and the down function typically does the reverse.

In this tutorial we'll be using Laravel's Schema Builder in order to elegantly craft the tables we need in our database.

<?php

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

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table){
           // our schema is defined in here 
           $table->increments('id');
           $table->string('title');
           $table->text('description');
           $table->text('body');
           $table->string('author');
           $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        // here we define what happens when we bring down our application
        // in this case we want to drop our posts table
        Schema::drop('posts');
    }
}

For the purpose of brevity, I'm only going to be showing you the up functions for the next 3 tables we'll be creating.

public function up()
    {
        Schema::create('comments', function (Blueprint $table){
           // our schema is defined in here 
           $table->increments('id');
           $table->integer('post_id');
           $table->text('body');
           $table->string('author');
           $table->timestamps();
        });
    }
public function up()
    {
        Schema::create('tags', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }
public function up()
    {
        Schema::create('post_tag', function (Blueprint $table){
            $table->integer('post_id')->unsigned()->index();
            $table->integer('tag_id')->unsigned()->index();
            $table->timestamps(); 
        }); 
        
        // This sets up the relationships for our table
        Schema::table('post_tag', function ($table){
            $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
            $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade'); 
        });
    }

Once these are in place and you have successfully set up a database for your test site, perform the following command in the terminal:

php artisan migrate

Conclusions

If everything went smoothly with our migrations then you should have in place everything we need to start building our blog.

In the next lesson I'm going to be demonstrating how to implement some basic routes in Laravel 5.2

Link: Lesson 3 - Creating some Basic Routes