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

Retrieving data is one of the basic requirements when working with the database using AJAX.

Showing data based on the user login, generating a report, etc.

In this tutorial, I show how you can fetch records from MySQL database using jQuery AJAX in Laravel 8.

 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/migration/ 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 a EmployeesController controller.

php artisan make:controller EmployeesController

Create 3 methods –

• index() – Load employees view.
• getUsers() – 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.

• 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.

Completed Code

<?php

namespace App\Http\Controllers;

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

class EmployeesController 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);
   }
}

5. Route

• 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\EmployeesController;

Route::get('/', [EmployeesController::class, 'index']);
Route::get('/getUsers', [EmployeesController::class, 'getUsers']);
Route::post('/getUserbyid', [EmployeesController::class, 'getUserbyid']);

6. View

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

HTML

Create a textbox to enter user id and 2 buttons. 1st button to fetch record by user id and 2nd button to fetch all users list.

Use <table id="empTable"> to list 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 '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 <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 8</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-7'>
   <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.5.1/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: '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>

7. Output

8. Conclusion

In the example, I showed you both GET and POST ways to retrieve data.