Multiple Database Connections

[eluser]bradya[/eluser]
Hi Everyone,

I'm relatively new to code igniter, and am having some issues with connecting to multiple databases. I've read the examples and instructions in the userguide, but am still stuck on how to properly alternate between the two.

To give you an overview:

I have 2 databases defined in database.php, one called $db['users'] and one called $db['events'].

Both work for their seperate functions when I have $active_group= set to either group.

However this seems to override any DB connections I declare myself. For example, in my Events model I have defined the DB in this fucntion:

Code:

function Event_model()
{
    parent::Model();
    $this->load->database('events');
}

Then, for example, if I try to run a query to get all the venues listed in the events table, I get back an SQL error saying "Table 'users.venue' doesn't exist", since it is obviously still trying to connect to the users DB.

So, can anyone help me out as to what exaclty I'm doing wrong. My feeling is that it has something to do with the $active_group. Do I need to set $active_group?

Any help would be greatly appreciated.

[eluser]adamBouchard[/eluser]
Hi,

I ran into a similar issue, and found through another post that you need to probably change one of your mysql_pconnect settings to FALSE.

For example, in config/database.php

$db['database1']['hostname'] = "localhost";
$db['database1']['username'] = "username";
$db['database1']['password'] = "password";
$db['database1']['database'] = "database1";
$db['database1']['dbdriver'] = "mysql";
$db['database1']['dbprefix'] = "";
$db['database1']['pconnect'] = TRUE;
$db['database1']['db_debug'] = TRUE;
$db['database1']['cache_on'] = FALSE;


$db['database2']['hostname'] = "localhost";
$db['database2']['username'] = "username";
$db['database2']['password'] = "password";
$db['database2']['database'] = "database2";
$db['database2']['dbdriver'] = "mysql";
$db['database2']['dbprefix'] = "";
$db['database2']['pconnect'] = FALSE;
$db['database2']['db_debug'] = TRUE;
$db['database2']['cache_on'] = FALSE;

[eluser]Randy Casburn[/eluser]
The usual culprit is using the usual $this-CI->db->query() or whatever calls...

Can't do that. You must use the respective db handles that were established at connection time with the additional boolean parameter that stores the handle in a variable. So your db calls become something like:

$this->myUsersDB->query()

and

$this->myEventsDB->query()

Hope this is helpful,

Randy

