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.
Contents
- 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 insrc/Controller/
folder. - Create
AutopopulateController
Class that extendsAppController
.
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
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.