CakePHP 4 Find Database Table Rows Using Model

Inside this tutorial we will see CakePHP 4 Find Database Table Rows Using Model. We will use the concept of Model of CakePHP to select rows. Also we will see selection based on conditional statements. Article contains classified information about this data selection concept.

There are several ways to select data from database table – Using Query Builder, Using Model & Entity, Connection Manager.

• Select all rows.
• Select single row.
• Select single row using condition.
• Select single row using primary key ID.

Suppose we have a table called products into database. Table products have these columns – ( id, name, cost, description, status ).

Learn More –

• CakePHP 4 Migrations Rollback in Non Shell Environment
• CakePHP 4 Multiple Database Using ConnectionManager
• CakePHP 4 Passing Parameters to Routes and Actions
• CakePHP 4 Insert Table Row Using Model And Entity

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.

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),
        ],
  
     //...

//...

//...

​

'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 Model & Entity

Open project into terminal and run this bake console command –

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

It will create several files like Model, Entity, Test & Fixture.

Model class file ProductsTable.php is inside /src/Model/Table folder. Entity class file Product.php is inside /src/Model/Entity folder.

Code for Model class file.

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class ProductsTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('products');
        $this->setPrimaryKey('id');
    }
}

<?php

declare(strict_types=1);

​

namespace App\Model\Table;

​

use Cake\ORM\Table;

​

class ProductsTable extends Table

{

    /**

     * Initialize method

     *

     * @param array $config The configuration for the Table.

     * @return void

     */

    public function initialize(array $config): void

    {

        parent::initialize($config);

​

        $this->setTable('products');

        $this->setPrimaryKey('id');

    }

}

​

Code for Entity class file.

<?php
declare(strict_types=1);

namespace App\Model\Entity;

use Cake\ORM\Entity;

/**
 * Product Entity
 *
 * @property int $id
 */
class Product extends Entity
{
    protected $_accessible = [
        'name' => true,
        'cost' => true,
        'description' => true,
        'status' => true,
    ];
}

<?php

declare(strict_types=1);

​

namespace App\Model\Entity;

​

use Cake\ORM\Entity;

​

/**

 * Product Entity

 *

 * @property int $id

 */

class Product extends Entity

{

    protected $_accessible = [

        'name' => true,

        'cost' => true,

        'description' => true,

        'status' => true,

    ];

}

​

Create Controller

To create controller we will use bake console command –

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

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

Next,

To use model into controller we need to load it first.

$this->loadModel("Products");

Let’s see the complete code of controller –

<?php

declare(strict_types=1);

namespace App\Controller;

class SiteController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();

        $this->loadModel("Products");
        $this->autoRender = false;
    }

    // Get all data
    public function getProducts()
    {
        $products = $this->Products
            ->find()
            ->toList();

        print_r($products);
    }

    // Get single data row
    public function getSingleProduct()
    {
        $product = $this->Products
            ->find()
            ->order(["id" => "desc"])
            ->first();

        print_r($product);
    }

    // Get data row conditionally
    public function getProductsByCondition()
    {
        $products = $this->Products
            ->find()
            ->where(["cost >=" => 800])
            ->toList();

        print_r($products);
    }

    // Get product data row by primary key
    public function getProductById()
    {
        $product = $this->Products
            ->get(13); // 13 is product id which is a primary key

        print_r($product);
    }
}

<?php

​

declare(strict_types=1);

​

namespace App\Controller;

​

class SiteController extends AppController

{

    public function initialize(): void

    {

        parent::initialize();

​

        $this->loadModel("Products");

        $this->autoRender = false;

    }

​

    // Get all data

    public function getProducts()

    {

        $products = $this->Products

            ->find()

            ->toList();

​

        print_r($products);

    }

​

    // Get single data row

    public function getSingleProduct()

    {

        $product = $this->Products

            ->find()

            ->order(["id" => "desc"])

            ->first();

​

        print_r($product);

    }

​

    // Get data row conditionally

    public function getProductsByCondition()

    {

        $products = $this->Products

            ->find()

            ->where(["cost >=" => 800])

            ->toList();

​

        print_r($products);

    }

​

    // Get product data row by primary key

    public function getProductById()

    {

        $product = $this->Products

            ->get(13); // 13 is product id which is a primary key

​

        print_r($product);

    }

}

​

Above is the complete code where we can understand the concept of data selection.

Concept #1 All rows selection

// Get all data
public function getProducts()
{
    $products = $this->Products
        ->find()
        ->toList();

    print_r($products);
}

Concept #2 Single row selection

// Get single data row
public function getSingleProduct()
{
    $product = $this->Products
        ->find()
        ->order(["id" => "desc"])
        ->first();

    print_r($product);
}

Concept #3 Get data row by condition

// Get single data row conditionally
public function getProductsByCondition()
{
    $products = $this->Products
        ->find()
        ->where(["cost >=" => 800])
        ->toList();

    print_r($products);
}

Concept #4 Get data row by Primary Key ID

// Get product data row by primary key
public function getProductById()
{
    $product = $this->Products
        ->get(13); // 13 is product id which is a primary key

    print_r($product);
}

Create Routes

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

//...

// Product Route
$routes->connect(
    '/products',
    ['controller' => 'Site', 'action' => 'getProducts']
);

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

$routes->connect(
    '/find-single-product',
    ['controller' => 'Site', 'action' => 'getProductsByCondition']
);

$routes->connect(
    '/single-product-by-id',
    ['controller' => 'Site', 'action' => 'getProductById']
);

//...

//...

​

// Product Route

$routes->connect(

    '/products',

    ['controller' => 'Site', 'action' => 'getProducts']

);

​

$routes->connect(

    '/single-product',

    ['controller' => 'Site', 'action' => 'getSingleProduct']

);

​

$routes->connect(

    '/find-single-product',

    ['controller' => 'Site', 'action' => 'getProductsByCondition']

);

​

$routes->connect(

    '/single-product-by-id',

    ['controller' => 'Site', 'action' => 'getProductById']

);

​

//...

​

URLs to access

• All products: http://localhost:8765/products
• Single product: http://localhost:8765/single-product
• Single product by condition: http://localhost:8765/find-single-product
• Single product by primary key: http://localhost:8765/single-product-by-id

So, here we can see we have these above options by which we can select rows from database table. This is only using model based concept. Using Query builder & Connection manager are different way than this.

We hope this article helped you to learn about CakePHP 4 Find Database Table Rows Using Model Tutorial in a very detailed way.

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.