Modify value that changes another value in SQL

Question

Modify value that changes another value in SQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

My Update function:

    public function Update($pcosto, $user_id)

{
    $query = $this->db->prepare("UPDATE users SET pcosto = :pcosto WHERE id = :id");
    $query->bindParam("pcosto", $pcosto, PDO::PARAM_INT);
    $query->bindParam("id", $user_id, PDO::PARAM_STR);
    $query->execute();
}

I have an Update function that receives a cost price (cost) and the product id to modify it. What I would like to do in the sql statement is that: Update the cost price (which already does) and also modify the value dif performing dif: pventa - pcosto (the value that the Update receives). I hope you understand.

Some considerations to better understand:

The DB has id, pventa (sale price), cost (cost price), and dif (difference of sale-cost).
Modify mainly the cost, but with the modification of this you also make the modification of dif by subtracting sales from the updated value of pcosto . When updating the cost, the update is automatically updated.

sql pdo

asked by Santiago 08.08.2017 в 20:06

source

2 answers

1

You can use the parameter is more than one place at the same time in your query

public function Update($pcosto, $user_id)
{
    $query = $this->db->prepare("UPDATE users SET pcosto = :pcosto, dif = pventa - :pcosto WHERE id = :id");
    $query->bindParam("pcosto", $pcosto, PDO::PARAM_INT);
    $query->bindParam("id", $user_id, PDO::PARAM_STR);
    $query->execute();
}

I hope the answer was useful.

answered by 08.08.2017 в 20:15

Laravel-blade- @ extends does not work and missing snippet? Problem when creating Dataframe in Pandas using Python 3.x

score 1 · Accepted Answer

look you must make another query after making the change so that you take in common the id you are going to operate.

public function Update($pcosto, $user_id){
    $query = $this->db->prepare("UPDATE users SET pcosto = :pcosto WHERE id = :id");
    $query->bindParam("pcosto", $pcosto, PDO::PARAM_INT);
    $query->bindParam("id", $user_id, PDO::PARAM_STR);
    $query->execute();

//Actualizo el costo

    $query = $this->db->prepare("UPDATE users SET dif = (pventa-pcosto) WHERE id = :id"); 
    $query->bindParam("id", $user_id, PDO::PARAM_STR);   
    $query->execute();
}