w3resource

Laravel Tutorial (5.7) Databases

Laravel has made processing with the database very easy. Laravel currently supports following 4 databases −

  • MySQL
  • Postgres
  • SQLite
  • SQL Server

The query to the database can be fired using raw SQL, the fluent query builder, and the Eloquent ORM. To understand the all CRUD (Create, Read, Update, Delete) operations with Laravel, we will use a simple student management system.

Connecting to Database

Configure the database in config/database.php file and create the college database with the structure in MySQL as shown in the following table.

Database: College

Table: student

Column Name Column Datatype Extra
Id int(11) Primary key | Auto increment
Name varchar(25)  

We will see how to add, delete, update and retrieve records from database using Laravel in the student table.

Insert Record

We can insert the record using the DB facade with the insert method. The syntax of the insert method is as shown in the following table.

Example

Step 1 - Execute the below command to create a controller called StudInsertController

php artisan make:controller StudInsertController --plain

Step 2 - After the successful execution of step 1, you will receive the following output -

Create a Controller

Step 3 - Copy the following code to file

app/Http/Controllers/StudInsertController.php
app/Http/Controllers/StudInsertController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudInsertController extends Controller {
   public function insertform() {
      return view('stud_create');
   }
	
   public function insert(Request $request) {
      $name = $request->input('stud_name');
      DB::insert('insert into student (name) values(?)',[$name]);
      echo "Record inserted successfully.<br/>";
      echo '<a href = "/insert">Click Here</a> to go back.';
   }
}

Step 4 - Create a view file called

resources/views/stud_create.php and copy the following code in that file.
resources/views/stud_create.php

<html>
   <head>
      <title>Student Management | Add</title>
   </head>

   <body>
      <form action = "/create" method = "post">
         <input type = "hidden" name = "_token" value = "<?php echo csrf_token(); ?>">
         <table>
            <tr>
               <td>Name</td>
               <td><input type='text' name='stud_name' /></td>
            </tr>
            <tr>
               <td colspan = '2'>
                  <input type = 'submit' value = "Add student"/>
               </td>
            </tr>
         </table>
      </form>
      
   </body>
</html>

Step 5 - Add the following lines in app/Http/routes.php. app/Http/routes.php

Route::get('insert','[email protected]');
Route::post('create','[email protected]');

6 - Visit the following URL to insert record in the database.

http://localhost:8000/insert

Retrieve Record

WAfter configuring the database, we can retrieve the records using the DB facade with the select method. The syntax of the select method is as shown in the following table.

Syntax

array select(string $query, array $bindings = array())

Parameters

Name Description
$query(string) query to execute in the database
$bindings(array) values to bind with queries

Returns

array

Description

Run a select statement against the database.

Example

Step 1 - Execute the below command to create a controller called StudViewController.

php artisan make:controller StudViewController --plain

Step 2 - After the successful execution of step 1, you will receive the following output -

Create a Controller Laravel-2

Step 3 - Copy the following code to file

app/Http/Controllers/StudViewController.php

app/Http/Controllers/StudViewController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudViewController extends Controller {
   public function index() {
      $users = DB::select('select * from student');
      return view('stud_view',['users'=>$users]);
   }
}

Step 4 - Create a view file called

resources/views/stud_view.blade.php and copy the following code in that file.

resources/views/ stud_view.blade.php

<html>
   
   <head>
      <title>View Student Records</title>
   </head>
   
   <body>
      <table border = 1>
         <tr>
            <td>ID</td>
            <td>Name</td>
         </tr>
         @foreach ($users as $user)
         <tr>
            <td>{{ $user->id }}</td>
            <td>{{ $user->name }}</td>
         </tr>
         @endforeach
      </table>
   </body>
</html>

Step 5 - Add the following lines in app/Http/routes.php. app/Http/routes.php

Route::get('view-records','[email protected]');

Step 6 - Visit the following URL to see records from the database. http://localhost:8000/view-records

Update Record

We can update the records using the DB facade with update method. The syntax of update method is as shown in the following table.

Syntax

int update(string $query, array $bindings = array())

Parameters

Name Description
$query(string) query to execute in the database
$bindings(array) values to bind with queries

Returns

int

Description

Run an update statement against the database.

Example

Observe the following example to understand more about updating records -

Step 1 - Execute the below command to create a controller called StudViewController.

php artisan make:controller StudUpdateController --plain

Step 2 - After successful execution, you will receive the following output -

Create a Controller Laravel-3

