With autopopulate dropdowns, a user is restricted to select an option from the available options based on the selection of its previous selection.
In this tutorial, I show how you can autopopulate dropdown with MySQL database data using jQuery AJAX in Laravel 8.
Contents
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
departments
andemployees
table using migration and add some records.
php artisan make:migration create_departments_table 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_departments_table
and open it. - Define the table structure in the
up()
method.
public function up() { Schema::create('departments', function (Blueprint $table) { $table->id(); $table->string('name'); $table->timestamps(); }); }
- Similarly, 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->id(); $table->string('username'); $table->string('name'); $table->string('email'); $table->integer('department'); $table->timestamps(); }); }
- Run the migration –
php artisan migrate
- The table is been created and I added some records in it.
3. Model
Create 2 Models –
Departments –
php artisan make:model Departments
- Specify mass assignable Model attributes – name using the
$fillable
property.
Completed Code
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Departments extends Model { use HasFactory; protected $fillable = [ 'name' ]; }
Employees –
php artisan make:model Employees
- Specify mass assignable Model attributes – username, name, email, and department 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','department' ]; }
4. Controller
Create a DepartmentsController
controller.
php artisan make:controller DepartmentsController
Create 2 methods –
- index() – Fetch all records from the
departments
table and assign to$departments['data']
.
Load index
view and pass $departments
.
- getEmployees() – This method is use to handle AJAX request.
Fetch records from the employees
table where department = $departmentid
and assign to $empData['data']
.
Return $empData
in JSON format.
Completed Code
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Models\Departments; use App\Models\Employees; class DepartmentsController extends Controller { public function index(){ // Fetch departments $departments['data'] = Departments::orderby("name","asc") ->select('id','name') ->get(); // Load index view return view('index')->with("departments",$departments); } // Fetch records public function getEmployees($departmentid=0){ // Fetch Employees by Departmentid $empData['data'] = Employees::orderby("name","asc") ->select('id','name') ->where('department',$departmentid) ->get(); return response()->json($empData); } }
5. Route
- Open
routes/web.php
file. - Define 2 routes –
- / – Load index view.
- /getEmployees/{id} – This is use to send AJAX GET request to fetch department employees list.
<?php use Illuminate\Support\Facades\Route; use App\Http\Controllers\DepartmentsController; Route::get('/', [DepartmentsController::class, 'index']); Route::get('/getEmployees/{id}', [DepartmentsController::class, 'getEmployees']);
6. View
Create a new index.blade.php
file in resources/views/
folder.
HTML –
Create two <select >
elements –
- The first
<select >
element is for departments. Loop on the$departments['data']
to add<option>
. - The second
<select >
element is for employees. Data is loaded in this dropdown based on the department selection using jQuery AJAX.
jQuery –
Define on change
event on the first dropdown.
Read selected value and assign to id
variable. Empty the second dropdown except the first <option>
.
Send AJAX GET request to 'getEmployees/'
and also pass id
value, set dataType: 'json'
.
On successful callback check response
length. If length is greater than 0 then loop on the response
.
Create options and append in #sel_emp
.
Completed Code
<!DOCTYPE html> <html> <head> <title>Auto populate Dropdown with jQuery AJAX in Laravel 8</title> </head> <body> <!-- Department Dropdown --> Department : <select id='sel_depart' name='sel_depart'> <option value='0'>-- Select department --</option> <!-- Read Departments --> @foreach($departments['data'] as $department) <option value='{{ $department->id }}'>{{ $department->name }}</option> @endforeach </select> <br><br> <!-- Department Employees Dropdown --> Employee : <select id='sel_emp' name='sel_emp'> <option value='0'>-- Select Employee --</option> </select> <!-- Script --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script> <script type='text/javascript'> $(document).ready(function(){ // Department Change $('#sel_depart').change(function(){ // Department id var id = $(this).val(); // Empty the dropdown $('#sel_emp').find('option').not(':first').remove(); // AJAX request $.ajax({ url: 'getEmployees/'+id, type: 'get', dataType: 'json', success: function(response){ var len = 0; if(response['data'] != null){ len = response['data'].length; } if(len > 0){ // Read data and create <option > for(var i=0; i<len; i++){ var id = response['data'][i].id; var name = response['data'][i].name; var option = "<option value='"+id+"'>"+name+"</option>"; $("#sel_emp").append(option); } } } }); }); }); </script> </body> </html>
7. Demo
8. Conclusion
If on your page there is more than one dropdown that you want to autopopulate then follow the same steps to load data.
If you found this tutorial helpful then don't forget to share.