[eluser]Rvnikita[/eluser]
$db[‘database2’][‘pconnect’] = FALSE;
is realy help me!
2 days in dead to find the solution .(

[eluser]Sumon[/eluser]
Never mind guys. I have tried but get errors
here is database.php

Code:

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "ci_practice";
$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['second_db']['hostname'] = "localhost";
$db['second_db']['username'] = "root";
$db['second_db']['password'] = "";
$db['second_db']['database'] = "dev_job";
$db['second_db']['dbdriver'] = "mysql";
$db['second_db']['dbprefix'] = "";
$db['second_db']['pconnect'] = FALSE;
$db['second_db']['db_debug'] = TRUE;
$db['second_db']['cache_on'] = FALSE;
$db['second_db']['cachedir'] = "";
$db['second_db']['char_set'] = "utf8";
$db['second_db']['dbcollat'] = "utf8_general_ci";

here is my controller

Code:

function duel_db2()
{
    $this->load->model('test_model');
    $user_data = $this->test_model->get_user(1);
    print_r($user_data);
    echo "<br>";
    $user_data = $this->test_model->get_country();
    print_r($user_data);
}

here is my model

Code:

class Test_model extends Model
{
   function __construct()
   {
    parent::Model();
   }
function get_user($id)
{
  $this->load->database('default');
  $query = $this->db
                ->from('users')
                ->limit(10)
                ->where('id' , $id)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

function get_country()
{
  $this->load->database('second_db');
  $query = $this->db
                ->from('countries')
                ->limit(1)
                ->where('id' , 10)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

}

But i am getting error. Where i am doing wrong?

Code:

Table 'ci_practice.countries' doesn't exist

[eluser]Randy Casburn[/eluser]
If you insist on this configuration, then you must insist upon yourself that you disconnect your db by issuing $this->db->close(); whenever you have completed your db operations.

i.e.

Code:

function get_user($id)
{
  $this->load->database('default');
  $query = $this->db
                ->from('users')
                ->limit(10)
                ->where('id' , $id)
                ->get();
// THIS MUST BE DONE IF YOU INSIST ON YOUR METHOD //
  $this->db-close();  
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

Hope this helps,

Randy

[eluser]Sumon[/eluser]
Now i am getting the following error

Code:

Fatal error: Call to undefined function close() in C:\wamp\www\CIPractice\application\models\test_model.php on line 63

[eluser]Randy Casburn[/eluser]
Some db drivers don't support this method or only support _close(), which you cannot call from you Model. You can try if you'd like.

Your best option is to capture the DB handle and refer to the db handle correctly. That will ensure you are asking the correct db for the correct data at the correct time.

It is less about being lazy and more about being precise. Please read my very first reply to the OPs first post.

Randy

[eluser]Rvnikita[/eluser]
[quote author="Sumon" date="1222918942"]

Code:

class Test_model extends Model
{
   function __construct()
   {
    parent::Model();
   }
function get_user($id)
{
  $this->load->database('default');
  $query = $this->db
                ->from('users')
                ->limit(10)
                ->where('id' , $id)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

function get_country()
{
  $this->load->database('second_db');
  $query = $this->db
                ->from('countries')
                ->limit(1)
                ->where('id' , 10)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

}

But i am getting error. Where i am doing wrong?
[/quote]

As I understand, if you use more than 1 database, you cannot use "$this->db" for both of them.
And I don't do it, I use this construction only for one database, but for another I use

Code:

$SecondDatabaseHandle =  $this->load->database('second_db', TRUE);

and use this Handle like

Code:

$SecondDatabaseHandle->from('countries');

But for the first database connection you can continue use the

Code:

$this->db->from('countries');

construction.

Why I want to use for one connection $this->db->from('countries')? Because I wrote my site for one connection, and after some time there is a need to use second database connection, but I dont whant to rewrote half site code.

[eluser]Sumon[/eluser]
Finally i sort it out how to access multiple database simultaneously.
Here is my config\database.php

Code:

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "ci_practice";
$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['second_db']['hostname'] = "localhost";
$db['second_db']['username'] = "root";
$db['second_db']['password'] = "";
$db['second_db']['database'] = "dev_job";
$db['second_db']['dbdriver'] = "mysql";
$db['second_db']['dbprefix'] = "";
$db['second_db']['pconnect'] = FALSE;
$db['second_db']['db_debug'] = TRUE;
$db['second_db']['cache_on'] = FALSE;
$db['second_db']['cachedir'] = "";
$db['second_db']['char_set'] = "utf8";
$db['second_db']['dbcollat'] = "utf8_general_ci";

here is my controller

Code:

function duel_db2()
{
    $this->load->model('test_model');
    $user_data = $this->test_model->get_country(1);
    print_r($user_data);

    $user_data = $this->test_model->get_feedback(1);
    print_r($user_data);
    $this->load->database('default');    //Get Back into default.
}

and finally this is my model(test_model.php)

Code:

function get_country()
{
  $DBTwo = $this->load->database('second_db', TRUE);
  $query = $DBTwo
                ->from('countries')
                ->limit(10)
                ->where('id' , 10)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}

function get_feedback($id)
{
  $DBOne = $this->load->database('default', TRUE);
  $query = $DBOne
                ->from('feedback')
                ->limit(10)
                ->where('id' , $id)
                ->get();
  if ($query->num_rows() > 0)
  {
    $rows = $query->row_array();
    return $rows;
  }
  return false;
}