Step 3 - Copy the following code to file app/Http/Controllers/ StudUpdateController.php

app/Http/Controllers/StudUpdateController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudUpdateController extends Controller {
   public function index() {
      $users = DB::select('select * from student');
      return view('stud_edit_view',['users'=>$users]);
   }
   public function show($id) {
      $users = DB::select('select * from student where id = ?',[$id]);
      return view('stud_update',['users'=>$users]);
   }
   public function edit(Request $request,$id) {
      $name = $request->input('stud_name');
      DB::update('update student set name = ? where id = ?',[$name,$id]);
      echo "Record updated successfully.<br/>";
      echo '<a href = "/edit-records">Click Here</a> to go back.';
   }
}

Step 4 - Create a view file called

resources/views/stud_edit_view.blade.php and copy the following code in that file.

resources/views/stud_edit_view.blade.php

<html>
   <head>
      <title>View Student Records</title>
   </head>
   
   <body>
      
      <table border = "1">
         <tr>
            <td>ID</td>
            <td>Name</td>
            <td>Edit</td>
         </tr>
         @foreach ($users as $user)
         <tr>
            <td>{{ $user->id }}</td>
            <td>{{ $user->name }}</td>
            <td><a href = 'edit/{{ $user->id }}'>Edit</a></td>
         </tr>
         @endforeach
      </table>
   </body>
</html>

Step 5 - Create another view file called resources/views/stud_update.php and copy the following code in that file.

resources/views/stud_update.php

<html>
   
   <head>
      <title>Student Management | Edit</title>
   </head>
   
   <body>
      <form action = "/edit/<?php echo $users[0]->id; ?>" method = "post">
         <input type = "hidden" name = "_token" value = "<?php echo csrf_token(); ?>">
      
         <table>
            <tr>
               <td>Name</td>
               <td>
                  <input type = 'text' name = 'stud_name' 
                     value = '<?php echo$users[0]->name; ?>'/>
               </td>
            </tr>
            <tr>
               <td colspan = '2'>
                  <input type = 'submit' value = "Update student" />
               </td>
            </tr>
         </table>
      </form>
   </body>
</html>

Step 6 - Add the following lines in app/Http/routes.php. app/Http/routes.php.

Route::get('edit-records','[email protected]');

Route::get('edit/{id}','[email protected]');

Route::post('edit/{id}','[email protected]');

Step 7 - Visit the following URL to update records in database.

http://localhost:8000/edit-records

Delete Record

We can delete the record using the DB facade with the delete method. The syntax of delete method is shown in the following table.

Syntax

int delete(string $query, array $bindings = array())

Parameters

Name Description
$query(string) query to execute in the database
$bindings(array) values to bind with queries

Returns

int

Description

Run a delete statement against the database.

Example

Step 1 - Execute the below command to create a controller called StudDeleteController.

php artisan make:controller StudDeleteController --plain

Step 2 - After successful execution, you will receive the following output -

Create a Controller Laravel-4

Step 3 - Copy the following code to file

app/Http/Controllers/StudDeleteController.php

app/Http/Controllers/StudDeleteController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudDeleteController extends Controller {
   public function index() {
      $users = DB::select('select * from student');
      return view('stud_delete_view',['users'=>$users]);
   }
   public function destroy($id) {
      DB::delete('delete from student where id = ?',[$id]);
      echo "Record deleted successfully.
"; echo '<a href = "/delete-records">Click Here</a> to go back.'; } }

Step 4 - Create a view file called resources/views/stud_delete_view.blade.php and copy the following code in that file.

resources/views/stud_delete_view.blade.php

<html>
   
   <head>
      <title>View Student Records</title>
   </head>
   
   <body>
      <table border = "1">
         <tr>
            <td>ID</td>
            <td>Name</td>
            <td>Edit</td>
         </tr>
         @foreach ($users as $user)
         <tr>
            <td>{{ $user->id }}</td>
            <td>{{ $user->name }}</td>
            <td><a href = 'delete/{{ $user->id }}'>Delete</a></td>
         </tr>
         @endforeach
      </table>
   </body>
</html>

Step 5 - Add the following lines in app/Http/routes.php. app/Http/routes.php

Route::get('delete-records','[email protected]');

Route::get('delete/{id}','[email protected]');

Previous: Laravel (5.7) Task Scheduling
Next: Laravel Tutorial (5.7) Migration



Share this Tutorial / Exercise on : Facebook and Twitter