Reading Time: 8 minutes

3,146 Views

Inside this article we will see the concept of autocomplete search using jquery ui in CakePHP 4. This autocomplete search will be total dynamic which searches data from database.

You will get the complete concept of CakePHP 4 jQuery UI Autocomplete Database Search. Autocomplete is a jQuery UI plugin feature which gives the flexibility to search value from a list of values.

Article contains classified information about Autocomplete Database Search of jQuery UI in CakePHP. It will help to create this advance feature.

Learn More –

Let’s get started.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE mydatabase;

Successfully, we have created a database.

Database Connection

Open app_local.php file from /config folder. Search for Datasources. Go to default array of it.

You can add your connection details here to connect with your database. It will be like this –

//...

'Datasources' => [
        'default' => [
            'host' => 'localhost',
            /*
             * 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' => 'sample@123',

            'database' => 'mydatabase',
            /*
             * 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),
        ],
  
     //...

//...

You can pass host, username, password and database.

Successfully, you are now connected with the database.

Create Migration

Open project into terminal and run this commands to create migration file.

$ bin/cake bake migration CreateCountries

It will create 20220330143909_CreateCountries.php file inside /config/Migrations folder.

Open migration file and write this following code into it. The code is all about for the schema of countries table.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class CreateCountries extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('countries');

        $table->addColumn("name", "string", [
            "limit" => 120,
            "null" => false
        ]);

        $table->addColumn("sortname", "string", [
            "limit" => 50,
            "null" => false
        ]);

        $table->addColumn("phonecode", "string", [
            "limit" => 20,
            "null" => false
        ]);

        $table->create();
    }
}

Run Migration

Back to terminal and run this command.

$ bin/cake migrations migrate

It will create table “countries” inside database.

Insert Test Data into Table

As we have created a table named as countries. We need some data into this table to test jQuery Select2 plugin.

For data here we have the link of github.

Here, you need to copy mysql query from this given file and run into sql tab of phpmyadmin. First download it and run.

Download Countries Data

You will see something like this after this test data insertion.

“countries” table

Create Controller

Open project into terminal and run this command into it.

$ bin/cake bake controller Search --no-actions

It will create SearchController.php file inside /src/Controller folder. Open controller file and write this code into it.

<?php

declare(strict_types=1);

namespace App\Controller;

use Cake\Datasource\ConnectionManager;

class SearchController extends AppController
{
    private $db;

    public function initialize(): void
    {
        parent::initialize();
        $this->db = ConnectionManager::get("default");
    }

    public function searchForm()
    {
        // open frontend view
    }

    public function searchCountry()
    {
        if ($this->request->is("ajax")) {

            $searchQuery = $this->request->getData("search");

            $countriesList = [];

            if (isset($searchQuery) && !empty($searchQuery)) {

                // Fetch record with search query
                $countries = $this->db->execute("SELECT id, name from countries WHERE name like '%" . $searchQuery . "%'")->fetchAll("assoc");
            } else {

                // Fetch record
                $countries = $this->db->execute("SELECT id, name from countries")->fetchAll("assoc");
            }

            foreach ($countries as $country) {
                $countriesList[] = array(
                    "value" => $country['id'],
                    "label" => $country['name'],
                );
            }

            echo json_encode(array(
                "status" => 1,
                "data" => $countriesList
            ));

            die;
        }
    }
}

Above controller class contains three methods:

initialize() constructor which loads the instance for database connection.
searchForm() which is for frontend layout for autocomplete dropdown view.
searchCountry(), this method will be hit by ajax request when we search into jQuery UI autocomplete dropdown.

Create Template

Create Search folder inside /templates folder. Next, needs to create search_form.php file inside /templates/Search folder.

Open search_form.php file and write this following code into it. This will give the frontend layout for jQuery UI autocomplete view.

<!DOCTYPE html>
<html>

<head>
    <title>Autocomplete Search From Database CakePHP 4 Using jQuery UI</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

    <!-- jQuery UI CSS -->
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

    <!-- jQuery -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

    <!-- jQuery UI JS -->
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
</head>

<body>

    <div class="container" style="margin-top: 40px;">
        <div class="panel panel-primary">
            <div class="panel-heading">Autocomplete Search From Database CakePHP 4 Using jQuery UI</div>
            <div class="panel-body">
                <label>Search Country...</label>
                <input class="form-control" id="autocomplete_country" type="text">
                <br>
                <p>Country ID : <span id="countryId"></span></p>
            </div>
        </div>
    </div>

    <!-- Script -->
    <script type='text/javascript'>
        $(document).ready(function() {
            // Initialize
            $("#autocomplete_country").autocomplete({
                source: function(request, response) {
                    // Fetch data
                    $.ajax({
                        url: "/list-countries",
                        type: 'post',
                        dataType: "json",
                        data: {
                            search: request.term
                        },
                        success: function(data) {
                            response(data.data);
                        }
                    });
                },
                select: function(event, ui) {
                    // Set selection
                    $('#autocomplete_country').val(ui.item.label); // display the selected text
                    $('#countryId').text(ui.item.value); // save selected id to input
                    return false;
                },
                focus: function(event, ui) {
                    $("#autocomplete_country").val(ui.item.label);
                    $("#countryId").text(ui.item.value);
                    return false;
                },
            });
        });
    </script>

</body>

</html>

Disable CSRF Token

When we submit a cakephp form, it needs a CSRF token should be submitted with form submission request.

We are not interested to send CSRF token with form data. To disable it, Open Application.php from /src folder.

Remove these lines of code from middleware() method.

->add(new CsrfProtectionMiddleware([
    'httponly' => true,
]))

Add Route

Open routes.php file from /config folder. Add these routes into it.

//...

$routes->connect(
    '/search-form',
    ['controller' => 'Search', 'action' => 'searchForm']
);

$routes->connect(
    '/list-countries',
    ['controller' => 'Search', 'action' => 'searchCountry']
);

//...

Application Testing

Open terminal and run this command to start development server.

$ bin/cake server

URL: http://localhost:8765/search-form

We hope this article helped you to learn about CakePHP 4 jQuery UI Autocomplete Database Search Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.