Laravel: Query Between 2 Dates

Submitted by nurhodelta_17 on Friday, December 1, 2017 - 22:01.

Getting Started

First, we're going to create a new project and I'm gonna name it wherebetween and add it to localhost with the name wherebetween.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 post. 2. In our project, open .env file and update the ff lines depending on your setting.

DB_DATABASE=post
DB_USERNAME=root
DB_PASSWORD=

Creating our Controller

Next we're gonna create a controller to handle our Post table that we are going to create 1. In command prompt, navigate to your project and type:

php artisan make:controller PostController

This will create our controller in the form of PostController.php located in app/Http/Controllers folder. 2. Open PostController.php and edit it with the ff codes:

<?php
 
namespace App\Http\Controllers;
 
use Illuminate\Http\Request;
use App\Post;
use DB;
 
class PostController extends Controller
{
    public function index(){
    	return view('post');
    }
 
    public function getDate(Request $request){
    	$posts = DB::table('posts')
    			->whereBetween('created_at', [$request->fdate, $request->sdate])
    			->get();
 
    	return view('result', ['posts' => $posts]);
    }
}

Creating our Model

1. In command prompt, navigate to our project and type:

php artisan make:model Post -m

This will create our model Post.php located in app folder. It will also create the migration for us due to the -m that we added in creating the model in the form of create_posts_table.php file located in database/migrations folder. 2. Open this migration and edit it with ff codes:

<?php
 
use Illuminate\Support\Facades\Schema;
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) {
            $table->increments('id');
            $table->text('post');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

Making our Migration/Migrate

In command prompt, navigate to your project and type:

php artisan migrate

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);

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('/', 'PostController@index');
 
Route::post('/select', 'PostController@getDate');

Creating our Views

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

<!DOCTYPE html>
<html>
<head>
	<title>Laravel Query Between 2 Dates</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://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
@yield('content')
</body>
</html>

post.blade.php

@extends ('app')
 
@section('content')
<div class="container">
	<h1 class="page-header text-center">Laravel Query Between 2 Dates</h1>
	<div class="row">
		<div class="col-md-4 col-md-offset-4">
			<h2 class="text-center">Select 2 Dates</h2>
			<form method="POST" action="/select">
				{{ csrf_field() }}
				<div class="form-group">
					<label>First Date:</label>
					<input type="date" class="form-control" name="fdate">
				</div>
				<div class="form-group">
					<label>Second Date:</label>
					<input type="date" class="form-control" name="sdate">
				</div>
				<input type="submit" value="Get Post Between" class="btn btn-primary">
			</form>
		</div>
	</div>
</div>
@endsection

result.blade.php

@extends ('app')
 
@section('content')
<div class="container">
	<h1 class="page-header text-center">Laravel Query Between 2 Dates</h1>
	<div class="row">
 
		<div class="col-md-8 col-md-offset-2">
			<h2>Results
				<a href="/" class="btn btn-primary pull-right">Back</a>
			</h2>
			@if(count($posts)>0)
				<table class="table table-bordered table-striped">
					<thead>
						<th>Post Date</th>
						<th>Post</th>
					</thead>
					<tbody>
						@foreach($posts as $post)
							<tr>
								<td>{{ date('M d, Y h:i A', strtotime($post->created_at)) }}</td>
								<td>{{ $post->post }}</td>
							</tr>
						@endforeach
					</tbody>
				</table>
			@else
				<h3 class="text-center">No Post from Selected Range</h3>
			@endif
		</div>
	</div>
</div>
@endsection

Running our Server

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

Add new comment

357 views