Laravel 9 Fetch records from MySQL with jQuery AJAX

Fetch records from MySQL with jQuery AJAX – Laravel 9

Laravel 9 Fetch records from MySQL with jQuery AJAX

In this post, we will give you information about Laravel 9 Fetch records from MySQL with jQuery AJAX. Here we will give you detail about Laravel 9 Fetch records from MySQL with jQuery AJAX And how to use it also give you a demo for it if it is necessary.

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 post, I show how you can fetch records from MySQL database using jQuery AJAX in Laravel 9.

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=employee
DB_USERNAME=root
DB_PASSWORD=

2. Table structure

Create a new table employees using migration.

php artisan make:migration create_employee_table

Now, navigate to database/migration/ folder from the project root.
Find a PHP file that ends with create_employee_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 EmployeeController controller.

php artisan make:controller EmployeeController

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

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\EmployeeController;

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

6. View
[/php]
Create employees.blade.php file in resources/views/.
[/php]
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

to list records using jQuery AJAX.

Script

Read CSRF token from the 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

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

in

.

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

Conclusion for Laravel 9 Fetch records from MySQL with jQuery AJAX

Hope this code and post will helped you for implement Laravel 9 Fetch records from MySQL with jQuery AJAX. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us.

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

  +  55  =  64

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US