Get data from another Database in Codeigniter MySQL-CodePudding

I want to get data from another DB in MySQL except the defualt DB. I added the following line into my model in addition to the excisting code. Another DB as 'storesDB'.

$this->load->database('storesDB', TRUE);

My model as follows :

public function getEquipment($id = null)
    {  
        $this->db->select('*');
        $this->db->from('tbl_equipment');
        $this->db->join('tbl_equipment_category', 'tbl_equipment.eq_cat=tbl_equipment_category.id_cat');
        $this->db->where("tbl_equipment.status", '1');
        if ($id)
            $this->db->where("tbl_equipment.id", $id);        
            $query = $this->db->get();
        return $query->result();
    }

The code is working properly. But the code got data from defualt DB not the by the 'storesDB'.

Modified code is as follows :

public function getEquipment($id = null)
    {       
        $this->load->database('storesDB', TRUE);
        $this->db->select('*');
        $this->db->from('tbl_equipment');
        $this->db->join('tbl_equipment_category', 'tbl_equipment.eq_cat=tbl_equipment_category.id_cat');
        $this->db->where("tbl_equipment.status", '1');
        if ($id)
            $this->db->where("tbl_equipment.id", $id);        
            $query = $this->db->get();
        return $query->result();
    }

What may be the reason? Can anyone help ?

CodePudding user response：

I have no experience in Codeigniter, but in a standar sql query you can add the database name in front of the table to get info from another db like:

SELECT * FROM sakila.actor;

Assuming you are not connected to sakila db.

Using this aproach both databases must have same credentials

CodePudding user response：

The reason is that you don't assign the second database properly. You also need to setup the correct config for the 2nd database

$config['hostname'] = "host";
$config['username'] = "user";
$config['password'] = "pass";
$config['database'] = "storesDB";
// etc..

then you load the database:

$this->db2 = load->database($config, TRUE);

now you have $this->db, the default database and $this->db2, the second database

and continue your code like:

$this->db2->select('*');
$this->db2->from('tbl_equipment');
// etc...