计算用户是否已达到极限

一尘不染

计算用户是否已达到极限

sql

我正在尝试编写单个sql，以检查用户是否已达到每个类别的借入限制。

现在，它使用相互调用的几个sql语句完成。

但是它的方法很简单。memId和id通过查询字符串来实现。

$medId = $_POST['memId']; Using 1 for this example. This is the members Id.
$id = $_POST['id']; Using 4 for this example. This is the item being lent.

之后，我要做：

select id, holder from collection_db where id = 4 // We have a valid item

select borrowMax from collection_db where id = (holder from the previous select) and category = 10 //Result = 2. Category indicates its a label and not a borrowable item.

select count(borrowedId) from lendings where memId = 1 and holder = (holder from the 1st query) //He's borrowed 2, under 1, so cant borrow any more. User 2 may borrow however.

if (count => borrowMax) {echo 'Cannot borrow more.';} else {echo 'Added to'}

如何将其组合成单个sql或最好以这种方式离开？

阅读 132

2021-05-23

共1个答案

一尘不染

这似乎产生了正确的结果集：

SELECT col1.id, col1.holder, col2.borrowMax, count(lend.borrowedId) as `count`
FROM collection_db col1
  INNER JOIN collection_db col2
  ON col1.holder = col2.id
    INNER JOIN lendings lend
    ON col1.holder = lend.holder
WHERE col1.id = $id
AND col2.category = 10
AND lend.memId = $medId

2021-05-23