Insert Update and Delete record from MySQL in Laravel

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 –

  • Open Command Prompt.
  • Navigate to your project folder.
  • Execute php artisan make:model Page. Here, I created Page model.
  • This will create a new file Page.php in app/ folder.

Create Model in Laravel 7

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 new record. Check username is exists or not in users table. If no record 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 –

  • Open Command Prompt.
  • Navigate to project folder.
  • Now, execute php artisan make:controller PagesController. Here, I created PagesController.

Create controller in Laravel 7

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 [email protected].

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

Redirect to [email protected].

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('[email protected]',['id'=>0]);
  }

  public function deleteUser($id=0){

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

      Session::flash('message','Delete successfully.');
      
    }
    return redirect()->action('[email protected]',['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

// [email protected]
Route::get('/', '[email protected]'); // localhost:8000/
Route::get('/{id}', '[email protected]');
Route::post('/save', '[email protected]');
Route::get('/deleteUser/{id}', '[email protected]');

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.

16 thoughts on “Insert Update and Delete record from MySQL in Laravel”

  1. I got below error :
    protected function methodNotAllowed(array $others)
    {
    throw new MethodNotAllowedHttpException($others);
    }

    Symfony\Component\HttpKernel\Exception\MethodNotAllowedHttpException
    …/vendor/laravel/framework/src/Illuminate/Routing/RouteCollection.php255

    Reply
  2. Hi Rashid,

    The class Page extends the Model base class, and so will already have CRUD methods inherited such as create, update, delete. If this is the case, why have you added your own methods (e.g. insertData and UpdateData), rather than using the base class methods?

    Thanks.

    Reply
  3. hi i’ve tried this and im having an error. the DELETE is working, but im having problem with the ADD and UPDATE. when i clicked ADD , it says “SQLSTATE[HY000]: General error: 1364 Field ‘position’ doesn’t have a default value (SQL: insert into `users` (`name`, `username`, `email`) values…”.
    for UPDATE- im having error in my blade. “Undefined index: editData (View: ..)”

    Reply

Leave a Comment