Get name in Select Join

Question

See the following function:

$id_contratos = array_map(function($item)
{ return $item['id'];}, $result['contratos']);

$empresa = " -- aqui eu preciso recuperar o nome da empresa --";
$view["empresa"] = $empresa;

And the table with the following fields:

DROP TABLE IF EXISTS `tbl_devedor_contrato`;
CREATE TABLE IF NOT EXISTS `tbl_devedor_contrato` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`plano` VARCHAR(10) NULL DEFAULT NULL,
`contrato` VARCHAR(60) NOT NULL,
`dt_contrato` DATE NULL DEFAULT NULL,
`dt_expiracao` DATE NULL DEFAULT NULL,
`devedor_id` INT(11) NULL DEFAULT NULL,
`empresa_id` INT(11) NULL DEFAULT NULL,
`importacao_id` INT(11) NULL DEFAULT NULL,
`crud` VARCHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1;

Knowing that the id table tbl_devedor_contrato is equal to $id_contratos, i want to retrieve information from the field empresa_id from this same table and having the information from this field, I need to use it to make a Join and get the nome_fantasia of the company, concerning the int obtained in empresa_id.

I know I’d have to make one first select and then a Join, but I tried and it didn’t work.

Below the table, to get the nome_fantasia

DROP TABLE IF EXISTS `tbl_empresa`;
CREATE TABLE IF NOT EXISTS `tbl_empresa` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`cnpj` varchar(20) NOT NULL,
`nome_fantasia` varchar(255) NOT NULL,
`razao_social` varchar(255) NOT NULL,
`cep` varchar(8) DEFAULT NULL,
`logradouro` varchar(160) DEFAULT NULL,
`numero` varchar(20) DEFAULT NULL,
`complemento` varchar(60) DEFAULT NULL,
`bairro` varchar(60) DEFAULT NULL,
`cidade` varchar(60) DEFAULT NULL,
`uf` varchar(2) DEFAULT NULL,
`telefone` varchar(10) DEFAULT NULL,
`email` varchar(160) DEFAULT NULL,
`id_assessoria` INT(11) NOT NULL,
`dt_cadastro` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`dt_atualizacao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`situacao` TINYINT(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

Variable of $result['contratos']:

Array
(
[0] => Array
     (
        [id] => 1
        [plano] => 4
        [contrato] => 93642059600
        [dt_contrato] => 2017-11-01
        [dt_expiracao] => 2017-11-30
        [devedor_id] => 1
        [empresa_id] => 1
        [importacao_id] => 0
        [crud] => R
    )
)

Answer 1

To do this in codeigniter, just use JOIN in your data return function, as follows:

public function get_devedor_contrato(){
    $this->db->select("tbl_devedor_contrato.*, tbl_empresa.razao_social");
    $this->db->join("tbl_empresa", "tbl_empresa.id=tbl_devedor_contrato.empresa_id");
    return $this->db->get('tbl_devedor_contrato')->result();
}