'Decrement value in mysql but not negative

I want to decrement a value when user delete it in php and mysql. I want to check not to go below than 0. If value is 0 then do not decrement.

mysql_query("UPDATE table SET field = field - 1 WHERE id = $number");

If field is 0 then do not do anything

mysqldecrement


Solution 1:[1]

You could prevent the new value to drop below zero by using GREATEST(). If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.

UPDATE  table
SET     field = GREATEST(0, field - 1)
WHERE   id = $number

And on a side note: Please don't use mysql_* functions any more. They are deprecated and will eventually be removed from PHP. Use PDO or MySQLi instead.

Solution 2:[2]

The option using GREATEST will not work in newer MySQL versions, and the accepted answer can be unuseful if you want to update multiple fields instead of one. My solution for this problem is using IF:

UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number

Solution 3:[3]

UPDATE table SET field = case when (field - 1) >0 then (field - 1)
else field end
WHERE id = $number

Solution 4:[4]

 UPDATE `table_name` SET field = field-1 WHERE `id` = '".$id."' AND field > 0

Note: field's data-type should be an INTEGER.

Solution 5:[5]

if the field is int unsigned, below is the best:

UPDATE table 
SET field = field - 1
WHERE id = $number
and field > 0

# or
UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number

Solution 6:[6]

For someone who search an exemple with PDO Quickly ...

<?php

/**
 * Class Set_quantity_product
 */
 class Set_quantity_product {
    
    
  /**
    * Set_quantity_product::update_quant_prod( $id, $number, $to_do );
    *
    * @param  {int} $id        a product id
    * @param  {int} $number    a number to increment or decrement for a value in DB
    * @param  {str} $to_do     'incr/decr'
    * @return {void}           increment or decrement but always return 0 if quant < 0
    */
    public static function update_quant_prod( $id, $number, $to_do ){
    
       $DATA_BASE = new PDO('mysql:host='.$YOUR_HOST.';dbname='.$YOUR_DB_NAME.';charset=utf8', $YOUR_USER_NAME, $YOUR_DB_PASSWORD, array( PDO::ATTR_PERSISTENT => false));
    
       // Note:
       // UPDATE  products
       // SET     quant = GREATEST( 0, quant+:ope )
       // WHERE   id = :id
       // increm or decrement but do nothing if quant is > quant in DB
    
       // pass quant number to affect to negative for decrement
       $number = ( $to_do == 'decr' ) ? $number*-1 : $number;
    
       // UPDATE ONE PROD. QUANT. 'ope' -> calcul operation
       $ARR_pdo = array( 'id' => (int) $id,
                            'ope' => $number );
       $sql = 'UPDATE products SET
          quant = IF(quant+:ope >= 0, quant+:ope, 0) WHERE id=:id';
    
       // prepa. SQL
       $request = $DATA_BASE->prepare($sql);
    
       // exec. request
       $request->execute($ARR_pdo);
    
       // PDO closeCursor
       $request->closeCursor();
    
       // return true for test
       return true;
   }
   /**
    * Set_quantity_product::update_quant_prod( $id, $number, $to_do );
    */
    
 }
 /**
  * Class Set_quantity_product
  */

?>

Use : ( consider you have a product with an id=42 )

Set_quantity_product::update_quant_prod( 42, 5, 'decr' );

if your quant in DB is 6 -> this set the value at 1

if your quant in DB is 5 -> this set the value at 0

if your quant in DB is 4 -> this set the value at 0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Eelco Luurtsema
Solution 3 Arun Vinoth - MVP
Solution 4 Criesto
Solution 5 Dev
Solution 6

Related Questions