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

Question

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 makes:

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());
} 

Only you are not doing the calculation correctly and the values that appear in dif has nothing to do with the bill, what I’m doing wrong ?

Answer 1

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

Try a var_dump at the beginning 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 subtract, I suggest you create a variable that receives subtraction, to avoid calculations in the SQL statement and avoid any future performance problems. Aside from generating the variable, you can later verify the value that was generated, so you can make a conference before giving the UPDATE in the database.

Ex:

$difavancfinanc = $VarConvValo - $VarConvValorFinanc;

Answer 2

I have two possible solutions:

The first is that you are doing a string subtracting another string, this may be giving conflict. Then unquote the $Varconvvalor and $Varconvvalorfinanc variables from the update:

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

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

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