Fetch records from MySQL with jQuery AJAX – Laravel

AJAX makes easier to load records from 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 MySQL database with jQuery AJAX in Laravel.

Fetch records from MySQL with jQuery AJAX – Laravel


Contents

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

 


1. Table structure

I am using users table in the example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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 a Page model.

php artisan make:model Page

Here, create a single method –

  • getuserData – Fetch all records from users table and return it. If $id value is not 0 then select a record by id.

Completed

<?php

namespace App;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;

class Page extends Model {
  public static function getuserData($id=0){

    if($id==0){
      $value=DB::table('users')->orderBy('id', 'asc')->get(); 
    }else{
      $value=DB::table('users')->where('id', $id)->first();
    }
    return $value;
  
  }
}

4. Controller

Create a Pages controller.

php artisan make:controller PagesController

Create two methods –

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

Fetch all records by calling Page::getuserData($id) where pass $id and initialize $userData['data']. Return in JSON fromat.

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Page;

class PagesController extends Controller{

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

  public function getUsers($id = 0){
    // Fetch all records
    $userData['data'] = Page::getuserData($id);

    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('/', 'PagesController@index'); // localhost:8000/
Route::get('/getUsers/{id}','PagesController@getUsers');

6. View

Create a new user_view.blade.php file in resources/views/ directory.

Download jQuery library and store in public/js/ directory.

Fetch records from MySQL with jQuery AJAX – Laravel

Or you can also use CND –

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/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 new row to <tbody> according to following conditions –

  • If len>0 then loop on response['data'] and create new <tr> and append in <tbody>.
  • If len is not greater than 0 and response['data'] is not null then create a single <tr> and append in <tbody>.
  • If len is not greater than 0 and response['data'] is null 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.3.1/jquery.min.js"></script> --> <!-- jQuery CDN -->
     <script src="{{asset('js/jquery-3.3.1.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 if(response['data'] != null){
              var tr_str = "<tr>" +
                  "<td align='center'>1</td>" +
                  "<td align='center'>" + response['data'].username + "</td>" + 
                  "<td align='center'>" + response['data'].name + "</td>" +
                  "<td align='center'>" + response['data'].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


8. Conclusion

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

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

Related Post

Spread the love
  • 4
  •  
  •  
  •  
  •  
  •  

Be First to Comment

Leave a Reply

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