计算用户是否已达到极限 [英] Count if a user has reached the borrwing limit

问题描述

我已经在表和数据上设置了小提琴此处

I've setup a fiddle with tables and data here

我正在尝试编写一个sql来检查用户是否已达到每个类别的借用限制.

I'm trying to write a single sql to check if user has reached the borrowing limit for each category.

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

Right now, it's done using severals sql statements called after each other.

但是方法很简单. memId和id通过查询字符串来获取.

But the way it goes is simple. memId and id come through a querystring.

$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或最好以这种方式保留?

How can this be combined into a single sql or is it best left this way?

推荐答案

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

This seems to produce a correct result set:

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

这篇关于计算用户是否已达到极限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！