如何从文本框值增加mysql值？

Question

Im trying to update a value in MySQL, to increase a number.Lets say that current value is 50, and if I will type 50 in the text-box, mysql value to become 100. Actually with the code below its only updating with the current text-box value.

     function updateMaterial($code){
                global $conn;
                $sql = $conn->prepare("SELECT * FROM stock WHERE itemcode = '$code'");
                $sql->execute();
                while($row = $sql->fetch(PDO::FETCH_ASSOC)){
                    $this->id = $row['id'];
                }
                if(isset($_POST['qty'])){

                     $sql = $conn->prepare("UPDATE stock SET qty='qty +$this->qty' WHERE id='$this->id'");
                     $sql->execute();

                }

            }

if(isset($_POST['update'])){
    $code = $_POST['itemcode'];
    if($addstock->updateMaterial($code)){
     return true;
    }else{
     return false;
    }
}

Answer 1

You have the column name inside the quote, I would say that the query is in fact failing as you have it, but you are not checking for errors. To prove this supposition, test by passing 10 from the screen and see that the value of 50 in the database never changes.

You should also be using prepared statement, to protect against SQL Injection

Also you first query serves no purpose, if you can get the row using the code as the key then use it in the Update query.

Also as this appears to be a method of a class the $conn variable should not be collected using global it destroys the encapsulation of the class. It should be a class property

class xxx
{
    private $conn;

    public function __construct($db_conn)
    {
        $this->conn = $db_conn;
    }


    function updateMaterial($code){

        $sql = $this->conn->prepare("UPDATE stock SET qty=qty+:incqty 
                                      WHERE WHERE itemcode = :code");
        $param = array(':incqty'=>$this->qty, ':code'=>$code);
        $sql->execute($param);

        if ( ! $sql ) {
            // while testing
            $arr = $sql->errorInfo();
            print_r($arr);
            exit;
        }      
    }

Now when you instaniate the object pass the database connection handle

$xxx = new xxx($conn);
$xxx->qty = 30;
$xxx->updateMaterial($code);