Insert Update and Delete record from MySQL in Laravel 7

Last updated on by

If you are using MySQL database in your web project then there is always possibility that you need to insert, update, and delete records instead of just fetching and displaying records.

In this tutorial, I show how you can insert, update, and delete a record from MySQL database table in Laravel.

Insert Update and Delete record from MySQL in Laravel 7

Contents

  1. Table structure
  2. Database Configuration
  3. Model
  4. Controller
  5. Route
  6. View
  7. Conclusion

1. Table structure

I am using users table in the example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

3. Model

Create Model –

  • Execute php artisan make:model Page to create Page Model.
  • Open app/Page.php file.

Here, create 4 methods –

  • getuserData() – Fetch all records from users table and return it. If $id is not 0 then select record by id.
  • insertData() – From this function insert a new record. Check username exists or not in users table. If no record is fetched then execute DB::table('users')->insert($data); and return 1 otherwise 0.
  • updateData() – From this function update record. Execute DB::table('users')->where('id',$id)->update($data);.
  • deleteData() – From this function delete record. Delete record from users table according to $id.

Completed Code

<?php

namespace App;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;

class Page extends Model {
  public static function getuserData($id=0){

    if($id==0){
      $value=DB::table('users')->orderBy('id', 'asc')->get(); 
    }else{
      $value=DB::table('users')->where('id', $id)->first();
    }
    return $value;
  }

  public static function insertData($data){
    $value=DB::table('users')->where('username', $data['username'])->get();
    if($value->count() == 0){
      DB::table('users')->insert($data);
      return 1;
     }else{
       return 0;
     }
 
  }

  public static function updateData($id,$data){
    DB::table('users')
      ->where('id', $id)
      ->update($data);
  }

  public static function deleteData($id){
    DB::table('users')->where('id', '=', $id)->delete();
  }
 
}

4. Controller

Create Controller –

Execute php artisan make:controller PagesController to create PagesController.

Load Model –

  • Navigate to app/Http/Controllers/ folder and open PagesController.php.
  • Import Page Model from App namespace with use App\Page outside of class.

Create 3 methods –

  • index() – Fetch all records by calling Page::getuserData() and assign to $userData['data'].

If $id > 0 then fetch record of the $id and assign in $userData['editData'].

Pass $userData to index view.

  • save() – This method call on <form> submit. Read submit values using $request.

If editid field is submitted then read values and initialize $data Array and pass in Page::updateData() with $editid for update record.

If editid field is not submitted then read values and initialize $data Array and pass in Page::insertData() for insert record.

Redirect to PagesController@index.

  • deleteUser() – This is called when delete button gets clicked. Pass $id in Page::deleteData() for delete record.

Redirect to PagesController@index.

Completed Code

<?php

namespace App\Http\Controllers;

use Session;
use Illuminate\Http\Request;
use App\Page;

class PagesController extends Controller{
 
  public function index($id=0){
 
    // Fetch all records
    $userData['data'] = Page::getuserData();
 
    $userData['edit'] = $id;

    // Fetch edit record
    if($id>0){
      $userData['editData'] = Page::getuserData($id);
    }

    // Pass to view
    return view('index')->with("userData",$userData);
  }

  public function save(Request $request){
 
    if ($request->input('submit') != null ){

      // Update record
      if($request->input('editid') !=null ){
        $name = $request->input('name');
        $email = $request->input('email');
        $editid = $request->input('editid');

        if($name !='' && $email != ''){
           $data = array('name'=>$name,"email"=>$email);
 
           // Update
           Page::updateData($editid, $data);

           Session::flash('message','Update successfully.');
 
        }
 
      }else{ // Insert record
         $name = $request->input('name');
         $username = $request->input('username');
         $email = $request->input('email');

         if($name !='' && $username !='' && $email != ''){
            $data = array('name'=>$name,"username"=>$username,"email"=>$email);
 
            // Insert
            $value = Page::insertData($data);
            if($value){
              Session::flash('message','Insert successfully.');
            }else{
              Session::flash('message','Username already exists.');
            }
 
         }
      }
 
    }
    return redirect()->action('PagesController@index',['id'=>0]);
  }

