Laravel 8 Tutorial - Database Query Builder and CRUD Operation
In this tutorial, we will learn about the DB Query Builder and how to perform CRUD operations using it. Laravel's database query builder provides a convenient and fluent interface for creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems.
Before using the DB Query Builder, make sure that your database configurations have been configured in your application.
CRUD Operations using Query Builder
Let's see how we can perform CRUD operations in Laravel using the query builder. Go to the PostController and create a method for getAllPost. It was showing all the posts, let's check first.
You can see here that it's showing all the posts which are stored in our database. Now, let's create methods for creating a post, getting a post by ID, updating a post, and deleting a post.
Start with creating a post first. Go to the PostController and create a function.
public function addPost()
{
return view('add-post');
}
Now, add the route for this function. Go to the web.php file and create the route.
Route::get('/add-post',[PostController::class,'addPost']);
Now, let's create the add-post view. Go to the view folder and create add-post.blade.php. Write the HTML5 boilerplate and add Bootstrap 4 for a better look and feel.
Inside the body, create a form and write the following code.
<section>
<div class="container">
<div class="row">
<div class="col-md-6 offset-md-3">
<div class="card">
<div class="card-header">
Add New Post
</div>
<div class="card-body">
@if(Session::has('post_created'))
<div class="alert alert-success" role="alert"> {{Session::get('post_created')}} </div>
@endif
<form method="POST" action="{{route('post.add')}}">
@csrf
<div class="form-group">
<label for="title">Post Title</label>
<input type="text" name="title" class="form-control" placeholder="Enter post title" />
</div>
<div class="from-group">
<label for="body">Post Description</label>
<textarea class="form-control" name="body" rows="5"></textarea>
</div>
<button type="submit" class="btn btn-success">Submit</button>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
Now, go to the PostController and create another function.
public function addPostSubmit()
{
DB::table('posts')->insert([
'title' => $request->title,
'body' => $request->body
]);
return back()->with('post_created','Post has been created successfully!');
}
Alright, now create routes for this. Go to the web.php file and create a route.
Route::post('/add-post/',[PostController::class, 'addPostSubmit ')->name('post.add');
Alright, now let's create a post. Go to the browser, open URL localhost:8000/add-post, and add a post. You can see that the post is created.
Let's see all the posts. Go to /posts and you can see here all the posts along with the newly created post.
Inside the posts view, add Bootstrap for a better view. Copy the CSS and JS from add-post.blade.php file and paste here. Make the following changes in the code.
<section>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="card">
<div class="card-header">
All Posts
</div>
<div class="card-body">
<table>
<thead>
<tr>
<th>Post Title</th>
<th>Post Body</th>
</tr>
</thead>
<tbody>
@foreach($posts as $post)
<tr>
<td>{{$post->title}}</td>
<td>{{$post->body}}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</section>
Now, save the file and see the changes on the posts view. Here, you can see that it's looking better.
Get Post By ID
Now, create another method for fetching the post by their ID. Go to the PostController and create a function.
public function getPostById($id)
{
$result = DB::table('posts')->where('id',$id)->first();
return view('single-post',['post',$result]);
}
Now, create a view for a single post. Go to the views directory and create a new file single-post.blade.php. Inside this view, copy the text from another view and paste here. Make the following changes.
<section>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="card">
<div class="card-header">
Post
</div>
<div class="card-body">
<h3>{{$post->title}}</h3>
<p>{{$post->body}}</p>
</div>
</div>
</div>
</div>
</div>
</section>
Now, create the route. Go to the web.php file and type.
Route::get::('/post/{id}',[PostController::class,'getPostById')->name(post.getbyid);
Now, go to the post.blade.php view file and inside the table body, add the link.
<a href="{{route('postbyid',['id'=>$post->id])}}">View Post</a>
Now, check this. Go to the browser, open /posts, and click on view post. You can see the post.
Delete Post
Alright, let's create another method for deleting the post. Go to the PostController and create a function.
public function deletePost($id)
{
DB::table('posts')->where('id',$id)->delete();
return back()->with("post_deleted", "Post is deleted sucessfully!");
}
Go to the posts.blade.php view file and before the table, add the following code for showing the message.
@if(Session::has('post_deleted')
<div class="alert alert-success" role="alert"> {{Session::get('post_deleted')}} </div>
@endif
Now, create the route for deletePost. Go to the web.php and create the route.
Route::get('/delete-post/{id}','PostController@deletePost')->name('post.delete');
Now, implement this route inside the posts view. Go to the post.blade.php file and create a link for deleting the post.
<a href="{{route('post.delete',['id',$post->id])}}">Delete</a>
Now, let's check. Switch to the browser and go to the URL /posts. Now, you can see here the delete link in each row.
Now, you can see here that the post is deleted. Try one more time. Delete another post and see the post deleted.
Update Post
In last, let's see how we can update a post using the DB query builder. So, for that, switch to PostController and create the following functions.
public function editPost($id)
{
$post = DB::table('posts')->where('id',$id);
return view('edit-post',['post'=>$post]);
}
public updatePost($Request $request){
DB::table('posts')->where('id',$request->id)->update(['title'=>$request->title,'body'=>$body]);
return "Post has been update successfully!";
}
Now, create the route.
Route::get('edit-post/{id}','PostController@editPost')->name('post.edit');
Route::get('/update-post',[PostController::class,'updatePost'])->name(post.update);
Now, create a view for editing the post. Go to the views folder and create a new view. Let's say the name is edit-post.blade.php.
Now, here create a form. So, just copy all the text from post.blade.php and make the following changes.
<form action="{{route('post.add')}}" method="post">
@csrf
<div class="form-group">
<label for="title">Post ID</label>
<input type="text" name="id" class="form-control" value="{{$post->id}}" >
</div>
<div class="form-group">
<label for="title">Post Title</label>
<input type="text" name="title" class="form-control" value="{{$post->title}}" placeholder="Enter Post Title">
</div>
<div class="form-group">
<label for="body">Enter Post Body </label>
<textarea class="form-control" name="body" rows="3">{{$post->body}}</textarea>
</div>
<input type="submit" value="Update" />
</form>
Now, go to the posts.blade.php and make a link here for editing the post. Inside the table body, let's write the following code.
<a herf="{{route('post.edit',['id'=>$post->id])}}">Edit Post</a>
Now, just check it. Switch to the browser and go to URL /posts. Now, click on edit.
Now, make some changes here and click on the update button. You can see here that the post is updated.
Let's see all the posts. Here is the updated post.
So, in this way, you can perform CRUD operations using the DB Query Builder.
