Reading Time: 4 minutes

3,626 Views

Inside this article we will see the concept i.e How To Run Raw Queries in CakePHP 4. Article contains classified information about executing raw queries of MySQL. This complete set of instructions will guide the detailed concept of running queries in raw format.

In CakePHP application, we have multiple options available to run MySQL queries like using ConnectionManager, Query Builder, Model & Entity, etc. In these methods we have predefined methods of CakePHP and it’s database classes which auto runs application queries and returns the result set.

Execution of Raw queries in application means we need to put complete query and then run. We need this approach in case of MySQL complex queries, running stored procedure, etc.

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.

How To Execute Raw Queries

In CakePHP 4 we have ConnectionManager class. Using this we can execute raw queries.

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.

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

Execute Raw Query

If we want to fetch data in bulk then this will be format. It will return results set in array format.

$db->execute("YOUR RAW QUERY")->fetchAll('assoc');

In case of single row value, we will use this

$db->execute("YOUR RAW QUERY")->fetch('assoc');

Examples of Raw Queries

Here, we will assume few cases and see how to run queries in raw format in CakePHP.

Example #1

Suppose we have stored procedures in MySQL database. We need to call and run stored procedure using CakePHP method.

$results = $this->db->execute("CALL getBlogs()")->fetchAll('assoc');

echo "<pre>";
print_r($results);

Example #2

Run raw query to select all rows from a table where name column rows contains a substring like jay value.

$results = $this->db->execute("SELECT * from employees WHERE name LIKE '%jay%'")->fetchAll('assoc');

echo "<pre>";
print_r($results);

Example #3

Run raw query to select all rows from a table where id column value will be either any of these 3, 5, 8, 9 or name column value contains a substring esh as last characters.

$results = $this->db->execute("SELECT * from employees WHERE id in (3, 5, 8, 9) name LIKE '%esh'")->fetchAll('assoc');

echo "<pre>";
print_r($results);

We hope this article helped you to learn about How To Run Raw Queries in CakePHP 4 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.