CakePHP 4 Working with CRUD Application with MySQL

Reading Time: 12 minutes
14,673 Views

Inside this article we will see about Basic CakePHP 4 Working with CRUD Application with MySQL (Create, Read, Update & Delete) Operations.

CRUD is a must-have resource for anyone looking to learn how to create web applications that deal with databases and manipulate data.

CRUD operation is a basic step of any computer programming. CRUD stands for the methods of Create, Read Update and Delete. So Inside this blog article we will see about the complete idea of insert update and delete in CakePHP 4.

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.

Let’s get started about Database & Configuration.


Application Database & It’s Configuration

We need to create a database. Database table is going to store form data. We have created a database in PhpMyAdmin with the name of cakephp4_crud. You can choose your own custom name of database.

Either you can use Manual tool of MySQL or you can create via MySQL command.

CREATE DATABASE cakephp4_crud;

So, now we have a database. We need to create a table to store data. Let’s create a table with the name of tbl_students. This table again we can create via Manual interface of creating table or we can run a command to create it.

Create Table tbl_students

CREATE TABLE IF NOT EXISTS tbl_students (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(120) COLLATE utf8_unicode_ci NOT NULL,
    email varchar(120) COLLATE utf8_unicode_ci NOT NULL,
    phone_no varchar(30) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In table tbl_students we have columns as id, name, email & phone_no.

After creating database and table, now we need to configure and connect database with CakePHP 4 application.

Go to Project setup >> config (directory) >> app_local.php (file). Open this file to connect database.

'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' => 'root',

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

Successfully, we have now configured database connection with CakePHP 4 application.


Settings Application Routes for CRUD Operations

We need to create now application routes from where we can control the database operations like Create, Read, Update & Delete operations.

Go to Project Setup >> config (directory) >> routes.php (file). Open this file. We need to add CRUD routes in this file.

$routes->scope('/', function (RouteBuilder $builder) {

    // Student Routes
    $builder->connect('/add-student', ['controller' => 'Students', 'action' => 'addStudent']);
    $builder->connect('/edit-student/:id', ['controller' => 'Students', 'action' => 'editStudent'], ["pass" => ["id"]]);
    $builder->connect('/delete-student/:id', ['controller' => 'Students', 'action' => 'deleteStudent'], ["pass" => ["id"]]);
    $builder->connect('/list-students', ['controller' => 'Students', 'action' => 'listStudents']);

});

Inside this routes configuration, we can see we have created routes for add, edit, delete & list. Next, we need to create Controller. Because all the actions what we have defined these are from Students Controller.


Application Models & Entity Settings

We need to configure Model & Entity. Model contains the information of Table where we will do crud operations. Entity define the columns for value assignment.

To create a model –

Go to Project Setup >> src (directory) >> Model (directory) >> Table (directory).

Inside Table directory, create a file StudentsTable.php. Inside this file copy and paste this given code.

<?php

namespace App\Model\Table;

use Cake\ORM\Table;

class StudentsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->setTable("tbl_students");
    }
}

Next, we need to set Entity for Table definition about value assignments.

To Create an Entity –

Go to Project Setup >> src (directory) >> Model (directory) >> Entity (directory).

Inside Table directory, create a file Student.php. Inside this file copy and paste this given code.

<?php

namespace App\Model\Entity;

use Cake\ORM\Entity;

class Student extends Entity
{
    protected $_accessible = [
        "name" => true,
        "email" => true,
        "phone_no" => true
    ];
}

Create Controller & CRUD Methods

Controller is the file which controls the application flow. Inside this controller file we will create CRUD methods. Those methods handles create, read, delete & update methods.

To Create a Controller –

Go to Project Setup >> src (directory) >> Controller (directory)

<?php

namespace App\Controller;

use App\Controller\AppController;

class StudentsController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();
       
        $this->loadModel("Students");
    }

    public function addStudent()
    {
        $student = $this->Students->newEmptyEntity();
        if ($this->request->is('post')) {
            $student = $this->Students->patchEntity($student, $this->request->getData());
            if ($this->Students->save($student)) {
                $this->Flash->success(__('The student has been created.'));
                return $this->redirect(['action' => 'listStudents']);
            }
            $this->Flash->error(__('Failed to create student. Please, try again.'));
        }
        $this->set("title", "Add Stduent");
        $this->set(compact("student"));
    }

    public function listStudents()
    {
        $students = $this->Students->find()->toList();
        $this->set("title", "List Student");
        $this->set(compact("students"));
    }

    public function editStudent($id = null)
    {
        $student = $this->Students->get($id, [
            'contain' => [],
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $student = $this->Students->patchEntity($student, $this->request->getData());
            if ($this->Students->save($student)) {
                $this->Flash->success(__('The student data has been updated successfully.'));

                return $this->redirect(['action' => 'listStudents']);
            }
            $this->Flash->error(__('The student could not be updated. Please, try again.'));
        }
        $this->set(compact('student'));
        $this->set("title", "Edit Student");
    }

    public function deleteStudent($id = null)
    {
        $this->request->allowMethod(['post', 'delete']);
        $student = $this->Students->get($id);
        if ($this->Students->delete($student)) {
            $this->Flash->success(__('The student has been deleted.'));
        } else {
            $this->Flash->error(__('The student could not be deleted. Please, try again.'));
        }

        return $this->redirect(['action' => 'listStudents']);
    }
}

