Laravel – Working with Database

0
219

In our previous blogs we have given a brief about Laravel Installation. Today we are going to discuss about how to work with DB in Laravel Framework with xampp server. So lets start…

Working-with-Laravel

Connecting to Database

Laravel has made processing with 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 simple student management system.

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

Database − College

Table − student

Column NameColumn DatatypeExtra
Idint(11)Primary key | Auto increment
Namevarchar(25)

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

Insert Records :

We can insert the record using the DB facade with insert method.

The syntax of insert method is as shown in the following table.

Syntaxbool insert(string $query, array $bindings = array())
Parameters
  • $query(string) – query to execute in database
  • $bindings(array) – values to bind with queries
Returnsbool
DescriptionRun an insert statement against the database.

Example

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

php artisan make:controller StudInsertController --plain

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

database studinsertcontroller

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

Step 6 − Visit the following URL to insert record in database.

http://localhost:8000/insert

Step 7 − The output will appear as shown in the following image.

insert_record

Retrieve Records:

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

Syntaxarray select(string $query, array $bindings = array())
Parameters
  • $query(string) – query to execute in database
  • $bindings(array) – values to bind with queries
Returnsarray
DescriptionRun 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 successful execution, you will receive the following output −

studviewcontroller

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

Step 6 − Visit the following URL to see records from database.

http://localhost:8000/view-records

Step 7 − The output will appear as shown in the following image.

id_name

Update Records:

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

Syntaxint update(string $query, array $bindings = array())
Parameters
  • $query(string) – query to execute in database
  • $bindings(array) – values to bind with queries
Returnsint
DescriptionRun an update statement against the database.

Example

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 −

update_records

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

Step 8 − The output will appear as shown in the following image.

edit_records

Step 9 − Click the edit link on any record and you will be redirected to a page where you can edit that particular record.

Step 10 − The output will appear as shown in the following image.

particular_record

Step 11 − After editing that record, you will see a prompt as shown in the following image.

record_updated

Delete Records:

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

Syntaxint delete(string $query, array $bindings = array())
Parameters
  • $query(string) – query to execute in database
  • $bindings(array) – values to bind with queries
Returnsint
DescriptionRun a delete statement against the database.

Example:

Follow Steps:

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

php artisan make:controller StudDeleteController --plain

2 − After successful execution as a result you will receive the following output −

studdeletecontroller

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.<br/>";
      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');

Step 6 −The output will appear as shown in the following image.

name_edit

Step 7 − Click on delete link to delete that record from database. You will be redirected to a page where you will see a message as shown in the following image.

record_deleted

Step 8 − Click on “Click Here” link and you will be redirected to a page where you will see all the records except the deleted one.

records

I hope it will be helpful for , Please Like and Share..