CakePHP Connection Manager

CakePHP supports the following relational database servers:

· MySQL 5.5+

· SQLite 3

· PostgreSQL 8.3+

· SQLServer 2008+

· Oracle (through a community plugin)

If you want to use MySQL database then follow these syntaxes in controller.php file for establishing the database connection.

useCake\Datasource\ConnectionManager;// this will be defined in controller
$dsn = 'mysql://root:password@localhost/my_database';    
ConnectionManager::config('default', ['url' => $dsn] 

We can also use phpMyadmin for creating the database, but in this case, we don’t need to write the last two lines as shown above.

Note: We have to create our database’s table name in plural form like employees, users, tutorials are all table names. But in case of a model data table, then we have to use the database table name in singular.

In this topic, firstly, we will create a database by using phpMyAdmin and then create a table by following these steps.

Step1. Firstly, you have to go to your localhost server then click on the phpMyAdmin button.

CakePHP Connection Manager

Step2. Click on the phpMyAdmin button in your bottom-left, and then it will open a window for the database.

CakePHP Connection Manager 1

Step3. Click on the create database link then.

CakePHP Connection Manager 2

Step4. Click on the table and write the table name in plural form like onlines, datas, and articles. Also, you have to define the number of fields that you want to create in your table.

CakePHP Connection Manager 3

After, this write the fields name in table.

CakePHP Connection Manager 4

Now our database has been ready for performing the CRUD operations.

CREATE READ UPDATE & DELETE

We can perform the CRUD (create, read, update and delete) operation by defining any of the following classes in CakePHP Controller which has given below.  

  1. ConnectionManager: It is used as a class in the controller for providing the database instances by executing the queries.
  2. TableRegistry: It is also a class that defines the table name along with the database in the ConnectionManager for fetching the records.
  3. Table: It is a model table that used for manipulating or retrieving the table records in the database by defining the table name in the model folder of CakePHP. In this table, you have to use table as a suffix in the table folder.

ConnectionManager

  1. ConnectionManager is a class through which we can perform CRUD (Create, read, update, and delete) operation in database values by defining the connection in config/app.php. In connection manager, we have to define the default database name during the configuration of the database to the cakephp. And in that database, we have to create a table for storing the data value. So, we have to define just database name as default in the connection manager, and then it automatically detects the table name.

In this category of Connection Manager, you must define a connection to a controller to establish a link with the database and also get the value.

use Cake\Datasource\ConnectionManager;

Once we have created ConnectionManager with our database, we can access the connection by creating the object to start.

$connection =ConnectionManager::get('default');

CREATE

It is a method of connection manager through which we can add values in our database.

Here is the representation of the index method.

$this->connection->insert("Connections", arr_dec[]);

The Insert method contains two parameters where the first parameter encompasses the database name, and the second parameter holds the value in array format, which we send in our database.

Like:                             

Connection->insert( “Connections”, [ “name” => “sumit”, “email” => “[email protected]”]);

This is the example of an insert method through which we can pass the values in the database.

Create a UsersController.php file at src/controller/UsersController.php. Copy the following code to the controller file.

 <?php
  namespace App\Controller;
  use App\Controller\AppController;
  use Cake\Datasource\ConnectionManager;
   class UsersController extends AppController
  {
  public $connection;
  public function initialize()
  {
  parent::initialize();
  $this->connection = ConnectionManager::get('default');
  } 
  public function create()
  {
  $data= $this->connection->insert("Connections",[
   "name" => "sumit",
   "email" => "[email protected]",
   "phone" => "1234567890"
   ]); 
    $this->connection->insert("Connections" ,[
   "name" => "Angle",
    "email" => "[email protected]",
   "phone" => "123489085"
   ]);
  if($this->request->is("post")){
  $data = $this->request->data;
  print_r($data);
  }
  $this->Flash->set('You have successfully registered',
   [ 'element' => 'success']);
  } } 
 ?>   

You have to generate an action file name create.ctp file in  src/Template/Users/create.ctp file and write this statement

“your record is save “ 

Now you can run your program in local server: localhost/my_app_name/users/create. It will show you this output in your screen:

CakePHP Connection Manager 5

You can also check the values ??in your database where your value is stored and it can look like this:

CakePHP Connection Manager 6

Similarly, you can add more entries in your database by passing values ??from your controller.                                        

Update method

As the name represents update, that means we have to update the pre-existing record in our database. This method is based on condition, and if you want to change in your database values, then you have to define some conditions for updating the record.

Here is the syntax for updating method:               

$this->connection->update ("Connections",["name" => "sumit"], [ "id" => "1"]); 

In this method, there are three arguments which as follows:

  1. The first argument represents the database table name.
  2. The second arguments define the key and its value, which you want to overwrite in pre-existing values.
  3. The last argument defines the condition, which is based on the primary key.

Example: This is the example of an update method.

Create a UsersController.php file at src/controller/UsersController.php. Copy the following code to the controller file.              

 <?php
  namespace App\Controller;
  use App\Controller\AppController;
  use Cake\Datasource\ConnectionManager;
  class UsersController extends AppController
  {
  public $connection;
  public function initialize()
  {
  parent::initialize();
  $this->connection = ConnectionManager::get('default');
  } 
 public function updatedata() {
  $data = $this->connection->update ("Connections", [ "name" => "Arun" , ”email”= “[email protected]”
   [ "id" => "2" ]);
  if($this->request->is("post")) {
   $data = $this->request->data;
  print_r($data);
  }
  $this->Flash->set ('Your data is successfully updated',
  [ 'element' => 'success']);
  } 
 }
 ?> 

You have to generate an action file name updatedata.ctp file in  src/Template/Users/updatedata.ctp file and write this statement.

Your data has been updated.

Now you can run your program in local server: localhost/my_app_name/users/updatedata. It will show you this output in your screen:

CakePHP Connection Manager 7

And you can see here that before the update method, there are no changes found in the database, but when you provide some condition, then the value will be changed. Also, you can see the given below image.

CakePHP Connection Manager 8

Delete Method

The delete method is also the same as the update method. In the update method, you have changed the value of your existing database records. And in case of a delete method, you can delete the particular element from your database table by setting the condition in the controller. Delete function has three parameters

  1. Table name of the database
  2. Which parameters do you want to delete?
  3. And the last is the unique id as a primary key through which you can delete the value.
$this->connection->delete("tablename",[ "name" => "delete variable"], [ “id” => “2”]); 

Here is the example of delete method: 

Create a UsersController.php file at src/controller/UsersController.php. Copy the following code to the controller file.

 <?php
 namespace App\Controller;
 use App\Controller\AppController;
  use Cake\Datasource\ConnectionManager;
  class UsersController extends AppController
  {
  public $connection;
  public function initialize()
  {
  parent::initialize();
  $this->connection = ConnectionManager::get('default');
  } 
  public function deletedata()
  { 
  $this->connection->delete("Connections",
  [ "id" => "1" ]);
  } 
 }
 ?> 

You have to generate an action file name deletedata.ctp file in  src/Template/Users/deletedata.ctp file and write this statement.

Successful!!.

Now you can run your program in local server: localhost/my_app_name/users/create. It will show you this output in your screen:

CakePHP Connection Manager 9

You can also check in your database table like the given below image in which id number 1 has been deleted.

CakePHP Connection Manager 10

Select Data  

You can get all the records from the database table using the select() method in the connection manager.

 public function selectdata() {
      $data = $this->connection->execute("select * from tablename")->fetchALL();
                  print_r($data);
                              } 

You can also display your data record using the syntax given below.

$datas =
$this->connection->execute("select * from Connections")->fetchALL("assoc");

Or we can define this way-

Create a UsersController.php file at src/controller/UsersController.php. Copy the following code to the controller file.                                                            

 <?php
 namespace App\Controller;
 use App\Controller\AppController;
  use Cake\Datasource\ConnectionManager;
  class UsersController extends AppController
  {
  public $connection;
  public function initialize()
  {
  parent::initialize();
  $this->connection = ConnectionManager::get('default');
  } 
  public function selectdata()
   { 
 $datas = $this->connection->execute ("select * from Connections")->fetchALL("assoc"); 
  $this->Flash->set ('Here is your data', [ 'element' => 'success']);
   foreach ($datas as $data)
  {  
  echo $data['name'].",".$data['phone']."<br/>";
   }
  }
 }
 ?> 

You have to generate an action file name selectdata.ctp file in src/Template/Users/selectdata.ctp file and write this statement.

You can see the record in table.

Now you can run your program in local server: localhost/my_app_name/users/selectdata. It will show you this output in your screen:

CakePHP Connection Manager 11