Store new Value, updating Old Value

Question

Store new Value, updating Old Value

Navigation

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

1

Can anyone help me with a function in PHP and MYSQL?

Today I have a view to Expense and Revenue Release. I also have a cashier that, when I issue a Revenue, the amount is added to the cashier and when I post an expense, the amount is subtracted from this box. (so far)

I'm having trouble editing the value of this release.

When I'm editing an expense for example, you're adding the total to the expense amount.

I want the cash value to be updated when I edit a post.

Type: As if I deleted the old release and included a new release with the same ID but a different value and the box would update accordingly.

I have a table that contains a "value" column and another column "old_value".

I need the old_value to always store the last value.

Cénario:
First Register:
  Value = 10,00
  Old Value = Null


First Change:
  Value = 30,00
  Old Worth = 10,00


Second Amendment:
  Value = 50,00
  Old Value = 30.00

I need the function to do the same, today I'm using the following function to update the balance: (It does not work accordingly)

function atualizaSaldoEditar($valor,$conta,$tipoLancamento) {

    $valor = $this->input->post('valor');
    $valorAntigo = $this->input->post('valor_antigo');
    $conta = $this->input->post('conta_id');        

    $atual = "SELECT saldo from contas where idConta=?";
    $this->db->query($atual , array($conta));
    $receita = $atual - $valorAntigo + $valor;
    $despesa = $atual - $valorAntigo + $valor;
    $tipoLancamento = $this->input->post('categoria_id');

    if($tipoLancamento == 1){
        $sql = "UPDATE contas set saldo = saldo + ? WHERE idConta = ?";
        $this->db->query($sql, array($receita , $conta));
    }
    else{
        $sql = "UPDATE contas set saldo = saldo - ? WHERE idConta = ?";
        $this->db->query($sql, array($despesa , $conta));
    };        
}

Accounts table (where the balance is stored)

-- -----------------------------------------------------
-- Table 'contas'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'contas' (
  'idConta' INT(11) NOT NULL AUTO_INCREMENT,
  'conta' VARCHAR(45) NOT NULL,
  'banco' VARCHAR(45) NOT NULL,
  'numero' VARCHAR(45) NOT NULL,  
  'saldo' DECIMAL(10,2) NOT NULL,
  'status' TINYINT(1) NOT NULL,
  'data_cadastro' DATE NULL DEFAULT NULL,
  PRIMARY KEY ('idConta'))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = latin1;

Table where the postings are stored

-- -----------------------------------------------------
-- Table 'lancamentos'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'lancamentos' (
  'idLancamentos' INT(11) NOT NULL AUTO_INCREMENT,
  'descricao' VARCHAR(255) NULL DEFAULT NULL,
  'valor' VARCHAR(15) NOT NULL,
  'valor_antigo' VARCHAR(15) NOT NULL,
  'data_vencimento' DATE NOT NULL,
  'categoria_id' INT(11) NULL DEFAULT NULL,
  'conta_id' INT(11) NULL DEFAULT NULL,
  'data_pagamento' DATE NULL DEFAULT NULL,
  'baixado' TINYINT(1) NULL DEFAULT NULL,
  'cliente_fornecedor' VARCHAR(255) NULL DEFAULT NULL,
  'forma_pgto' VARCHAR(100) NULL DEFAULT NULL,
  'tipo' VARCHAR(45) NULL DEFAULT NULL,
  'anexo' VARCHAR(250) NULL,
  'clientes_id' INT(11) NULL DEFAULT NULL,
  PRIMARY KEY ('idLancamentos'),
  INDEX 'fk_lancamentos_clientes1' ('clientes_id' ASC),
  CONSTRAINT 'fk_lancamentos_clientes1'
    FOREIGN KEY ('clientes_id')
    REFERENCES 'clientes' ('idClientes')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

php mysql codeigniter

asked by anonymous 19.09.2016 / 17:38

2 answers

JavaScript Slider Fading JSON request comes full page

score 0 · Answer 1

In your code, you make no mention of the posting table, that is, you are not posting the postings that will result in the balance of the accounts table.

In addition to updating the balance in the accounts table, you need to run a INSERT INTO... in the posting table. This should be done in another method that was not pasted here (something like insertTitle ).

By your question, it looks like you're also wanting to save the last balance before the current release. To do this, you must add the old_value column in the Accounts column (instead of the Postings table). Then yes with UPDATE you solve (as @VirgilioNovic). If the old_value column in the table is already used to register this, you only need to look up where this table is being used to record the postings and use the one update in the table "releases".

score 0 · Answer 2

I recommend leaving the job with MySQL, using TRIGGERS

In this way you can do something like:

CREATE TRIGGER 'nome_trigger' 
BEFORE UPDATE ON 'tabela' 
FOR EACH ROW SET 
SaldoAnterior = OLD.Saldo

Suppose a table:

- id (Padrao : AUTO INCREMENT)
- Saldo
- SaldoAnterior (Padrão : "0")

If you do this:

INSERT INTO tabela ('Saldo')  VALUES ('10')

Assuming this INSERT is of ID = 1, doing this:

UPDATE tabela SET Saldo = 50 WHERE id = 1;

Automatically will trigger TRIGGER , resulting in:

SELECT * FROM tabela WHERE id = 1

id: 1
Saldo: 50
SaldoAnterior: 10

This is an example of another (and in my opinion best) solution, you should suit your needs, logically.

I will not emphasize this, but be careful when manipulating "balance" out of TRANSACTION " and without LOCK and be aware of which level of isolation you are using, as there is not any code I believe everything is ok.