5

I want to connect to a second (remote) database using CakePHP 3. I have found solutions online that suggest how to associate different models with different databases but that is not what I need to achieve.

I need to be able to connect to a remote database (that is not associated with any model) and read/write some records from an action in my controller. Can this be achieved using CakePHP?

Edit (more information):

I have a website that acts as a booking platform for hotel rooms. The availability of these rooms can be controlled via my website and stored in my database. But for some clients I want to be able to connect to their private database directly and use their records to check availability.

Cellydy
  • 1,365
  • 3
  • 15
  • 27
  • If you need to write in a table in a database you might as well create a Model for it... I don't understand the "that is not associated with any model" part. It may not be part of your main data structure but it is still structured data. – JvO Mar 14 '17 at 12:04
  • @JvO Thanks for your response! Here is the thing. I have a website that acts as a booking platform for hotel rooms. The availability of these rooms can be controlled via the website and stored in my database. But for some clients I want to be able to connect to their private database directly and use their records to check availability. Would I need to create a custom model for each client? – Cellydy Mar 14 '17 at 12:18
  • Don't these clients offer an api you can access? Why would you be querying the database directly? Just curious – yBrodsky Mar 14 '17 at 13:14
  • That, or at least one model per type of booking software (I hope that some clients at least use the same software package). This is also necessary since I assume the exact query will vary a lot between systems. – JvO Mar 14 '17 at 13:18
  • @yBrodsky Unfortunately they don't and I don't have the time to create a unique API for each of my clients. Hence the direct connection. Not ideal but the only solution I could come up with. – Cellydy Mar 14 '17 at 13:24
  • @JvO Ok, thanks a lot! – Cellydy Mar 14 '17 at 13:27
  • Possible duplicate of [Multiple database connection in cakephp 3](https://stackoverflow.com/questions/29981890/multiple-database-connection-in-cakephp-3) – monsur.hoq Nov 26 '17 at 12:48

1 Answers1

9

Follow the Below Instructions

Step 1 : Open config/app.php Find Datasources array and add Remote Database configuration like as -

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',
        'port' => '3306',
        'username' => 'YOUR_DB_USER',
        'password' => 'YOUR_DB_PASS',
        'database' => 'YOUR_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],
    'remote_db_1' => [ /*Remote Database 1*/
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '192.168.1.47', /*YOUR_REMOTE_SERVER_IP*/
        'port' => '3306',
        'username' => 'REMOTE_DB_USER',
        'password' => 'REMOTE_DB_PASS',
        'database' => 'REMOTE_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],
   'remote_db_2' => [ /*Remote Database 2*/
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '192.168.1.47', /*YOUR_REMOTE_SERVER_IP*/
        'port' => '3306',
        'username' => 'REMOTE_DB_USER',
        'password' => 'REMOTE_DB_PASS',
        'database' => 'REMOTE_DB_NAME',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => false,
        'log' => false,
        'quoteIdentifiers' => false,
        'url' => env('DATABASE_URL', null),
    ],

Step 2 : Now you can access Remote database in your controller like as-

use Cake\Datasource\ConnectionManager;
use \PDO;

class YourController extends AppController{
    public function getRemoteData(){
      $conn1 = ConnectionManager::get('remote_db_1'); #Remote Database 1
      $conn2 = ConnectionManager::get('remote_db_2'); #Remote Database 2
    }
}

Note: Now you can use PDO methods to Insert,Retrieve,Update

Example :

class YourController extends AppController{
    public function getRemoteData(){
      $conn1 = ConnectionManager::get('remote_db_1'); #Remote Database 1
      $sql   = "SELECT * FROM  users";
      $query = $conn1->prepare($sql);
      $query->execute();
      $result = $query->fetchAll(); #Here is the result
    }
}
Sumon Sarker
  • 2,707
  • 1
  • 23
  • 36