How to Auto populate Dropdown using jQuery AJAX in CakePHP 4

Dynamic-dependent dropdowns are very helpful to restrict user selection. You may have already seen this on the registration or profile page of the website where you need to select country, state, and city. Options in the dropdown update every time when a country or state dropdown selection gets changed.

If your tables are properly interlinked in the database and want to list their data on the dropdown then you can make them dynamically dependent on one another and load their data using AJAX.

In this tutorial, I show how you can auto populate dropdown using jQuery AJAX with MySQL database data in CakePHP 4.

How to Autopopulate Dropdown using jQuery AJAX in CakePHP 4

Create Table
Database Configuration
Create Model
Create Controller
Include jQuery and CSRF token
Create Template
Output
Conclusion

1. Create Table

In the example, I am using 3 tables –

countries
states
cities

countries (Store countries name) –

CREATE TABLE `countries` (
    `id` int(10) UNSIGNED NOT NULL,
    `name` varchar(80) NOT NULL
);

ALTER TABLE `countries`
    ADD PRIMARY KEY (`id`);

ALTER TABLE `countries`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

states (Store country states names) –

Added foreign key on country_id field.

CREATE TABLE `states` (
    `id` int(10) UNSIGNED NOT NULL,
    `country_id` int(10) UNSIGNED NOT NULL,
    `name` varchar(80) NOT NULL
);

ALTER TABLE `states`
    ADD PRIMARY KEY (`id`),
    ADD KEY `states_country_id_foreign` (`country_id`);

ALTER TABLE `states`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

cities (Store state cities name) –

Added foreign key on state_id field.

CREATE TABLE `cities` (
    `id` int(10) UNSIGNED NOT NULL,
    `state_id` int(10) UNSIGNED NOT NULL,
    `name` varchar(80) NOT NULL
);

ALTER TABLE `cities`
    ADD PRIMARY KEY (`id`),
    ADD KEY `cities_state_id_foreign` (`state_id`);

ALTER TABLE `cities`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

2. Database Configuration

Open config/app_local.php file.
Specify your database configuration details in the Datasources default.

'Datasources' => [
     'default' => [
          'host' => '127.0.0.1',
          /*
          * CakePHP will use the default DB port based on the driver selected
          * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
          * the following line and set the port accordingly
          */
          //'port' => 'non_standard_port_number',

          'username' => 'root',
          'password' => 'root',

          'database' => 'cakephp4',
          /*
          * If not using the default 'public' schema with the PostgreSQL driver
          * set it here.
          */
          //'schema' => 'myapp',

          /*
          * You can use a DSN string to set the entire configuration
          */
          'url' => env('DATABASE_URL', null),
     ],

     /*
     * The test connection is used during the test suite.
     */
     'test' => [
          'host' => 'localhost',
          //'port' => 'non_standard_port_number',
          'username' => 'my_app',
          'password' => 'secret',
          'database' => 'test_myapp',
          //'schema' => 'myapp',
          'url' => env('DATABASE_TEST_URL', 'sqlite://127.0.0.1/tests.sqlite'),
     ],
],

3. Create Model

Create 3 models –

Countries
States
Cities

Countries Model –

bin/cake bake model Countries

This will create 2 files –

src/Model/Entity/Country.php

<?php
declare(strict_types=1);

namespace App\Model\Entity;

use Cake\ORM\Entity;

class Country extends Entity
{
     protected $_accessible = [ 
         'name' => true,
         'states' => true,
     ];
}

src/Model/Table/CountriesTable.php

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class CountriesTable extends Table
{
     public function initialize(array $config): void
     {
           parent::initialize($config);

           $this->setTable('countries');
           $this->setDisplayField('name');
           $this->setPrimaryKey('id');

           $this->hasMany('States', [
               'foreignKey' => 'country_id',
           ]);
     }

     public function validationDefault(Validator $validator): Validator
     {
           $validator
               ->scalar('name')
               ->maxLength('name', 255)
               ->requirePresence('name', 'create')
               ->notEmptyString('name');

           return $validator;
     }
}

