Update doesn't work as should be in MySQL

Question

 echo $totalprice;
echo "<br/>";
echo $shortfall;
echo "<br/>";
echo $unitprice;
echo "<br/>";

I got

24 80 0.3

Then the following command was executed.

// update query

However, only

total_price

was changed(became 0.00) while other values like

unit_price

stay unchanged. But other values like

unit_price

should be changed.

Total_price

is

unsigned when total_price-pricebalance is done, it becomes 0.00. So does it refuse to subtract $totalprice? Any idea?

Answer 1

Why are you using ANDs in your UPDATE query?

mysql_query("update piecework set total_price=total_price-pricebalance+$totalprice, quota=quota-shortfall+$shortfall, shortfall=$shortfall, unit_price=$unitprice, pricebalance=$totalprice where piecework_id='$pieceworkid' and publisher=$memberid and (pricebalance-$totalprice)>=0")or die(mysql_error());

Or with better readability:

UPDATE piecework SET total_price = total_price - pricebalance + $totalprice,
                     quota = quota - shortfall + $shortfall,
                     shortfall = $shortfall,
                     unit_price = $unitprice,
                     pricebalance = $totalprice
...