Fetch records from MySQL with jQuery AJAX – Laravel 9

How to Fetch records from MySQL with jQuery AJAX – Laravel 9

Step 1 : Create Laravel 9 Project
Step 2 : Project Configuration the Database
Step 3 : Project Table Structure
Step 4 : Project Models
Step 5 : Project Controllers
Step 6 : Project Routes
Step 7 : Project Views
Step 8 : Project Output
Step 9 : Project Conclusion

So, let us begin.

Step 1 : Create Laravel 9 Project

First we are going to create a latest laravel 9 project with the help of composer.

If your local machine already has PHP and Composer installed, you may create a new Laravel project by using Composer directly. After the application has been created, you may start Laravel’s local development server using the Artisan CLI’s serve command:

composer create-project laravel/laravel laraveltuts
 
cd laraveltuts


php artisan serve

Once you have started the Artisan development server, you may access your application at http://localhost:8000 or http://127.0.0.1:8000.

Step 2 : Project Configuration the Database

Now we have to configure the laravel database for which we have to edit the .env file.

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

Because we are using mysql in this tutorial, the database connection will be mysql. Then enter the hostname, database name, database username and database password.

Step 3 : Project Table Structure

Now we are going to create a employee table. For creating a employee table please run the following code.

php artisan make:migration create_employee_table

This will create a migration file in database/migration/ folder with a name create_employee_table.php just open the file and add the field as follow.

public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('username');
            $table->string('name');
            $table->string('email');
            $table->timestamps();
        });
    }

Then run the migration command to create a table into the database.

php artisan migrate

The table has been create and just add some records to it for testing.

Also Read : How to Create a User using tinker in laravel 9

Step 4 : Project Models

After creating a table we have to create a employees model. So we are going to create using the artisan command.

php artisan make:model Employees

That will create a models file in app/Models/Employees.php

Using the $fillable property, specify mass assignable Model attributes such as username, name, and email.

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
 
class Employees extends Model
{
   use HasFactory;
 
   protected $fillable = [
      'username','name','email'
   ];
}

Step 5 : Project Controllers

Now are going to create a employee controller by run the artisan command.

php artisan make:controller EmployeeController

EmployeeController going to have three methods:

index()
getUsers()
getUserbyid()

index() – Load employees view.

getUsers() – This method is used to handle AJAX GET request.

we are going to fetch all records from the employees table and then assign to $employees. Assign $employees to $response[‘data’] Array.

Return $response Array in JSON format.

getUserbyid() – This method is used to handle AJAX POST request. Read POST value and assign to the $userid variable.
Search record by id from the employees table. Assign $employees to $response[‘data’] Array.

Return $response Array in JSON format.

<?php
 
namespace App\Http\Controllers;
 
use Illuminate\Http\Request;
use App\Models\Employees;
 
class EmployeeController extends Controller
{
   public function index(){
     return view('employees');
   }
 
   public function getUsers(){
 
     $employees = Employees::orderby('id','asc')->select('*')->get(); 
      
     // Fetch all records
     $response['data'] = $employees;
 
     return response()->json($response);
   }
 
   public function getUserbyid(Request $request){
 
      $userid = $request->userid;
 
      $employees = Employees::select('*')->where('id', $userid)->get();
 
      // Fetch all records
      $response['data'] = $employees;
 
      return response()->json($response);
   }
}

Step 6 : Project Routes

So, Now are are going to create some route. For the we have to open routes/web.php file.

Define 3 routes –

‘/’ – Load employees view.
‘/getUsers’ – This use to send AJAX GET request.
‘/getUsersbyid’ – This use to send AJAX POST request.

<?php
 
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeeController;
 
Route::get('/', [EmployeeController::class, 'index']);
Route::get('/getUsers', [EmployeeController::class, 'getUsers']);
Route::post('/getUserbyid', [EmployeeController::class, 'getUserbyid']);

Step 7 : Project Views

Now everything is done with creating database table, model, controller and route. Now we have to create the blade file which is going to be the view.

Create a blade file in resources/views/ with name employees.blade.php

First Download JQuery and store them to public/js folder

or you can use CDN.

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

Structure of Blade Code:

HTML