States Model –

bin/cake bake model States

This will create 2 files –

src/Model/Entity/State.php

<?php
declare(strict_types=1);

namespace App\Model\Entity;

use Cake\ORM\Entity;

class State extends Entity
{
     protected $_accessible = [
         'country_id' => true,
         'name' => true,
         'country' => true,
         'cities' => true,
     ];
}

src/Model/Table/StatesTable.php

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class StatesTable extends Table
{

     public function initialize(array $config): void
     {
           parent::initialize($config);

           $this->setTable('states');
           $this->setDisplayField('name');
           $this->setPrimaryKey('id');

           $this->belongsTo('Countries', [
               'foreignKey' => 'country_id',
               'joinType' => 'INNER',
           ]);
           $this->hasMany('Cities', [
               'foreignKey' => 'state_id',
           ]);
     }

     public function validationDefault(Validator $validator): Validator
     {
           $validator
               ->notEmptyString('country_id');

           $validator
               ->scalar('name')
               ->maxLength('name', 255)
               ->requirePresence('name', 'create')
               ->notEmptyString('name');

           return $validator;
     }

     public function buildRules(RulesChecker $rules): RulesChecker
     {
           $rules->add($rules->existsIn('country_id', 'Countries'), ['errorField' => 'country_id']);

           return $rules;
     }
}

Cities Model –

This will create 2 files –

src/Model/Entity/City.php

<?php
declare(strict_types=1);

namespace App\Model\Entity;

use Cake\ORM\Entity;

class City extends Entity
{
     protected $_accessible = [
         'state_id' => true,
         'name' => true,
         'state' => true,
     ];
}

src/Model/Table/CitiesTable.php

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

class CitiesTable extends Table
{

     public function initialize(array $config): void
     {
          parent::initialize($config);

          $this->setTable('cities');
          $this->setDisplayField('name');
          $this->setPrimaryKey('id');

          $this->belongsTo('States', [
              'foreignKey' => 'state_id',
              'joinType' => 'INNER',
          ]);
     }

     public function validationDefault(Validator $validator): Validator
     {
          $validator
              ->notEmptyString('state_id');

          $validator
              ->scalar('name')
              ->maxLength('name', 255)
              ->requirePresence('name', 'create')
              ->notEmptyString('name');

          return $validator;
     }

     public function buildRules(RulesChecker $rules): RulesChecker
     {
          $rules->add($rules->existsIn('state_id', 'States'), ['errorField' => 'state_id']);

          return $rules;
     }
}

4. Create Controller

Create a AutopopulateController.php file in src/Controller/ folder.
Create AutopopulateController Class that extends AppController.

Create 3 method –

index() – Fetch all records from the countries table. Loop on the fetched records and store them in $country_arr Array. Using $this->set() pass $country_arr Array to template.
getCountryStates() – Using this method return states list of the requested country from AJAX request.

Read POST country_id and assign it to the variable. Fetch all records from the states table where country_id = $country_id.

Loop on the fetch records and store state id and name in $data_arr Array. Return $data_arr Array in JSON format.

getStateCities() – Using this method return cities list of the requested state from AJAX request.

Read POST state_id and assign it to the variable. Fetch all records from the cities table where state_id = $state_id.

Loop on the fetch records and store city id and name in $data_arr Array. Return $data_arr Array in JSON format.

Completed Code

<?php
declare(strict_types=1);

namespace App\Controller;

class AutopopulateController extends AppController
{

     public function index(){

          ## Fetch all countries
          $countries = $this->getTableLocator()->get('Countries');
          $query = $countries->find('all')->order(['name' => 'ASC']);
          $countriesList = $query->toArray();

          $country_arr = array(); 
          foreach($countriesList as $country){
               $country_arr[$country['id']] = $country['name'];
          }

          $this->set(compact('country_arr'));
     }

