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

Retrieving data is one of the basic requirement when working with AJAX. Data gets loaded without reloading the whole page.

In this tutorial, I show how you can fetch records using the GET and POST method from MySQL database with jQuery AJAX in Laravel 9.

Demo

Database Configuration
Table structure
Model
Controller
Route
View
Demo
Conclusion

1. 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=

2. Table structure

Create a new table employees using migration.

php artisan make:migration create_employees_table

Now, navigate to database/migrations/ folder from the project root.
Find a PHP file that ends with create_employees_table and open it.
Define the table structure in the up() method.

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

Run the migration –

php artisan migrate

The table is been created and I added some records to it.

3. Model

Create Employees Model.

php artisan make:model Employees

Open app/Models/Employees.php file.
Specify mass assignable Model attributes – username, name, and email using the $fillable property.

Completed Code

<?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' 
   ];
}

4. Controller

Create EmployeesController controller.

php artisan make:controller EmployeesController

Create 3 methods –

index() – Load index view.
getEmployees() – This method is used to handle AJAX GET request.

Fetch all records from the employees table and assign to $employees. Assign $employees to $response['data'] Array.

Return $response Array in JSON format.

getEmployeebyid() – This method is used to handle AJAX POST request. Read POST value and assign to the $employeeid variable.

Search record by id from the employees table. Assign $employees to $response['data'] Array.

Return $response Array in JSON format.

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Employees;

class EmployeesController extends Controller
{
   public function index(){
     return view('index');
   }

   public function getEmployees(){

     $employees = Employees::orderby('id','asc')->select('*')->get(); 
     
     // Fetch all records
     $response['data'] = $employees;

     return response()->json($response);
   }

   public function getEmployeebyid(Request $request){

      $employeeid = $request->employeeid;

      $employees = Employees::select('*')->where('id', $employeeid)->get();

      // Fetch all records
      $response['data'] = $employees;

      return response()->json($response);
   }
}

5. Route

Open routes/web.php file.
Define 3 routes –
- / – Load index view.
- /getEmployees – This is GET type route to fetch all records.
- /getEmployeebyid – This is POST type route to record by id.

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index']);
Route::get('/getEmployees', [EmployeesController::class, 'getEmployees']);
Route::post('/getEmployeebyid', [EmployeesController::class, 'getEmployeebyid']);

6. View

Create index.blade.php file in resources/views/.

HTML

Store CSRF token in <meta > tag.
Create a textbox to enter id and 2 buttons –
- 1st button to fetch record by employee id and
- 2nd button to fetch all employees list.
Use <table id="empTable"> to list fetched records using jQuery AJAX.

Script

Read CSRF token from the <meta > tag and assign it to CSRF_TOKEN variable.
Define click event on #but_fetchall and #but_search.
If #but_fetchall is gets clicked then send AJAX GET request to 'getEmployees', 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 employeeid variable. Send AJAX POST request to 'getEmployeebyid', pass CSRF_TOKEN and employeeid as data, set dataType: 'json'. On successful callback pass response to createRows() function to create table rows.

createRows() – Empty <table> <tbody>. If response['data'] length is greater than 0 then loop on the response['data'] and create new <tr > and append in #empTable tbody otherwise, append “No record found” <tr> in <tbody>.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <title>How to 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-24'>
   <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 -->
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.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: 'getEmployees',
           type: 'get',
           dataType: 'json',
           success: function(response){

              createRows(response);

           }
         });
      });

      // Search by userid
      $('#but_search').click(function(){
         var employeeid = Number($('#search').val().trim());

         if(employeeid > 0){

           // AJAX POST request
           $.ajax({
              url: 'getEmployeebyid',
              type: 'post',
              data: {_token: CSRF_TOKEN, employeeid: employeeid},
              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>

7. Demo

View Demo

8. Conclusion

You can either use GET or POST method while sending an AJAX request according to your requirement.

Make sure to pass the CSRF token with data when sending using the POST method otherwise data will not fetch.

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

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

Contents

1. Database Configuration

2. Table structure

3. Model

4. Controller

5. Route

6. View

7. Demo

8. Conclusion

Leave a Comment Cancel reply