Laravel Tutorial (5.7) Databases

Last update on November 14 2023 10:21:07 (UTC/GMT +8 hours)

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 -

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','StudInsertController@insertform');

Route::post('create','StudInsertController@insert');

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 -

Step 3 - Copy the following code to file

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','StudViewController@index');

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 -

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','StudUpdateController@index');

Route::get('edit/{id}','StudUpdateController@show');

Route::post('edit/{id}','StudUpdateController@edit');

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 -

Step 3 - Copy the following code to file

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','StudDeleteController@index');

Route::get('delete/{id}','StudDeleteController@destroy');

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