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

Last updated on by

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.

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

Contents

  1. Database Configuration
  2. Table structure
  3. Model
  4. Controller
  5. Route
  6. View
  7. Output
  8. 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 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.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: '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

View Output

8. Conclusion

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

CSRF token is required when sending AJAX POST requests.

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

Leave a Comment

© 2016-2024 Makitweb, All rights reserved