Laravel: Joining Tables

Submitted by nurhodelta_17 on Friday, November 24, 2017 - 20:14.

Getting Started

Note: I'm using hosted bootstrap so you need internet connection for them to work. First, we're going to create a new project and I'm gonna name it site and add it to localhost with the name site.dev. If you have no idea on how to do this, please refer to my tutorial Installing Laravel - PHP Framework.

Setting up our Database

1. Open your phpMyAdmin and create a new database. In my case, I've created a database named join. 2. In our project, open .env file and update the ff lines depending on your setting.


DB_DATABASE=join
DB_USERNAME=root
DB_PASSWORD=

Creating our Controller

1. In command prompt, navigate to your project and type: php artisan make:controller EmployeeController This will create our controller in the form of EmployeeController.php located in app/Http/Controllers folder. 2. Open EmployeeController.php and edit it with the ff codes:

<?php
 
namespace App\Http\Controllers;
 
use Illuminate\Http\Request;
use DB;
use App\Employee;
 
class EmployeeController extends Controller
{
    public function index(){
                $employee = DB::table('employees')
                                        ->join('genders', 'genders.id', '=', 'employees.gender_id')
                                        ->join('positions', 'positions.id', '=', 'employees.position_id')
                                        ->get();
 
                $gender = DB::table('genders')
                                        ->get();
 
                $position = DB::table('positions')
                                        ->get();
 
        return view('home', ['employees' => $employee , 'genders' => $gender , 'positions' => $position]);
    }
 
    public function save(Request $request){
        $employee = new Employee;
        $employee->firstname = $request->input('firstname');
        $employee->lastname = $request->input('lastname');
        $employee->gender_id = $request->input('gender');
        $employee->position_id = $request->input('position');
        
                $employee->save();
 
                return redirect('/');
    }
}

Creating our Models

1. In command prompt, navigate to our project and type: For Employee: php artisan make:model Employee -m For Position: php artisan make:model Position -m For Gender: php artisan make:model Gender -m This will create our models Member.php, Position.php and Gender.php located in app folder. It will also create the migrations for us due to the -m that we added in creating the model located in database/migrations folder. Edit this migrations with the ff to set up our table: create_employees_table.php

<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreateEmployeesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->increments('id');
            $table->string('firstname');
            $table->string('lastname');
            $table->integer('gender_id');
            $table->integer('position_id');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('employees');
    }
}

create_genders_table.php

<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreateGendersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('genders', function (Blueprint $table) {
            $table->increments('id');
            $table->string('gender');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('genders');
    }
}

create_positions_table.php

<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreatePositionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('positions', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('positions');
    }
}

Migrating

In command prompt, navigate to your project and type: php artisan migrate It will then create our database migration. If you have an error like this: [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 767 bytes (SQL: alter table `users` add unique ` users_email_unique`(`email`)) You can solve this by opening AppServiceProvider.php located in app/Providers folder. Add this line: use Illuminate\Support\Facades\Schema; In boot add this line: Schema::defaultStringLength(191); migration error

Run php artisan migrate again and make sure that your database is empty because it will have another error if its not.

Creating our Routes

In routes folder, open web.php and edit it with the ff codes:

<?php
 
Route::get('/', 'EmployeeController@index');
 
Route::post('/save', 'EmployeeController@save');

Creating our Views

In resources/views folder, create the ff files: home.blade.php

<!DOCTYPE html>
<html>
<head>
        <meta charset="UTF-8">
        <title>Laravel: Join Table</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
        <link rel="stylesheet" href="/css/app.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
        <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
        <h1 class="page-header text-center">Laravel: Join Table</h1>
        <div class="row">
                <div class="col-md-10 col-md-offset-1">
                        <h2>Employee Table
                                <a href="#addnew" data-toggle="modal" class="btn btn-primary pull-right"><span class="glyphicon glyphicon-plus"></span> Employee</a>
                        </h2>
                        <table class="table table-bordered table-striped">
                                <thead>
                                        <th>Firsttname</th>
                                        <th>Lastname</th>
                                        <th>Gender</th>
                                        <th>Position</th>
                                </thead>
                                <tbody>
                                        @foreach($employees as $employee)
                                                <tr>
                                                        <td>{{$employee->firstname}}</td>
                                                        <td>{{$employee->lastname}}</td>
                                                        <td>{{$employee->gender}}</td>
                                                        <td>{{$employee->title}}</td>
                                                </tr>
                                        @endforeach
                                </tbody>
                        </table>
                </div>
        </div>
</div>
@include('modal')
</body>
</html>

modal.blade.php

<!-- Add Modal -->
<div class="modal fade" id="addnew" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
        <div class="modal-dialog" role="document">
                <div class="modal-content">
                        <div class="modal-header">
                                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                                <h4 class="modal-title text-center" id="myModalLabel">Add New Employee</h4>
                        </div>
                        <div class="modal-body">
                                <form action="{{ URL::to('save') }}" method="POST">
                                        {{ csrf_field() }}
                                <div class="form-group">
                                                <div class="row">
                                                <div class="col-md-2" style="margin-top:7px;">
                                                        <label for="firstname">Firstname</label>
                                                </div>
                                                <div class="col-md-10">
                                                        <input type="text" name="firstname" class="form-control" placeholder="Input Firstname" required>
                                                </div>
                                        </div>
                                </div>
                                <div class="form-group">
                                        <div class="row">
                                                <div class="col-md-2" style="margin-top:7px;">
                                                        <label for="lastname">Lastname</label>
                                                </div>
                                                <div class="col-md-10">
                                                        <input type="text" name="lastname" class="form-control" placeholder="Input Lastname" required>
                                                </div>
                                        </div>
                                </div>
                                <div class="form-group">
                                        <div class="row">
                                                <div class="col-md-2" style="margin-top:7px;">
                                                        <label for="lastname">Gender</label>
                                                </div>
                                                <div class="col-md-10">
                                                        <select class="form-control" name="gender">
                                                                @foreach($genders as $gender)
                                                                        <option value="{{ $gender->id }}">{{ $gender->gender }}</option>
                                                                @endforeach
                                                        </select>
                                                </div>
                                        </div>
                                </div>
                                <div class="form-group">
                                        <div class="row">
                                                <div class="col-md-2" style="margin-top:7px;">
                                                        <label for="lastname">Position</label>
                                                </div>
                                                <div class="col-md-10">
                                                        <select class="form-control" name="position">
                                                                @foreach($positions as $position)
                                                                        <option value="{{ $position->id }}">{{ $position->title }}</option>
                                                                @endforeach
                                                        </select>
                                                </div>
                                        </div>
                                </div>
                        </div>
                        <div class="modal-footer">
                                <button type="button" class="btn btn-default" data-dismiss="modal"><i class="fa fa-times"></i> Cancel</button>
                                <button type="submit" class="btn btn-primary"><i class="fa fa-save"></i> Save</button>
                                </form>
                        </div>
                </div>
        </div>
</div>

Running our Server

In your web browser, type the name that you added in localhost for your project in my case, site.dev. That ends this tutorial. Happy Coding :)

Add new comment

571 views