Get name in Select Join

Question

Get name in Select Join

Navigation

#1 by (1 votes)

0

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 of the tbl_devedor_contrato table is equal to $id_contratos , I want to retrieve the information from the empresa_id field of this same table and having the information of this field, I need to use it to make a join and get nome_fantasia of the company, relative to int obtained in empresa_id .

I know I'd need to do a select first and then a join, but I've tried and failed.

Below the table, to get 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%:

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
    )
)

codeigniter

asked by anonymous 16.11.2017 / 12:44

1 answer

How do I hide or clear the url while giving a get in asp.net mvc? How to make a query bringing me the last value of a specific id

score 1 · Answer 1

In order for you to be able to do this in codeigniter, simply 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();
}