Fetch records from MySQL with jQuery AJAX – Laravel 7

Last updated on by

AJAX makes it easier to load records from the MySQL database without reloading the whole page.

Handle AJAX request from the controller and database manipulation in the model.

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

Fetch records from MySQL with jQuery AJAX - Laravel 7

Contents

  1. Table structure
  2. Database Configuration
  3. Model
  4. Controller
  5. Route
  6. View
  7. Output
  8. Conclusion

1. Table structure

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

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

3. Model

  • Create Employees Model.
php artisan make:model Employees
  • Specify mass assignable Model attributes – username, name, and email using the $fillable property.

Completed Code

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{

   protected $fillable = [
      'username','name','email' 
   ];

}

4. Controller

Create a EmployeesController controller.

php artisan make:controller EmployeesController

Create two methods –

  • index() – Load employee_data view.
  • getUsers() – This method is call from $.ajax request in jQuery.

If $id value equals 0 then select all records from employees table otherwise select record by id from employees table.

Assign fetched records to $userdata['data']. Return $userdata Array in JSON format.

Completed Code

<?php

namespace App\Http\Controllers;

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

class EmployeesController extends Controller{

  public function index(){
     return view('employee_data');
  }

  public function getUsers($id = 0){

     if($id==0){ 
        $employees = Employees::orderby('id','asc')->select('*')->get(); 
     }else{   
        $employees = Employees::select('*')->where('id', $id)->get(); 
     }
     // Fetch all records
     $userData['data'] = $employees;

     echo json_encode($userData);
     exit;
  }
}

5. Route

Define 2 routes in routes/web.php file –

  • /
  • getUsers/{id} – This is used in jQuery AJAX.

Completed Code

<?php

// Controller-name@method-name
Route::get('/', 'EmployeesController@index'); // localhost:8000/
Route::get('/getUsers/{id}','EmployeesController@getUsers');

6. View

Create a new employee_data.blade.php file in resources/views/ folder.

Download jQuery library and store it in public/js/ folder.

Or you can also use CND –

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

HTML

Create a textbox and two buttons and list all fetch records in <table> 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

Create a function fetchRecords from where send AJAX GET request to 'getUsers/'+id. On successful callback empty the <table> <tbody> and get response['data'] length is not null.

Add a new row to <tbody> according to the following conditions –

  • If len>0 then loop on response['data'] and create new <tr> and append in <tbody>.
  • If len does not greater than 0 then create a single <tr> with No record found. message in <td> and append in <tbody>.

On the search button click gets the textbox value and pass in fetchRecords() function.

Completed Code

<!doctype html>
<html>
   <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 border='1' id='userTable' 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> --> <!-- jQuery CDN -->
     <script src="{{asset('js/jquery-3.6.0.min.js')}}"></script>

     <script type='text/javascript'>
     $(document).ready(function(){

       // Fetch all records
       $('#but_fetchall').click(function(){
	 fetchRecords(0);
       });

       // Search by userid
       $('#but_search').click(function(){
          var userid = Number($('#search').val().trim());
				
	  if(userid > 0){
	    fetchRecords(userid);
	  }

       });

     });

     function fetchRecords(id){
       $.ajax({
         url: 'getUsers/'+id,
         type: 'get',
         dataType: 'json',
         success: function(response){

           var len = 0;
           $('#userTable 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>";

                 $("#userTable tbody").append(tr_str);
              }
           }else{
              var tr_str = "<tr>" +
                  "<td align='center' colspan='4'>No record found.</td>" +
              "</tr>";

              $("#userTable tbody").append(tr_str);
           }

         }
       });
     }
     </script>
  </body>
</html>

7. Output

View Output

8. Conclusion

You need to create a separate method in the controller to handle the AJAX request from jQuery.

If you want to fetch records using the POST method instead of GET then you need to define POST route and need to send CSRF token with the AJAX request.

You can view this tutorial, to know fetch records with jQuery AJAX in Laravel 8.

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

© 2016-2023 Makitweb, All rights reserved