CODEWITHSUNDEEP
phpdatabasecodeignitercakephp-2.0
Jens
Jens

Reputation: 111

Use multiple databases and tables across an application in CakePHP

For my job, I've build an web-based application with CodeIgniter. Now I want to rebuild this application in CakePHP, because I think it's a better framework which can do more and has more structure than my current framework.

I've started with the basic tutorial on the CakePHP website to create a small blog application. That works fine. I also combined this with Twitter Bootstrap. Now I'm rebuilding my application.

First I explain what my application does. It displays a list (overview) of data from the database. This database is located on localhost (MySQL) and is called "localdb1" (for this example). The tabel is called "sheets". This table contains data which I can use directly, but it also contains id's that refer to other databases.

For example a field that is called customer, which contains the customer's number. The customer's name is stored in another database (ODBC), located on another host ("remotehost2"). For this example the database is called "remotedb2" and the customernames are stored in the table "customers" in the field "customername".

For 2 days now, I'm struggling with the database system in CakePHP. As of the system uses name convention, it's not al clear for me.

What have I done:

How the result should look like:

I want a list of records from localdb1.sheets, but I don't want to display the customer's number, but I want the customer's name, which i found in the remote database remotedb2.customers and can be found by the customer's number

Can someone tell me how I should do that? The difficulty is, that I don't only want this, but I shall combine more databases and tables located on those 2 hosts. So I need a solution like a plugin (or if it's better a model) that is able to get content from the remotehost, or the localhost from other tables.

Upvotes: 0

Views: 1506

Answers (1)

Jhanvi
Jhanvi

Reputation: 592

I don't know it will be useful to you or not but i am maintaining my multiple databases of cloud with the use of below pattern.I hope it can work for you also...

In your database.php make a new connection object for second database 

public $user = array(
    'datasource' => 'Database/Mysql',
    'persistent' => false,
    'host' => 'remotehost2',
    'login' => 'login_id',
    'password' => 'password',
    'database' => 'remotedb2',
    'prefix' => '',
    'encoding' => 'utf8',
);

after that you can use that connction object using below syntax in your model 

enter var $useDbConfig = 'table_name';

OR

you can maintain connections using below syntax also

 $options = array();
    $db = ConnectionManager::getDataSource('table_name');
    $list = $db->rawQuery('select id, name from table_name;');
    while ($row = $db->fetchRow()) { 
        $options = Set::insert($options, $row["table_name"]["id"], $row["tablle_name"]["name"]);
    }

Upvotes: 2

Related Questions