相同的物品有两条或者两条以上的库存量,在扣除库存时如何先扣除库存量最低或最早入库的呢?
当wupin_name为QD001的需求量post传来的$demand是8时,如何先扣除id库存最低的1个,再依次扣除后面的5,最后id为3的库存变为8。
mysql表结构
id inputdate wupin_name wupinID kucun
1 2023-4-12 QD001 FF202101 1
2 2023-5-1 QD001 FF202305 5
3 2023-5-20 QD001 FF202302 10
链接数据库:
<?php
try {
$pdo = new PDO('mysql:host=127.0.0.1;dbname=mydb;port=3306', 'root', 'root');
} catch (PDOException $e) {
die('connet error :' . $e->getMessage());
};
$pdo->exec('set names utf8');
顺序扣减库存,先扣除库存最少的,或者最早时间入库的物品。目前无法扣除:
$demand = $_POST['demand'];
$wupin_name = $_POST['wupin_name'];
$result = $pdo->query("SELECT * FROM kucun WHERE wupin_name='$wupin_name ' and kucun>0 ORDER BY id ASC");
$reduced = 0; //已减少的总量
while ($row = $result->fetchAll(PDO::FETCH_ASSOC)) {
if($reduced < $demand) {
if( $row['kucun'] <= ($demand - $reduced)) {
$reduced += $row['kucun'];
$send1 = $pdo->exec( "UPDATE kucun SET kucun=0 WHERE id={$row['id']}");
} else {
$send1 = $pdo->exec( "UPDATE kucun SET kucun={$row['kucun']-($demand-$reduced)} WHERE id={$row['id']}");//报错:Parse error: syntax error, unexpected '-', expecting :: (T_PAAMAYIM_NEKUDOTAYIM) in.....
$reduced = $demand;
break;
}
}
}