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 –
- CakePHP 4 What is Accessor & Why Application Uses?
- CakePHP 4 What is Mutator & Why Application Uses?
- Call MySQL Stored Procedure in CakePHP 4 Tutorial
- How To Check CakePHP Version in a CakePHP Project
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.
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.
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.
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.
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);
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);
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.