Ahmed F. Alhamzawi Ahmed F. Alhamzawi - 7 months ago 12
PHP Question

PHP / MysQl limit user posts in month - day - hours

I am working on a php script, I have an admin control panel to add users, and I need to add a few options like user monthly posts - user daily posts - user hourly posts, let's say I set user monthly post to 30 and user daily posts is 10 and user hourly post is 5, that will be:

The user can post only 5 posts per 1 hour and 10 posts per day from the monthly 30 posts limit, if user monthly post is used, he can't add posts in this month and the next month i want to automatically add another 30 posts!!

My user table name is (user):

`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`monthly` int(2) unsigned NOT NULL,
`daily` int(10) unsigned NOT NULL,
`hourly` int(10) unsigned NOT NULL,


And my post table name is
user_post
:

`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`created_dt` datetime NOT NULL,
`user` int(10) unsigned NOT NULL,


I need to know :


  1. how to make the monthly user column updated monthly to 30 if I registered the user with 30 monthly post limit.

  2. when user is logged in and he want to post, how to check if he have more monthly, daily, hourly posts remaining!



Can anyone help me to see how I can do that, thank you my friends

Answer

Add a "datetime posted" column to your "user posts" table. For this example, we'll call the new column `created_dt` (with a dataype of DATETIME. We'll also assume that the name of the table is `user_post`.

When a row is inserted, populate the new column with the current date and time.

When a user attempts to post another row, you could perform a check whether any limit has been exceeded.

 SELECT SUM(1)                                        AS cnt_past_month
      , SUM(p.created_dt >= NOW() + INTERVAL -7 DAY)  AS cnt_past_week
      , SUM(p.created_dt >= NOW() + INTERVAL -1 HOUR) AS cnt_past_hour
   FROM user_post p
  WHERE p.user_id = ?
    AND p.created_dt >= NOW() - INTERVAL 1 MONTH

You can then compare the values returned to the limits for the user, to see if any limits have been exceeded, or would be exceeded if another post is added.

For optimal performance of this query, you will want an index

ON user_post(userid, created_dt) 

You could get the limits for the user within the query itself...

SELECT q.count_past_month
     , m.limit_past_month
     , q.count_past_week
     , m.limit_past_week
     , q.count_past_hour
     , m.limit_past_hour
  FROM ( SELECT p.userid
              , SUM(1)                                       AS cnt_past_month
              , SUM(p.created_dt >= NOW() - INTERVAL 7 DAY)  AS cnt_past_week
              , SUM(p.created_dt >= NOW() - INTERVAL 1 HOUR) AS cnt_past_hour
           FROM user_post p 
          WHERE p.user_id = ?
            AND p.created_dt >= NOW() - INTERVAL 1 MONTH
       ) q
  CROSS
   JOIN ( SELECT MIN(l.limit_per_month)
               , MIN(l.limit_per_week)
               , MIN(l.limit_per_hour)
            FROM user_limit l
           WHERE l.user_id = ?
        ) m

With this approach, you won't need a bunch of unnecessary DML to increment counters, and reset counters. Any change you make to the limits for user would could take effect immediately.

And you could use a value of "0" to specify "no limit". Your logic for doing comparisons would need to take that into account.

That's how I would do it.

I could also do the comparisons of the count to the limit in the query itself, returning the "number of posts remaining" until the limit is exceeded.

SELECT m.limit_past_month-IFNULL(q.count_past_month,0) AS remaining_past_month
     , m.limit_past_week -IFNULL(q.count_past_week ,0) AS remaining_past_week
     , m.limit_past_hour -IFNULL(q.count_past_hour ,0) AS remaining_past_hour
  FROM (

The mechanics of the "ban" (no posts allowed) and "unlimited" (no limits on posts) would need to be worked out. For example using 0 to represent a ban, and a NULL to represent "no limit".

With that, we'd know that when the query returns a column with value less than or equal to zero, it would mean that a limit has been exceeded (or would be exceeded by another post.) All other values (NULL or positive integer) in the column would mean a "next post" would be allowed.

Comments