I would like to check the stock of each number before doing anything forward, and if it is in stock reduce a given number from the stock amount. The issue is it still updates other numbers even if a number is not in stock. for my need, it should update only if all the numbers are in stock.

for example in the given array below 998, 989 and 899 are the numbers. consider 899 has a stock of 10 and other are 100. if $count="20" the query should not update existing stock so that the affected rows will be zero. it should only update rows if all numbers has a stock of 20 or above (depending on the $count)

Here is the code I am able to do so far

$ids=Array ( [0] => 998 [1] => 989 [4] => 899 );
$count="20";

// Attempt update query execution
$sql = 'UPDATE tbl_numbers_stock SET lot_stock= lot_stock - '.$count.' WHERE lot_stock >= '.$count.'  AND lot_number IN (' . implode( ',', $ids ) . ' );';
if(mysqli_query($link, $sql)){
    $affectedrows=mysqli_affected_rows($link);
    if ( $affectedrows == "0" ) { 
        echo "nothing updated";
 } else { 
        echo "all updated";

here a table sample

CREATE TABLE IF NOT EXISTS `tbl_stock` (
  `id` int(6) unsigned NOT NULL,
  `lot_number` varchar(200) NOT NULL,
  `stock` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `tbl_stock` (`id`, `lot_number`, `stock`) VALUES
  ('1', '100', '1'),
  ('2', '998', '100'),
  ('3', '899', '10'),
  ('4', '999', '100'),
  ('5', '888', '100'),
  ('6', '833', '100'),
  ('7', '989', '100'),
  ('8', '101', '100'),
  ('9', '777', '100'),
  ('10', '104', '100');

Here is some more examples. if input array and $count is as following

$ids=Array ( [0] => 998 [1] => 989 [4] => 899 );
$count="20";

then final data state will not update anything. because one number (899) is missing 10 stock. and it will echo "nothing updated".

if input array and $count is as following $ids=Array ( [0] => 998 [1] => 989 [4] => 899 ); $count="10";

Then it should update stock of all 3 numbers, because all 3 numbers have stock more than 10. and the table will update into following.

(`id`, `lot_number`, `stock`)
      ('1', '100', '1'),
      ('2', '998', '90'),
      ('3', '899', '0'),
      ('4', '999', '100'),
      ('5', '888', '100'),
      ('6', '833', '100'),
      ('7', '989', '90'),
      ('8', '101', '100'),
      ('9', '777', '100'),
      ('10', '104', '100');

and it will echo "all updated".

the above two will echo only if $affectedrows is more than 0, currently even if a single number stock has enough stock is gets updated. I only want it to update if all numbers has enough stock.

CodePudding user response：

Recommended solution.

1. The column stock by meaning stores the amount of something, i.e. it stores numeric data. Modify this column datatype and make it numeric. Define this datatype as unsigned, this will forbid any attempt to alter the data and make the value negative:

ALTER TABLE tbl_stock MODIFY stock INT UNSIGNED;

The query is executed once. It both modifies the column datatype and recalculates all existing values to new datatype.

2. Use the next query:

UPDATE tbl_stock
JOIN (
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@ids,',',seq),',',-1) lot_number, 
         @count stock
  FROM seq_1_to_6
  ) sale USING (lot_number)
SET tbl_stock.stock = tbl_stock.stock - sale.stock;

This query parses the IDs list (user-defined variable @ids is used, put your parameter placeholder instead) which should be provided as CSV values list (use implode on PHP side), then tries to perform an update and to reduce all stock values by specified amount (user-defined variable @stock is used).

If some final stock value should be negative finally then the whole UPDATE fails due to datatype check error (UNSIGNED cannot be negative) which can be catched in PHP, otherwise the updating is performed.

If the amount of values in CSV is less than the amount of values produced by the generator then some copies of the most last CSV value will be produced. But in UPDATE each row is updated only once, so we don't need to remove these duplicates.

fiddle

PS. If you don't want to alter the stock datatype then you may add according CHECK constraint instead:

ALTER TABLE tbl_stock 
    ADD CONSTRAINT stock_cannot_be_negative CHECK (stock   0 >= 0);

But this solution does not forbid to store non-numeric data in this column.

Also think does the datatype of lot_number should be modified too..