CakePHP 4 Query Builder CRUD Functions Tutorial

Reading Time: 6 minutes
2,242 Views

Inside this article we will see the concept of CakePHP 4 Query Builder CRUD functions. In this we will cover Insert, Update, Select, Delete functions of CakePHP ORM’s Builder. Article contains classified information about the concept.

CakePHP 4 provides several ways to run database queries like ORM Query Builder, By using ConnectionManager Instance, By Model & Entity. In this we will cover Query Builder methods.

CRUD means Create, Read, Update & Delete. In this we will discuss all methods of ORM to perform a complete set of crud functions.

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 CreateBlogs

It will create 20220330143909_CreateBlogs.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 blogs table.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

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

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

        $table->addColumn("description", "text", [
            "null" => false
        ]);

        $table->create();
    }
}

Run Migration

Back to terminal and run this command.

$ bin/cake migrations migrate

It will create table “blogs” inside database.

Create Table Locator Class

Here, we need to create table class. Model is termed as table class. For blogs table we should BlogsTable.php class.

Let’s create it.

We will create model and entity. Back to terminal and run this command.

$ bin/cake bake model Blogs --no-validation --no-rules

It will create model file BlogsTable.php inside /src/Model/Table folder. Also we should see the entity file Blog.php inside /src/Model/Entity folder.

Create Controller

Open project into terminal and run this command into it.

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

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

<?php

declare(strict_types=1);

namespace App\Controller;

class SiteController extends AppController
{
    private $blogObject;
    public function initialize(): void
    {
        parent::initialize();
        $this->autoRender = false;

        $this->blogObject = $this->getTableLocator()->get('Blogs'); // Loading Table Class
    }
    // Get all blogs
    public function getAllBlogs()
    {
        $query = $this->blogObject->find();

        $blogs = $query->all();

        foreach ($blogs as $article) {
            echo $article->title;
        }
    }

    // Get single blog data
    public function singleBlogData()
    {
        $blog_id = 5;

        $query = $this->blogObject->find()->where(['id' => $blog_id]);

        $singleBlog = $query->first();
        
        print_r($singleBlog);
    }

    // Insert Blog
    public function insertBlog()
    {
        $query = $this->blogObject->query();

        $query->insert(['title', 'description'])
            ->values([
                'title' => 'First post',
                'description' => 'Some body text'
            ])
            ->execute();
    }

    // Update Blog
    public function updateBlog()
    {
        $query = $this->blogObject->query();

        $blog_id = 5;

        $query->update()
            ->set(['title' => "Updated value"])
            ->where(['id' => $blog_id])
            ->execute();
    }

    // Delete blog
    public function deleteBlog()
    {
        $query = $this->blogObject->query();

        $blog_id = 5;

        $query->delete()
            ->where(['id' => $blog_id])
            ->execute();
    }
}

Above controller class contains all methods for CRUD operations like of Insert, Update, Delete, Select etc.

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(
    '/insert-blog',
    ['controller' => 'Site', 'action' => 'insertBlog']
);

$routes->connect(
    '/update-blog',
    ['controller' => 'Site', 'action' => 'updateBlog']
);

$routes->connect(
    '/all-blogs',
    ['controller' => 'Site', 'action' => 'getAllBlogs']
);

$routes->connect(
    '/single-blog',
    ['controller' => 'Site', 'action' => 'singleBlogData']
);

$routes->connect(
    '/delete-blog',
    ['controller' => 'Site', 'action' => 'deleteBlog']
);

//...

Application Testing

Open terminal and run this command to start development server.

$ bin/cake server

CRUD URL’s

  • Create Blog: http://localhost:8765/insert-blog
  • Update Blog: http://localhost:8765/update-blog (You can specify your dynamic Blog ID)
  • Delete Blog: http://localhost:8765/delete-blog (You can specify your dynamic Blog ID)
  • All Blogs: http://localhost:8765/all-blogs
  • Single Blog: http://localhost:8765/single-blog (You can specify your dynamic Blog ID)

We hope this article helped you to learn about CakePHP 4 Query Builder CRUD Functions 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.