How to Create Model and Fetch records in CakePHP 4

Last updated on by

Model are used to interact with the specific database table and perform operations like – retrieving, inserting, updating, and deleting records.

In CakePHP 4 from Model you can restrict field access for insertion/updation. It automatically defines field validation while Model creation according to table structure that you can modify.

In this tutorial, I show how you can create a model and fetch records from the database table and display it on the page in CakePHP 4 project.

How to create Model and fetch records in CakePHP 4

Contents

  1. Create Table
  2. Database Configuration
  3. Create Model
  4. Create Controller
  5. Create Template
  6. Output
  7. Conclusion

1. Create Table

In the example, I am using users table. It has the following structure –

CREATE TABLE `users` (
    `id` int(11) NOT NULL,
    `username` varchar(50) NOT NULL,
    `name` varchar(60) NOT NULL,
    `gender` varchar(10) NOT NULL,
    `email` varchar(60) NOT NULL,
    `city` varchar(80) NOT NULL
)

INSERT INTO `users` (`id`, `username`, `name`, `gender`, `email`, `city`) VALUES
(1, 'yssyogesh', 'Yogesh singh', 'male', 'yogesh@makitweb.com', 'Bhopal'),
(2, 'bsonarika', 'Sonarika Bhadoria', 'female', 'bsonarika@makitweb.com', 'Indore'),
(3, 'sunil', 'Sunil singh', 'male', 'sunil@makitweb.com', 'Pune'),
(4, 'vishal', 'Vishal Sahu', 'male', 'vishal@makitweb.com', 'Bhopal'),
(5, 'jiten', 'jitendra singh', 'male', 'jitendra@makitweb.com', 'Delhi'),
(6, 'shreya', 'Shreya joshi', 'female', 'shreya@makitweb.com', 'Indore'),
(7, 'abhilash', 'Abhilash namdev', 'male', 'abhilash@makitweb.com', 'Pune'),
(8, 'ekta', 'Ekta patidar', 'female', 'ekta@makitweb.com', 'Bhopal'),
(9, 'deepak', 'Deepak singh', 'male', 'deepak@makitweb.com', 'Delhi'),
(10, 'rohit', 'Rohit Kumar', 'male', 'rohit@makitweb.com', 'Bhopal');

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 Users Model.
bin/cake bake model Users
  • This will create 2 files –
src/Model/Entity/User.php
src/Model/Table/UsersTable.php
  • I didn’t modify this file’s values.

NOTE – Field names and validation automatically defined according to your table structure in the above created files.

src/Model/Entity/User.php

In this Entity class specify field names that you want to allow insertion and updation. You can either remove the field name or set it to false if you don’t want to allow.

Completed Code

<?php
declare(strict_types=1);

namespace App\Model\Entity;

use Cake\ORM\Entity;
class User extends Entity
{

     protected $_accessible = [
          'username' => true,
          'name' => true,
          'gender' => true,
          'email' => true,
          'city' => true,
     ];
}

src/Model/Table/UsersTable.php

This Table class tells ORM which table needs to use and defines validation rules for the fields.

Completed Code

<?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 UsersTable extends Table
{

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

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

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

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

            $validator
                ->scalar('gender')
                ->maxLength('gender', 10)
                ->requirePresence('gender', 'create')
                ->notEmptyString('gender');

            $validator
                ->email('email')
                ->requirePresence('email', 'create')
                ->notEmptyString('email');

            $validator
                ->scalar('city')
                ->maxLength('city', 80)
                ->requirePresence('city', 'create')
                ->notEmptyString('city');

            return $validator;
      }

      public function buildRules(RulesChecker $rules): RulesChecker
      {
            $rules->add($rules->isUnique(['username']), ['errorField' => 'username']);
            $rules->add($rules->isUnique(['email']), ['errorField' => 'email']);

            return $rules;
      }
}

4. Create Controller

  • Create a UsersController.php file in src/Controller/ folder.
  • Create UsersController Class that extends AppController.
  • Create index() method –
    • Check if search is POST or not. If POST then assign POST value to $search variable.

Fetch records –

The created Model is available on the controller don’t require to include for access. To call the Users Model create an object of Users Table class using getTableLocator()->get(). Pass Table class name in get(). Use the created object to fetch records.

If $search value is empty then fetch all records from the users table. For this, you can use find('all') to fetch all records with all fields – $users->find('all'). If you want to select some field while fetching then specify it in select([ 'username','name','email','city']).

If $search value is not empty then fetch matching records from the users table. Using conditions to specify multiple OR conditions for searching on the fields.

With $this->set() pass $usersList to the template.

Completed Code

<?php
declare(strict_types=1);

namespace App\Controller;

class UsersController extends AppController
{

      public function index(){
           $search = '';

           if(isset($this->request->getData()['search'])){
                 $search = $this->request->getData()['search'];
           }

           if($search != ''){
                 // Select matching records
                 $users = $this->getTableLocator()->get('Users');
                 $query = $users->find('all',[
                      'conditions' => [
                            'OR' => [
                                  'users.username LIKE' => '%'.$search.'%',
                                  'users.name LIKE' => '%'.$search.'%',
                                  'users.email LIKE' => '%'.$search.'%',
                                  'users.city LIKE' => '%'.$search.'%',
                            ]
                      ]
                 ])->order(['id' => 'ASC']);
                 $usersList = $query->toArray();

           }else{
                 // Fetch all records
                 $users = $this->getTableLocator()->get('Users');
                 // $query = $users->find('all')->order(['id' => 'ASC']);
                 $query = $users->find('all')
                       ->select(['id', 'username','name','email','city','gender'])
                       ->order(['id' => 'ASC']);
                 $usersList = $query->toArray();
           }

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

}

5. Create Template

Create a new Users folder in templates/ folder. Now in the Users folder create index.php file (templates/Users/index.php).

Create a <form >. Set action to users/index. In the <form > create a textbox for searching and a submit button.

Using <table > to list fetched records. Loop on $usersList Array to read values and create a new <tr> <td>.

Completed Code

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

         <?php 
         // Search form
         echo $this->Form->create(NULL,array('url'=>['controller' => 'users','action' => 'index'] ));
         echo $this->Form->control('search',['id'=>'search','class' => 'form-control']);
         echo $this->Form->button('Submit');
         echo $this->Form->end();

         ?>

         <!-- Users list -->
         <table>
             <thead>
                 <tr>
                     <th>ID</th>
                     <th>Username</th>
                     <th>Name</th>
                     <th>Gender</th>
                     <th>Email</th>
                     <th>City</th>
                 </tr>
             </thead>
             <tbody>
             <?php foreach ($usersList as $user): ?>
                 <tr>
                     <td><?= $user['id'] ?></td>
                     <td><?= $user['username'] ?></td>
                     <td><?= $user['name'] ?></td>
                     <td><?= $user['gender'] ?></td>
                     <td><?= $user['email'] ?></td>
                     <td><?= $user['city'] ?></td>
                 </tr>
             <?php endforeach; ?>
             </tbody>
         </table>

     </div>
</div>

6. Output

View Output

7. Conclusion

First create your table in the database then execute the Model creation command otherwise, Model will not be created.

You can learn more about query building in CakePHP 4 here.

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

Leave a Comment

© 2016-2024 Makitweb, All rights reserved