Store New Value by Updating Old Value

Question

Can someone help me with a PHP and MYSQL function?

Today I have a view for Expense and Revenue Launch. I also have a box, which, when I launch an Revenue,the value is added to the box and when I set an expense, the value is subtracted from this box. (so far it works)

I’m having problems editing the value of this release.

When I’m editing an expense for example, it’s adding up the cash total with the expense amount.

I want the value of the box to be updated when editing a release.

Type: As if I deleted the old release and included a new release with the same ID, but different value and the box updated according to the edition.

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

I need the old value to always store the last value.

Caenarius:
First Register:
Value = 10,00
Old value = Null

First Amendment:
Value = 30,00
Old value = 10,00

Second amendment:
Value = 50,00
Old value = 30,00

And I need the function to do the same, today I’m using the following function to update the balance:(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 releases 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;

Answer 1

In your code, you make no mention of the table releases, ie you are not registering the releases that will result in the balance of the table accounts.

In addition to updating the balance in the accounts table, you need to run a INSERT INTO... in the table of releases. This must be being done in another method that was not pasted here (something like insert).

By your question, it seems that you are also wanting to save the last balance before the current release. For this, you should add the column old value column accounts (instead of table entries). There yes with a UPDATE you resolve (as @Virgilionovic). If the old value column in the table already serves to record this, just search where this Tablea is being used to record the releases and use the update in the table "lancamentos".

Answer 2

I recommend you leave the work with Mysql, using TRIGGERS

That 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 ID = 1, doing this:

UPDATE tabela SET Saldo = 50 WHERE id = 1;

Will automatically trigger the TRIGGER, resulting:

SELECT * FROM tabela WHERE id = 1

id: 1
Saldo: 50
SaldoAnterior: 10

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

I will not emphasize this, but beware of manipulating "balance" out of TRANSACTION and without LOCK and be aware of what level of isolation you are using, as there is no code at all I believe everything is ok.