Please enable JavaScript to view this site.

Navigation: Advanced topics > Events > Sample events > Database

Limit the number of records users can add

Scroll Prev Next More

 

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()

Security screen

Database API:Query()

About SQLQuery class