Inside above code we can see addStudent, editStudent, deleteStudent, listStudents are the methods which handling CRUD functions. Keep in mind we have declared Model & Entity before creating this controller.

$this->loadModel(“Students”); This is loading Students model to controller.


Creating View for Methods

For crud methods like for add, edit, list we want the application view files. To create view files we will create a folder inside /templates directory.

Folder name – /templates/Students. Students is the name of the controller. We will keep all views inside this folder. Let’s create views for CRUD methods –

  • addStudent – add_student.php
  • editStudent – edit_student.php
  • listStudents – list_students.php

These all views will be created inside /templates/Students/* directory.

<?= $this->Form->create($student, [
  "id" => "frm-add-branch"
]) ?>
<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Name</label>
           <input type="text" required class="form-control" placeholder="Name" name="name">
       </div>
   </div>
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Email</label>
           <input type="email" required class="form-control" placeholder="email" name="email">
       </div>
   </div>
</div>
<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Phone No</label>
           <input type="text" required class="form-control" placeholder="Phone No" name="phone_no">
       </div>
   </div>
</div>


<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- Select multiple-->
       <div class="form-group">
           <button class="btn btn-primary">Submit</button>
       </div>
   </div>
</div>
  <?= $this->Form->end() ?>

List view file –

<table id="tbl-students-list" class="table table-bordered table-striped">
  <thead>
      <tr>
            <td colspan="5" align="right"><a href="<?= $this->Url->build('/add-student/', ['fullBase' => true]) ?>">Add Student</a></td>
       </tr>
      <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Email</th>
          <th>Phone No</th>
          <th>Action</th>
      </tr>
  </thead>
  <tbody>
      <?php
        if (count($students) > 0) {
            foreach ($students as $index => $data) {
        ?>
              <tr>
                  <td><?= $data->id ?></td>
                  <td><?= $data->name ?></td>
                  <td><?= $data->email ?></td>
                  <td><?= $data->phone_no ?></td>
                  <td>
                      <form id="frm-delete-student-<?= $data->id ?>" action="<?= $this->Url->build('/delete-student/' . $data->id, ['fullBase' => false]) ?>" method="post"><input type="hidden" value="<?= $data->id ?>" name="id" /></form>
                      <a href="<?= $this->Url->build('/edit-student/' . $data->id, ['fullBase' => true]) ?>" class="btn btn-warning">Edit</a>
                      <a href="javascript:void(0)" onclick="if(confirm('Are you sure want to delete ?')){ $('#frm-delete-student-<?= $data->id ?>').submit() }" class="btn btn-danger">Delete</a>
                  </td>
              </tr>
      <?php
            }
        }
        ?>
  </tbody>
</table>

Edit view file –

<?= $this->Form->create($student, [
  "id" => "frm-edit-branch"
]) ?>
<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Name</label>
           <input type="text" value="<?= $student->name ?>" required class="form-control" placeholder="Name" name="name">
       </div>
   </div>
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Email</label>
           <input type="email" value="<?= $student->email ?>" required class="form-control" placeholder="email" name="email">
       </div>
   </div>
</div>
<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- text input -->
       <div class="form-group">
           <label>Phone No</label>
           <input type="text" value="<?= $student->phone_no ?>" required class="form-control" placeholder="Phone No" name="phone_no">
       </div>
   </div>
</div>


<div class="row custom-padding">
   <div class="col-sm-6">
       <!-- Select multiple-->
       <div class="form-group">
           <button class="btn btn-primary">Submit</button>
       </div>
   </div>
</div>
  <?= $this->Form->end() ?>

Next, start development server

$ bin/cake server -p 8765

List Page URL: http://localhost:8765/list-students

Add Page URL: http://localhost:8765/add-student

Edit Page URL: http://localhost:8765/edit-student/3

We hope this article helped you to learn about CakePHP 4 Working with CRUD Application with MySQL 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.