Only one line as true, MYSQL - Codeigniter

Question

Only one line as true, MYSQL - Codeigniter

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Hello

Since I have the following table:

DROP TABLE IF EXISTS 'tb_conta';
CREATE TABLE IF NOT EXISTS 'tb_conta' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'nome' VARCHAR(255) NOT NULL,
    'tipo' TINYINT(1) NOT NULL DEFAULT 1,
    'padrao' BIT(1) NOT NULL DEFAULT 1,	
    PRIMARY KEY ('id'))
ENGINE=InnoDB
DEFAULT CHARSET=utf8;

I need to make sure that regardless of the amount of rows written in this table, there is only one result in the padrao field whose value is equal to true

Both new insertion and update.

Scenario: If when entering a new record and the value of the field padrao is equal to true , then automatically all other values already registered in the table, become false , the same goes for updates . >

Add and Update Functions

// add
public function add($dados)
{       
    $conta = [
        'nome'   => $dados->nome,
        'tipo'   => $dados->tipo,
        'padrao' => $dados->padrao,
    ];
    $this->db->insert($this->tabela, $conta);
}

// edit 
public function edit($where, $dados)
{     
    $conta = [
        'nome'   => $dados->nome,
        'tipo'   => $dados->tipo,
        'padrao' => $dados->padrao,
    ];
    $this->db->update($this->tabela, $conta, $where);
    return $this->db->affected_rows();
}

Queries:

INSERT INTO 'tb_conta' ('nome', 'tipo', 'padrao') VALUES ('Teste', '1', 1)
UPDATE 'tb_conta' SET 'nome' = 'Caixa Interno', 'tipo' = '1', 'padrao' = 1 WHERE 'id' = '1'

mysql codeigniter

asked by anonymous 21.08.2018 / 21:47

2 answers

How to send via post in java? Compare SQL result using PHP

score 1 · Answer 1

I solved creating a new function and placing a condition on the functions:

function created

// mark_all_false
public function mark_all_false()
{
    $this->db->where('padrao', true)->update($this->tabela, array('padrao' => false));
}

Condition created

if ($dados->padrao == true)
{
    $this->mark_all_false();
}

score 0 · Answer 2

Create a trigger to act before the select for update all that are true, so when you enter the new it will come as TRUE.

CREATE TRIGGER BEFORE INSERT ON Tb_conta FOR EACH ROW BEGIN UPDATE tb_conta SET padrao = false WHERE default = true; END $