CakePHP 4 Database Operations Using ConnectionManager

Reading Time: 5 minutes
2,181 Views

Inside this tutorial we will see CakePHP 4 Database Operations Using ConnectionManager. We will use the concept of ConnectionManager of CakePHP to create a database connection. The created instance will perform all operations like insert, update, delete and select. Article contains classified information about this concept.

There are several ways to perform database operations – Using Query Builder, Using Model & Entity, ConnectionManager.

Using ConnectionManager you can connect and create instance of database with any group.

Suppose we have a table called products into database. Table products have these columns – ( id, name, cost, description, status ). We will create database connection manager instance and perform insert, update, delete and select operations.

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.

Connection Manager Instance

ConnectionManager is a class in CakePHP. We use get() method to get the instance of database group. The ConnectionManager class acts as a registry to access database connections your application has. It provides a place that other objects can get references to existing connections.

use Cake\Datasource\ConnectionManager;

To create database instance using Connection Manager, we use like this –

Connection with default group of database.

$object = ConnectionManager::get("default");

Connection with otherDb group of database.

$object = ConnectionManager::get("otherDb");

We will use the returned connection object to perform insert, update, delete and select operations.

How to connect with more than one database in CakePHP application, click here.

Create Controller

To create controller we will use bake console command –

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

It will create a file SitesController.php inside /src/Controller folder.

<?php

declare(strict_types=1);

namespace App\Controller;

use Cake\Datasource\ConnectionManager;

class SitesController extends AppController
{
    private $db;

    public function initialize(): void
    {
        parent::initialize();
        // Connection to default database group
        $this->db = ConnectionManager::get("default");
        $this->autoRender = false;
    }

    // Add row using connection manager instance
    public function addProduct()
    {
        $this->db->insert("products", [
            "name" => "Sample Product",
            "cost" => 800,
            "description" => "Sample product content",
            "status" => 1
        ]);
    }

    // Update row using connection manager instance
    public function updateProduct()
    {
        $product_id = 105;

        $this->db->update("products", [
            "name" => "Sample Product updated",
            "cost" => 100
        ], [
            "id" => $product_id
        ]);
    }

    // Delete row using connection manager instance
    public function deleteProduct()
    {
        $product_id = 105;

        $this->db->delete("products", [
            "id" => $product_id
        ]);
    }

    // Select single row using connection manager instance
    public function selectProduct()
    {
        $product_id = 99;

        $results = $this->db
            ->newQuery()
            ->select('*')
            ->from('products')
            ->where([
                "id" => $product_id
            ])
            ->execute()
            ->fetch('assoc');

        print_r($results);
    }

    // Select all rows using connection manager instance
    public function selectAllProduct()
    {
        $results = $this->db
            ->newQuery()
            ->select('*')
            ->from('products')
            ->execute()
            ->fetchAll('assoc');

        print_r($results);
    }
}

In the above code we can see all database operations. We can use this concept to create CRUD application in CakePHP.

Create Routes

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

//...

// Product Route
$routes->connect(
    '/add-product',
    ['controller' => 'Sites', 'action' => 'addProduct']
);

$routes->connect(
    '/edit-product',
    ['controller' => 'Sites', 'action' => 'updateProduct']
);

$routes->connect(
    '/delete-product',
    ['controller' => 'Sites', 'action' => 'deleteProduct']
);

$routes->connect(
    '/single-product',
    ['controller' => 'Sites', 'action' => 'selectProduct']
);

$routes->connect(
    '/products',
    ['controller' => 'Sites', 'action' => 'selectAllProduct']
);

//...

Application Testing

Open project and run this command to start development server.

$ bin/cake server

URL: http://localhost:8765/

  • Insert: http://localhost:8765/add-product
  • Update: http://localhost:8765/edit-product
  • Delete: http://localhost:8765/delete-product
  • Single Product: http://localhost:8765/single-product
  • All Products: http://localhost:8765/products

We hope this article helped you to learn about CakePHP 4 Database Operations Using ConnectionManager 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.