Lets say you want to limit the number of records users can add to the certain table. For example, if you run a classified ads website, and you want free users to be able to post up to 3 ads, basic plan users to add up to 10 ads, etc.
Sample database structure
Table1: users, userid, password, limit.
Table2: ads, id, userid, adtext.
Tables are linked via the userid field. It would also make sense to turn on Advanced security mode 'Users can see all data; can edit their own data only'.
Use the following code in the Add page: Before Record Added event.
Scenario 1. All users can add the same number of records
dim limit, rs, data, count
limit = 3
set rs = DB_Query("select count(*) as c from ads where userid = " & SESSION("userid"))
set data = rs.fetchAssoc()
count = data("c")
if count>= limit then
Response.Write "Limit reached: " & count & " records added already"
Response.End
end if
Scenario 2. Each user has it's own limit. Limits are stored in the userlimit field of users table
dim rs, data, count, data2, rs2, limit
set rs = DB_Query("select count(*) as c from ads where userid = " & SESSION("UserID"))
set data = rs.fetchAssoc()
count = CSmartDbl(data("c"))
set rs2 = DB_Query("select " & AddFieldWrappers("limit") & " from users where userid = " & SESSION("UserID"))
set data2 = rs2.fetchAssoc()
limit = CSmartDbl(data2("limit"))
if count>= limit then
Response.Write "Limit reached: " & count & " records added already"
Response.End
end if
See also:
•QueryResult object fetchAssoc()