Error when trying to calculate in fields of the same table in update mysql

Question

Error when trying to calculate in fields of the same table in update mysql

Navigation

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

1

I have the following table:

DROP TABLE IF EXISTS 'convenio';
CREATE TABLE 'convenio' (
  'codigo'      int(8)        DEFAULT NULL,
  'cpf'         varchar(11)   NOT NULL DEFAULT '0',
  'Nome'        varchar(35)   DEFAULT NULL,
  'valor'       decimal(10,2) DEFAULT NULL,
  'valorfinanc' decimal(10,2) DEFAULT NULL,
  'dif'         decimal(10,2) DEFAULT NULL,
  'codger'      varchar(6)    DEFAULT NULL,
  'cpf0'        varchar(11)   DEFAULT NULL,
  'data'        timestamp     NULL DEFAULT CURRENT_TIMESTAMP,
  'status'                    int(11) DEFAULT '0',
  PRIMARY KEY ('cpf')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What I need:

An update that does:

UPDATE SET dif = valor - valorfinanc WHERE cpf = cpf

I'm trying this way:

    $listaconv  = mysql_query ("SELECT 
                                  * 
                                    FROM financeiro 
                                      ORDER BY nome",$convenio_conn) 
                                          or die (mysql_error());
                                             while($row = mysql_fetch_array($listaconv)){
                                               $VarConvCodigo         = $row[0];
                                               $VarConvCPF            = $row[1];
                                               $VarConvNome           = $row[2];
                                               $VarConvValor          = $row[3];
                                               $VarConvCodTotvs       = $row[4];
                                               $VarConvCPF0           = $row[5];
                                               $VarConvData           = $row[6];
                                               $VarConvValorFinanc    = $row[7];
                                               $VarConvDifAvancFinanc = $row[8];
                                               $VarConvStatus         = $row[9];

    $gera_divergencia = mysql_query ("UPDATE convenio 
                                        SET difavancfinanc = '$VarConvValor'-'$VarConvValorFinanc' WHERE cpf = '$VarConvCPF'",$convenio_conn)
                                           or die (mysql_error());
}

But you are not doing the calculation properly and the values that appear in dif have nothing to do with the account, what am I doing wrong?

php mysql

asked by anonymous 24.01.2017 / 13:32

2 answers

2

I have two possible solutions:

The first is that you are making a string by subtracting another string, this may be conflicting. Then unzip the variables $ VarConvValor and $ VarConvValorFinanc from update:

UPDATE convenio SET difavancfinanc = $VarConvValor - $VarConvValorFinanc WHERE cpf = '$VarConvCPF'

Or create a php variable before and set it straight in the query:

$diferenca = $VarConvValor - $VarConvValorFinanc;
$gera_divergencia = mysql_query ("UPDATE convenio SET difavancfinanc = $diferenca WHERE cpf = '$VarConvCPF'",$convenio_conn) or die (mysql_error());

24.01.2017 / 13:47

Modal bootstrap does not open [closed] Access row and column specific of a C # list [closed]

score 2 · Accepted Answer

Have you checked that the values that are coming in $ row are correct?

Try to perform a var_dump at the start of your while to check.

while($row = mysql_fetch_array($listaconv)){
    var_dump($row);
    die;
...

If everything is correct with the data you want to perform the subtraction, I suggest you create a variable that receives the subtraction, to avoid calculations in the SQL statement and avoiding any future performance problems. Other than generating the variable, you can later check the value that was generated, so you can do a conference before giving the UPDATE in the database.

Ex:

$difavancfinanc = $VarConvValo - $VarConvValorFinanc;