  public function deleteUser($id=0){

    if($id != 0){
      // Delete
      Page::deleteData($id);

      Session::flash('message','Delete successfully.');
      
    }
    return redirect()->action('PagesController@index',['id'=>0]);
  }
}

5. Route

Open routes/web.php file.

Define 4 routes –

  • /
  • /{id} – Called when Update button gets clicked where pass the id.
  • /save – Called on <form> post.
  • /deleteUser/{id} – Called when delete button clicked.

Completed Code

<?php

// Controller-name@method-name
Route::get('/', 'PagesController@index'); // localhost:8000/
Route::get('/{id}', 'PagesController@index');
Route::post('/save', 'PagesController@save');
Route::get('/deleteUser/{id}', 'PagesController@deleteUser');

6. View

Create a new index.blade.php file in resources/views/ folder.

Create two <form> –

  • In the first <form> set action='/save' and method='post'. Add textboxes and a button to add a new record. List all fetched records and add <a > for Update and Delete.
  • The second <form> visible when the Update button gets clicked. In the form display values from $userData["editData"] in the input elements.

Completed Code

<!doctype html>
<html>
 <body>
   <form method='post' action='/save'>

     <!-- Message -->
     @if(Session::has('message'))
       <p >{{ Session::get('message') }}</p>
     @endif

     <!-- Add/List records -->
     <table border='1' style='border-collapse: collapse;'>
       <tr>
         <th>Username</th>
         <th>Name</th>
         <th>Email</th>
         <th></th>
       </tr>
       <tr>
         <td colspan="4">{{ csrf_field() }}</td>
       </tr>
       <!-- Add -->
       <tr>
         <td><input type='text' name='username'></td>
         <td><input type='text' name='name'></td>
         <td><input type='email' name='email'></td>
         <td><input type='submit' name='submit' value='Add'></td>
       </tr>

       <!-- List -->
       @foreach($userData['data'] as $user)
       <tr>
         <td>{{ $user->username }}</td>
         <td>{{ $user->name }}</td>
         <td>{{ $user->email }}</td>
         <td><a href='/{{ $user->id }}'>Update</a> <a href='/deleteUser/{{ $user->id }}'>Delete</a></td>
       </tr>
       @endforeach
    </table>
  </form>

  <!-- Edit -->
  @if($userData['edit'])
  <form method='post' action='/save'>
   <table>
     <tr>
       <td colspan='2'><h1>Edit record</h1></td>
     </tr>
     <tr>
       <td colspan="2">{{ csrf_field() }}</td>
     </tr>
     <tr>
       <td>Username</td>
       <td><input type='text' name='uname' readonly value='{{ $userData["editData"]->username }}' ></td>
     </tr>
     <tr>
       <td>Name</td>
       <td><input type='text' name='name' value='{{ $userData["editData"]->name }}'></td>
     </tr> 
     <tr>
       <td>Email</td>
       <td><input type='email' name='email' value='{{ $userData["editData"]->email }}' ></td>
     </tr>
     <tr>
       <td>&nbsp;<input type='hidden' value='{{ $userData["edit"] }}' name='editid'></td>
       <td><input type='submit' name='submit' value='Submit'></td>
     </tr>
   </table>
  </form>
  @endif
 
 </body>
</html>

7. Conclusion

Create your methods in the Model for Database manipulation and handle them from the controller.

Make sure to add csrf_field() in your <form> otherwise Request object unable to read submit values.

View this tutorial, to know CRUD (Create Read Update Delete) using Eloquent in Laravel 7.

If you found this tutorial helpful then don't forget to share.

© 2016-2024 Makitweb, All rights reserved