使用PHP中的MySQL获取函数计算表中2个字段的总和

Question

I have a table having the following fields: id(Autoincrement/Primary Key)/Integer1/Integer2/Sum/Product). I have filled integer1 and integer2 with the following code:

    for ($i=1;$i<=1000;$i++)
{
    $x2=rand(0,100);
    $x3=rand(0,100);
    $sql="INSERT INTO data(integer1,integer2) VALUES ($x2,$x3)";
$conn->query($sql);     
}

I need help to prepare a function which uses MySQLFetch and computes sum of integer1 and integer2 and assigns the value in sum and product. I know it can be done using a simple loop, but would really like to get an understanding of fetching data.

Answer 1

Assuming you are using mysqli which is how it appears with the use of $conn->query() then this might be of interest.

/* establish db connection */
$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';
$conn   =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

/* select records to to perform sum & product upon */
$sql='select * from `data`';
$res=$conn->query( $sql );
if( $res ){

    /* prepare a new sql statement to update db records */
    $sql='update `data` set `sum`=?, `product`=? where `id`=?;';
    $stmt=$conn->prepare( $sql );

    while( $rs=$res->fetch_object() ){
        /* make some variables with records for each record */
        $id=$rs->id;
        $int_1=$rs->integer1;
        $int_2=$rs->integer2;

        /* basic maths operations */
        $sum=$int_1+$int_2;
        $product=$int_1 * $int_2;

        /* bind the variables into declared statement & execute */
        $stmt->bind_param( 'iii', $sum, $product, $id );
        $stmt->execute();
    }
    /* tidy up */
    $stmt->close();
    $conn->close();
}