     // Get country states
     public function getCountryStates(){

          // POST value
          $country_id = $this->request->getData()['country_id'];

          ## Fetch all states of country_id
          $states = $this->getTableLocator()->get('States');
          $query = $states->find('all')
               ->where(['country_id' => $country_id])
               ->order(['name' => 'ASC']);
          $statesList = $query->toArray();

          $data_arr = array();
          foreach($statesList as $state){
               $data_arr[] = array(
                   'id' => $state['id'],
                   'name' => $state['name']
               );
          }

          echo json_encode($data_arr);
          die;
     }

     // Get state cities
     public function getStateCities(){

          // POST value
          $state_id = $this->request->getData()['state_id'];

          ## Fetch all cities of state_id
          $cities = $this->getTableLocator()->get('Cities');
          $query = $cities->find('all')
              ->where(['state_id' => $state_id])
              ->order(['name' => 'ASC']);
          $citiesList = $query->toArray();

          $data_arr = array();
          foreach($citiesList as $city){
               $data_arr[] = array(
                   'id' => $city['id'],
                   'name' => $city['name']
               );
          }

          echo json_encode($data_arr);
          die;
     }

}

6. Include jQuery and CSRF token

I am including jQuery and CSRF token on templates/layout/default.php file.

Stored CSRF token in <meta > tag –

<?= $this->Html->meta('csrfToken', $this->request->getAttribute('csrfToken')); ?>

and jQuery in <head > section –

<?= $this->Html->script('https://code.jquery.com/jquery.min.js'); ?>

Completed Code

<?php
$cakeDescription = 'CakePHP: the rapid development php framework';
?>
<!DOCTYPE html>
<html>
<head>
     <?= $this->Html->charset() ?>
     <meta name="viewport" content="width=device-width, initial-scale=1">

     <!-- CSRF Token -->
     <?= $this->Html->meta('csrfToken', $this->request->getAttribute('csrfToken')); ?>
     <title>
           <?= $cakeDescription ?>:
           <?= $this->fetch('title') ?>
     </title>
     <?= $this->Html->meta('icon') ?>

     <link href="https://fonts.googleapis.com/css?family=Raleway:400,700" rel="stylesheet">

     <?= $this->Html->css(['normalize.min', 'milligram.min', 'cake']) ?>

     <!-- jQuery -->
     <?= $this->Html->script('https://code.jquery.com/jquery.min.js'); ?>

     <?= $this->fetch('meta') ?>
     <?= $this->fetch('css') ?>
     <?= $this->fetch('script') ?>
</head>
<body>
     <nav class="top-nav">
          <div class="top-nav-title">
               <a href="<?= $this->Url->build('/') ?>"><span>Cake</span>PHP</a>
          </div>
          <div class="top-nav-links">
               <a target="_blank" rel="noopener" href="https://book.cakephp.org/4/">Documentation</a>
               <a target="_blank" rel="noopener" href="https://api.cakephp.org/">API</a>
          </div>
     </nav>
     <main class="main">
          <div class="container">
               <?= $this->Flash->render() ?>
               <?= $this->fetch('content') ?>
          </div>
     </main>
     <footer>
     </footer>
</body>
</html>

6. Create Template

Create Autopopulate folder in templates/ location. In the Autopopulate folder create index.php file – templates/Autopopulate/index.php.

Create 3 <select > elements –

country – Display the passed country list from the controller.
state – Empty dropdown. Data load using jQuery AJAX when a country is been selected.
city – Empty dropdown. Data load using jQuery AJAX when a state is been selected.

jQuery

Read CSRF token from the <meta > tag and assign it to csrfToken variable.

Country Change –

Define change event on #country_id. Read selected country_id and empty the state and city dropdown. If country_id is not empty then send AJAX POST request to <?= $this->Url->build(['controller' => 'Autopopulate','action' => 'getCountryStates']) ?>.

Here, pass country_id – {country_id: country_id} as data, set dataType: 'json', pass CSRF token using header.

On successful callback loop on the response and add a new <option > in state dropdown.

