mysql：将数学运算存储为新数据库

Question

I have table, from this table, I want to do some math operation (such as sum) then I want to save the result in a separate column from the table.

so this is my table

table: xxx

!  code   !   A  !   B  !   C  !
---------------------------------
!  0001   !   4  !   2  !      !
!  0002   !   2  !   1  !      !
!  0003   !   1  !   4  !      !
!  0004   !   3  !   5  !      !

then I use this code to call the query, and save the math operation.

$mySql = "SELECT *  FROM xxx ORDER BY code ASC "; 
$myQry = mysql_query($mySql, $koneksidb)  or die ("Query salah : ".mysql_error());
while ($myData = mysql_fetch_array($myQry))

$sum = $myData['A'] +  $myData['A']

$mySql  = "UPDATE xxx  SET C='$sum' WHERE code='$Kode'";
$myQry  = mysql_query($mySql, $koneksidb) or die ("Gagal query".mysql_error());
while ($myData = mysql_fetch_array($myQry))
{
    $Kode = $myData['code'];
}

my problem is, it's not come up like what I want. I want like this:

table: xxx

!  code   !   A  !   B  !    C  !
---------------------------------
!  0001   !   4  !   2  !    6  !
!  0002   !   2  !   1  !    3  !
!  0003   !   1  !   4  !    5  !
!  0004   !   3  !   5  !    8  !

but this is what I've got

table: xxx

!  code   !   A  !   B  !    C  !
---------------------------------
!  0001   !   4  !   2  !    6  !
!  0002   !   2  !   1  !    6  !
!  0003   !   1  !   4  !    6  !
!  0004   !   3  !   5  !    6  !

any help? I think, my code just take data from the first code, how can I fix this?

Answer 1

There are several issues here and this task can be done much easier. However since this sounds like you are trying things and want to learn let's concentrate on you code.

The immediate problem is the structure of your code. You perform your math operation, however your structure does not store the result as you expect it to. You have to change the structure to something like that:

$selectQuery = "SELECT * FROM xxx ORDER BY code ASC"; 
$selectResult = mysql_query($selectQuery, $koneksidb) or die ("Query salah: ".mysql_error());
while ($myData = mysql_fetch_array($selectResult)) {
    $sum = $myData['A'] +  $myData['B'];
    $code = $myData['code'];
    $updateQuery = "UPDATE xxx SET C='$sum' WHERE code='$code'";
    mysql_query($updateQuery, $koneksidb) or die ("Gagal query: ".mysql_error());
}

Especially not the curly brackets used with the while command: while (...) { ... }.

A general warning: you are using the old, outdated and deprecated mysql extension here. You should take a look at the current alternatives mysqli or PDO and learn about the advantages of "prepared statements" and "parameter binding" for your code, so that you do not run into the typical issue of "sql injection vulnerability".

As mentioned in the beginning it certainly is possible to simplify this whole task by not performing the math computation in php, but directly in mysql. That way you would only need a single sql update statement which does all the job: UPDATE xxx SET C=A+B;. But as mentioned before your question looks like you are trying to work into this field, so it probably is a very good idea to first get your own code to work before moving on.