SELECT DINAMICO CODEIGNITER - AJAX

Question

Hello! I have the following tables:

tbl_lancamento
tbl_category
tbl_burden

Whereas in tbl_lacamento there is a field (id_categoria) that receives the ids of tbl_categoria and tbl_categoria receives their id(id_juros), coming from the table tbl_juros.

I’m using codeigniter and intend to display in a field hidden <input type="hidden" id="juros" name="txt_juros" placeholder="juros" title="juros"/> in the view lançamento all categories of tbl_categoria, and I want to display dynamically, all the ids_juros related to tbl_categoria.

Can you lead me to a code that does that? I believe that this would be work for ajax, I tried to relate, following an example of states and cities, but could not, because the related ids are in the parent table.

What I have today, shows only the categories.
Below a view code

<select id="categoria" class="form-control" name="sel_id_categoria" >
    <?php foreach ($categoria as $categoria) {
       echo '<option value="'.$categoria->id.'">'.$categoria->descricao.'</option>';
    } ?>
</select>

Code of the controller release

//Listar Categorias ativas Receitas e Despesas
$this->load->model('app/Categoria_model');
$this->data['categoria'] = $this->Categoria_model->obter_ativo('tbl_categoria', 'tbl_categoria.id,tbl_categoria.descricao');

Code in model Categoria_model

	//Listar ativos
  var $tabela = 'tbl_categoria';	
	function obter_ativo($tabela,$campos)
	{        
        $this->db->select($campos);
        $this->db->from($tabela);
        $this->db->where('situacao',1);
        $query = $this->db->get();
        return $query->result();;
  }

-- -----------------------------------------------------
-- Table `tbl_lancamento`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tbl_lancamento`;
CREATE TABLE IF NOT EXISTS `tbl_lancamento`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `tipo` TINYINT(1) NOT NULL DEFAULT 0,
  `dt_lancamento` DATETIME NULL DEFAULT NULL,  
  `descricao` VARCHAR(255) NULL DEFAULT NULL,
  `valor` decimal(10,2) NOT NULL,
  `dt_vencimento` DATE NOT NULL,
  `id_categoria` INT(11) NULL DEFAULT NULL,
  `id_juros` INT(11) NULL DEFAULT NULL,
  `id_conta` INT(11) NULL DEFAULT NULL,
  `baixado` TINYINT(1) NOT NULL DEFAULT 0,
  `valor_pagamento` decimal(10,2) NOT NULL,
  `dt_pagamento` DATE NULL DEFAULT NULL,  
  `forma_pagamento` VARCHAR(100) NULL DEFAULT NULL,
  `estornado` TINYINT(1) NOT NULL DEFAULT 0,
  `observacao` TEXT(160) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tbl_lancamento_tbl_conta` (`id_conta` ASC),
  CONSTRAINT `fk_tbl_lancamento_tbl_conta`
  FOREIGN KEY (`id_conta`)
  REFERENCES `tbl_conta` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
  )
ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tbl_categoria`;
CREATE TABLE IF NOT EXISTS `tbl_categoria` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `descricao` varchar(45) NOT NULL,
  `tipo` TINYINT(1) NOT NULL,
  `id_juros` TINYINT(1) NOT NULL,
  `aplicar_juros` TINYINT(1) NOT NULL,
  `situacao` TINYINT(1) NOT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tbl_encargo`;
CREATE TABLE IF NOT EXISTS `tbl_encargo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `descricao` varchar(45) NOT NULL,
  `juros` decimal(10,2) NOT NULL,
  `multa` decimal(10,2) NOT NULL,
  `funcao` varchar(250) NULL DEFAULT NULL,
  `situacao` TINYINT(1) NOT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

I’d do it this way:

<select id="categoria" class="form-control" name="sel_id_categoria" >
    <?php foreach ($categoria as $categoria) {
       echo '<option value="'.$categoria->id.'">'.$categoria->descricao.'</option>';
    } ?>
</select>

<input type="hidden" name="juros" id="juros" value="">


<script>
    $("#categoria").change(function(){
        var categoria = $("#categoria").val();
            $.ajax({
                url: "ajax/buscar_juros", // Metodo de buscar juros
                type: "POST",
                data: {categoria:categoria},
                success: function(data){
                    $("#juros").val(data);
                }
            });
    });
</script>

ajax/search_interest

public function buscar_juros(){
    $this->db->where('categoria', $this->input->post('categoria'));
    echo $this->db->get("juros")->row('taxa_juros');
}