limiting user actions by months

Question

hi

my users upload photo and all uploads recorded in mysql with the date info. i want to limit uploads by the months. user may just upload 3 pics in a month. what is the best way to do this ?

cheers

Answer 1

One possible solution:

Have a table of user id's and months.

Every time the user uploads a photo increment the number in the current month for that user.

Then on the page where they upload photos check the value in the current month to see if it's less than 3. If it is allow the uploads, if it's equal to three display the "you've used up your uploads for this month" message.

If you also include a "max uploads" field associated to each user you could allow some users more uploads and others less if necessary, or just increase the limit if your bandwidth allows.

Answer 2

Untested:

SELECT COUNT(*) FROM photos WHERE user="bob" AND createdAt > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

That query should return the number of rows in the photos table that have been created (according to the createdAt field) in the past month by the user named bob. Compare that to 3 to know whether to allow them to upload something.

Answer 3

What do you mean with "limit by the months"? Do you mean "limit over the last 30 days", or "limit is reset every first of the month"?

In case of the first: just select the number of pictures created by your current user during the last month. If this number is >=3, deny the user the right to upload.

SELECT COUNT(*) FROM <pictures_table> 
WHERE <upload_date_field> >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
AND <user_field> = <current_user>

I should mention you can implement this logic on different layers within your application, but that's up to you.

Answer 4

Use:

  SELECT YEAR(t.ur_date_field) AS yr,
         MONTH(t.ur_date_field) AS month_number,
         COUNT(*) AS num_photos
    FROM UR_TABLE t
GROUP BY YEAR(t.ur_date_field), MONTH(t.ur_date_field)
ORDER BY yr, month_number

...to get:

yr    |  month_number  |  num_photos
--------------------------------
2010  |  1             |  2
2010  |  2             |  1

If there aren't any photos for a given month, it won't show up in the resulset.

From there, you can validate the month in particular.