Create a textbox and two buttons and list all fetch records in with jQuery AJAX.

The first button uses to fetch records according to entered userid in the textbox.
The second button uses to fetch all records.

Script

Read CSRF token from the tag and assign it to CSRF_TOKEN variable. Important to add CSRF token or its will give CSRF 403 Forbidden error.

Define click event on #but_fetchall and #but_search.

Also Read : Laravel 9 Shopping Cart Tutorial with Ajax Example

If #but_fetchall is gets clicked then send AJAX GET request to ‘getUsers’, set dataType: ‘json’. On successful callback pass response to createRows() function to create table rows.

If #but_search is gets clicked then read value from the textbox and assign it to userid variable. Send AJAX POST request to ‘getUserbyid‘, pass CSRF_TOKEN and userid as data, set dataType: ‘json’. On successful callback pass response to createRows() function to create table rows.

createRows() – Empty

If response[‘data’] length is greater than 0 then loop on the response[‘data’] and create new and append in #empTable tbody otherwise, append “No record found”.

<!doctype html>
<html>
<head>
   <title>LaravelTuts.com - Fetch records from MySQL with jQuery AJAX – Laravel 9</title>
   <!-- Meta -->
   <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
   <meta charset="utf-8">
   <meta name="csrf-token" content="{{ csrf_token() }}">
</head>
<body>
   <input type='text' id='search' name='search' placeholder='Enter userid 1-27'>
   <input type='button' value='Search' id='but_search'>
   <br/>
   <input type='button' value='Fetch all records' id='but_fetchall'>
 
   <!-- Table -->
   <table border='1' id='empTable' style='border-collapse: collapse;'>
     <thead>
       <tr>
         <th>S.no</th>
         <th>Username</th>
         <th>Name</th>
         <th>Email</th>
       </tr>
     </thead>
     <tbody></tbody>
   </table>
 
   <!-- Script CDN -->
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
   <!-- Script Local -->

   <!-- <script src="{{asset('js/jquery-3.6.0.min.js')}}"></script> -->
 
   <script type='text/javascript'>
   var CSRF_TOKEN = $('meta[name="csrf-token"]').attr('content');
   $(document).ready(function(){
 
      // Fetch all records
      $('#but_fetchall').click(function(){
 
         // AJAX GET request
         $.ajax({
           url: 'getUsers',
           type: 'get',
           dataType: 'json',
           success: function(response){
 
              createRows(response);
 
           }
         });
      });
 
      // Search by userid
      $('#but_search').click(function(){
         var userid = Number($('#search').val().trim());
 
         if(userid > 0){
 
           // AJAX POST request
           $.ajax({
              url: 'getUserbyid',
              type: 'post',
              data: {_token: CSRF_TOKEN, userid: userid},
              dataType: 'json',
              success: function(response){
 
                 createRows(response);
 
              }
           });
         }
 
      });
 
   });
 
   // Create table rows
   function createRows(response){
      var len = 0;
      $('#empTable tbody').empty(); // Empty <tbody>
      if(response['data'] != null){
         len = response['data'].length;
      }
 
      if(len > 0){
        for(var i=0; i<len; i++){
           var id = response['data'][i].id;
           var username = response['data'][i].username;
           var name = response['data'][i].name;
           var email = response['data'][i].email;
 
           var tr_str = "<tr>" +
             "<td align='center'>" + (i+1) + "</td>" +
             "<td align='center'>" + username + "</td>" +
             "<td align='center'>" + name + "</td>" +
             "<td align='center'>" + email + "</td>" +
           "</tr>";
 
           $("#empTable tbody").append(tr_str);
        }
      }else{
         var tr_str = "<tr>" +
           "<td align='center' colspan='4'>No record found.</td>" +
         "</tr>";
 
         $("#empTable tbody").append(tr_str);
      }
   } 
   </script>
</body>
</html>

Step 8 : Project Output

Step 9 : Project Conclusion

I hope you found this code and post useful in implementing Laravel 9 Fetch records from MySQL with jQuery AJAX. If you need assistance or feedback, please leave it in the comments section. If you have a good idea for this post, please share it in the comments section. Your feedback will allow us to better serve you.

Also Read : Laravel 9 Multiple Database Connection Tutorial