Retrieve data from two Databases on single controller in codeigniter

Question

i want to retrieve data from two different databases tables such as job database and classified database in that two databases i have job_history table and ad_mobile_phones table now i want to retrieve the data from these two tables on single controller please suggest me thanks in advance

$active_group = 'default';
$active_record = TRUE;



//for job portal
$db['default']['hostname'] = 'TZ07';
$db['default']['username'] = 'root';
$db['default']['password'] = 'TZ';
$db['default']['database'] = 'doolaly_jobz';

$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


//for classified portal
$db['classified_db']['hostname'] = 'TZ07';
$db['classified_db']['username'] = 'root';
$db['classified_db']['password'] = 'TZ';
$db['classified_db']['database'] = 'doolaly';



$db['classified_db']['dbdriver'] = 'mysql';
$db['classified_db']['dbprefix'] = '';
$db['classified_db']['pconnect'] = TRUE;
$db['classified_db']['db_debug'] = TRUE;
$db['classified_db']['cache_on'] = FALSE;
$db['classified_db']['cachedir'] = '';
$db['classified_db']['char_set'] = 'utf8';
$db['classified_db']['dbcollat'] = 'utf8_general_ci';
$db['classified_db']['swap_pre'] = '';
$db['classified_db']['autoinit'] = TRUE;
$db['classified_db']['stricton'] = FALSE;

Controller

class Account_detail extends CI_Controller 
{
    public function __construct()
    {
        parent::__construct();
        $this->load->model('Jobseekermodel','',TRUE);
        $CI =   &get_instance();
        $this->db = $CI->load->database('classified_db', TRUE);
        //$secound_db= $this->load->database('classified_db',TRUE);
        $this->load->model('Emailmodel','',TRUE);
        $this->load->model('Bookmodel','',TRUE);
        $this->load->model('Consultantmodel','',TRUE);
        $this->load->model('Openingmodel','',TRUE);
        $this->load->model('Commfuncmodel','',TRUE);
        $this->lang->load('message', 'english');
        $this->lang->load('mail', 'english');
        $this->load->library('form_validation');
        if (session_id() == "") session_start();
    }
}

hope CI db connection will be helpful http://ellislab.com/codeigniter/user-guide/database/connecting.html — Girish, May 14 '14 at 06:26
what you tried?, please add in your question, and also error — Girish, May 14 '14 at 06:35
In controller i added this lines $secound_db= $this->load->database('classified_db',TRUE); and now it gives error such as Error Number: 1146 Table 'doolaly.tbl_post' doesn't exist SELECT * FROM (`tbl_post`) ORDER BY `post_name` Filename: C:\wamp\www\DoolalyJobsBackup\system\database\DB_driver.php Line Number: 330 — Omkar Hendre, May 14 '14 at 06:51
did you try `$secound_db` object instead on `$this` like `$secound_db->db->query("SELECT * FROM (tbl_post)");` ? — Girish, May 14 '14 at 06:55

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

make a new connection in your config/database.php

first db (main db):-

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'doolaly_jobz';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

second db :-

$db['db2']['hostname'] = 'localhost';
$db['db2']['username'] = 'root';
$db['db2']['password'] = '';
$db['db2']['database'] = 'doolaly';
$db['db2']['dbdriver'] = 'mysql';
$db['db2']['dbprefix'] = '';
$db['db2']['pconnect'] = FALSE;
$db['db2']['db_debug'] = TRUE;
$db['db2']['cache_on'] = FALSE;
$db['db2']['cachedir'] = '';
$db['db2']['char_set'] = 'utf8';
$db['db2']['dbcollat'] = 'utf8_general_ci';
$db['db2']['swap_pre'] = '';
$db['db2']['autoinit'] = TRUE;
$db['db2']['stricton'] = FALSE;

Then load your second db in controller like

$CI =   &get_instance();
$this->db2 = $CI->load->database('db2', TRUE); //doolaly

and get query like

$this->db2->query('your_query') //doolaly
 $this->db->query('your_query') // doolaly_jobz

For more :- Codeigniter - multiple database connections

edited May 23 '17 at 12:21

Community

1
1

answered May 14 '14 at 06:27

Rakesh Sharma

13,680
5
37
44

sure let me know if you have still any issue – Rakesh Sharma May 14 '14 at 06:59
A Database Error Occurred Error Number: 1146 Table 'doolaly.tbl_post' doesn't exist SELECT * FROM (`tbl_post`) ORDER BY `post_name` Filename: C:\wamp\www\DoolalyJobsBackup\system\database\DB_driver.php Line Number: 330 – Omkar Hendre May 14 '14 at 07:21
is your db name doolaly? show your code of config and controller – Rakesh Sharma May 14 '14 at 07:24
i am not able to put my code it gives error for saving changes in question – Omkar Hendre May 14 '14 at 07:33
my database names are doolaly and doolaly_classified doolaly is database for job – Omkar Hendre May 14 '14 at 07:36
hey friend plz help me yr – Omkar Hendre May 14 '14 at 07:47

score 0 · Answer 2 · answered May 14 '14 at 11:57

you can manually connect to db each time you need or you can autoload it...
set like this in your config.php

$config['hostname'] = 'localhost';
$config['username'] = 'root';
$config['password'] = '';
$config['database'] = 'doolaly';
$config['dbdriver'] = 'mysql';
$config['dbprefix'] = '';
$config['pconnect'] = FALSE;
$config['db_debug'] = TRUE;
$config['cache_on'] = FALSE;
$config['cachedir'] = '';
$config['char_set'] = 'utf8';
$config['dbcollat'] = 'utf8_general_ci';
$config['swap_pre'] = '';
$config['autoinit'] = TRUE;
$config['stricton'] = FALSE;

add the desired fields and connect the db using
$this->load->database($config);

or try this

$dsn = 'dbdriver://username:password@hostname/database';

$this->load->database($dsn);

it will works...

Retrieve data from two Databases on single controller in codeigniter

2 Answers2