State Change –

Define change event on #state_id. Read selected state_id and empty the city dropdown. If state_id is not empty then send AJAX POST request to <?= $this->Url->build(['controller' => 'Autopopulate','action' => 'getStateCities']) ?>.

Here, pass state_id – {state_id: state_id} as data, set dataType: 'json', pass CSRF token using header.

On successful callback loop on the response to add <option > in city dropdown.

Completed Code

<div class="row">
     <div class="col-6">

         <!-- Country -->
         <div id="input">
              <label for='country_id'>Country</label>
              <?php 
              echo $this->Form->select(
                  'country_id',
                  $country_arr,
                  [
                      'id' => 'country_id',
                      'empty' => '-- Select Country --'
                  ]
              );
              ?>
         </div>

         <!-- State -->
         <div id="input">
              <label for='state_id'>State</label>
              <?php 
              echo $this->Form->select(
                  'state_id',
                  [],
                  [
                      'id' => 'state_id',
                      'empty' => '-- Select State --'
                  ]
              );
              ?>
         </div>

         <!-- City -->
         <div id="input">
              <label for='city_id'>City</label>
              <?php 
              echo $this->Form->select(
                  'city_id',
                  [],
                  [
                      'id' => 'city_id',
                      'empty' => '-- Select City --'
                  ]
              );
              ?>
         </div>

     </div>
</div>
<!-- Script -->
<script type="text/javascript">
// Read CSRF Token
var csrfToken = $('meta[name="csrfToken"]').attr('content');
$(document).ready(function(){

     // Country change
     $('#country_id').change(function(){
          var country_id = $('#country_id').val();

          // Empty state and city dropdown
          $('#state_id').find('option').not(':first').remove();
          $('#city_id').find('option').not(':first').remove();

          if(country_id != ''){

               // AJAX request
               $.ajax({
                   url: "<?= $this->Url->build(['controller' => 'Autopopulate','action' => 'getCountryStates']) ?>",
                   type: 'post',
                   data: {country_id: country_id},
                   dataType: 'json',
                   headers:{
                        'X-CSRF-Token': csrfToken
                   },
                   success: function(response){

                        var len = response.length;

                        // Add response data to state dropdown
                        for( var i = 0; i<len; i++){
                             var id = response[i]['id'];
                             var name = response[i]['name'];

                             $("#state_id").append("<option value='"+id+"'>"+name+"</option>");

                        }
                   },
               });
          }

     });

     // State change
     $('#state_id').change(function(){
          var state_id = $('#state_id').val();

          // Empty city dropdown
          $('#city_id').find('option').not(':first').remove();

          if(state_id != ''){

               // AJAX request
               $.ajax({
                    url: "<?= $this->Url->build(['controller' => 'Autopopulate','action' => 'getStateCities']) ?>",
                    type: 'post',
                    data: {state_id: state_id},
                    dataType: 'json',
                    headers:{
                         'X-CSRF-Token': csrfToken
                    },
                    success: function(response){

                         var len = response.length;

                         // Add response data to city dropdown
                         for( var i = 0; i<len; i++){
                              var id = response[i]['id'];
                              var name = response[i]['name'];

                              $("#city_id").append("<option value='"+id+"'>"+name+"</option>");
                         }
                    },
               });
          }

     });

});
</script>

7. Output

View Output

8. Conclusion

In the example, I returned a JSON response from the server and looped on it to add a new item to the dropdown, but you can also return an HTML response and directly append it to the dropdown.

After implementing this on your project if data is not loading in the dropdown then debug it using the browser console and network tab.

You can also checkout this tutorial if you want to know jQuery UI autocomplete using jQuery AJAX in CakePHP 4.

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

How to Auto populate Dropdown using jQuery AJAX in CakePHP 4

Contents

1. Create Table

2. Database Configuration

3. Create Model

4. Create Controller

6. Include jQuery and CSRF token

6. Create Template

7. Output

8. Conclusion

Leave a Comment Cancel reply