我可以使用PHP在同一个MYSQL表中从两个其他单元格更新一个单元格

Question

i want to update a single row multiple time. when i update it from one cell it update successfully but when i update it from two cell one of them updated while another want is not.

the MYSQL Table Registration is:

CREATE TABLE `registration` (
  `Reg_id` int(11) NOT NULL AUTO_INCREMENT,
  `Arrears` int(10) NOT NULL,
  `Current_Bill` varchar(20) NOT NULL,
  `Current_Surcharge` varchar(20) NOT NULL,
  PRIMARY KEY (`Reg_id`),
  KEY `Reg_id` (`Reg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;

when i update Arrears from Current_Bill and update Current_Bill to 0. it works. but when i update Arrearsfrom Current_Bill and update Current_Bill to 0 and update Arrears from Current_Surcharge and Current_Surcharge to 0 then Current_Surcharge is updating to Arrears and Current_Surcharge updating to 0 and also Current_Bill Updating to 0 but Arrears can not be updated from Current_Bill

The PHP Code Is:

 <?php
    include("Database/connection.php");

    $sql = "SELECT *  FROM registration,billing_month WHERE Bill_id=(SELECT MAX(Bill_id) FROM billing_month)";
    $result = $link->query($sql); 

        while($row = $result->fetch_assoc()) 
        {
            $currentsurcharge = $row['Current_Surcharge'];
            $currentbill = $row['Current_Bill'];
            $arrears = $row['Arrears'];

            $updatearrearsfromcurrentbill = $arrears + $currentbill;
            $updatecurrentbilltozero = 0;
            $updatearrearsfromcurrentsurcharge = $arrears + $currentsurcharge;
            $updatecurrentsurchargetozero = 0;

            if (strtotime(date('Y-m-d')) > strtotime($duedate)) 
            {
            // thid query is not working while the other 
            // Update Arrears From Current Bill when Bill Is Not Pay On Due Date
             $sql_update5 = "UPDATE  `registration` SET `Arrears`=             $updatearrearsfromcurrentbill WHERE `Reg_id` = $regid";
            // Update Current Bill To Zero 
            $sql_update6 = "UPDATE  `registration` SET `Current_Bill`= $updatecurrentbilltozero WHERE `Reg_id` = $regid";
            // Update Arrears From Current Surcharge when Bill is not pay on due date
            $sql_update7 = "UPDATE  `registration` SET `Arrears`= $updatearrearsfromcurrentsurcharge WHERE `Reg_id` = $regid";
            // Update Current Surcharge to Zero
            $sql_update8 = "UPDATE  `registration` SET `Current_Surcharge` = $updatecurrentsurchargetozero WHERE `Reg_id` = $regid";

            mysqli_query($link, $sql_update5);
            mysqli_query($link, $sql_update6);
            mysqli_query($link, $sql_update7);
            mysqli_query($link, $sql_update8);
    }

    }

    ?>

Answer 1

You need to specify additional parameters in the where clauses to achieve what you want because currently they ALL simply user where Reg_id=$regid - so in essence you overwrite the arrears data with the second query. In both cases you say, in your comments, that the query should update the arrears if the bill is not paid by due date - that field can only hold one value.

Effectively, the way it is written currently, your sql is the same as:-

$sql_effective="update `registration` set 
    `arrears`='$updatearrearsfromcurrentsurcharge',
    `current_bill`='$updatecurrentbilltozero',
    `current_surcharge`='$updatecurrentsurchargetozero'
    where `reg_id`='$regid';";

You need to clarify your logic on what you want to achieve under certain conditions - I'm not clear on what your requirements are.

Based on your further comments, this might suffice. ( Note that there is only 1 update query! ):

$sql_update = "update `registration` set 
    `arrears`=".abs( $arrears + $currentbill + $currentsurcharge ).",
    `current_bill`=0,
    `current_surcharge`=0
    where `reg_id`='$regid';";