How To Restrict User To Upload Only Three Images In A Month Using Php/mysql

I am working on PHP. My question is How can restrict a user can upload only three images in a month. My Mysql Database table -- CREATE TABLE `images` ( `id` int(40) NOT NULL,

Solution 1:

The basic idea is to count all images uploaded in the given month by given user, like:

SELECTCOUNT(*) FROM `images` WHERE `user_name` = ? GROUPBYMONTH(`date`);

If the query above returns 3, you can prevent the upload.

Solution 2:

@fabrik solution is the simplest approach, but it has a cave-eat of performance decay when dealing with a lot of images/users.

If you dont mind to dig a bit more into the solution, I would suggest adding image_upload_credit field to user table. Every time user uploads the image you would reduce the credit by 1.

Since you already have access to User object at the time of upload (or should have), the complexity of insert is O(1).

To finish up the solution you need to write a cron (periodically run) task which resets the image_upload_credit on the first day of each month with UPDATE user SET image_upload_credit = 3 WHERE image_upload_credit <> 3;

Solution 3:

Consider the following...

CREATETABLE my_table
(id SERIAL PRIMARY KEY
,user_id INTNOTNULL
,dateDATENOTNULL
);

INSERTINTO my_table (user_id,date)
SELECT1
     , '2018-01-01'FROM (SELECT1) x
  LEFTJOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUPBY user_id
             , ym
        HAVINGCOUNT(*) >=3
       ) y
    ON y.user_id =1AND y.ym = DATE_FORMAT('2018-01-01','%Y-%m')
 WHERE y.user_id ISNULL;
 LIMIT 1;

SELECT*FROM my_table;
+----+---------+------------+| id | user_id |date|+----+---------+------------+|1|1|2018-01-01|+----+---------+------------+INSERTINTO my_table (user_id,date)
SELECT1
     , '2018-01-02'FROM (SELECT1) x
  LEFTJOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUPBY user_id
             , ym
        HAVINGCOUNT(*) >=3
       ) y
    ON y.user_id =1AND y.ym = DATE_FORMAT('2018-01-02','%Y-%m')
 WHERE y.user_id ISNULL
 LIMIT 1;

SELECT*FROM my_table;
+----+---------+------------+| id | user_id |date|+----+---------+------------+|1|1|2018-01-01||2|1|2018-01-02|+----+---------+------------+INSERTINTO my_table (user_id,date)
SELECT1
     , '2018-01-02'FROM (SELECT1) x
  LEFTJOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUPBY user_id
             , ym
        HAVINGCOUNT(*) >=3
       ) y
    ON y.user_id =1AND y.ym = DATE_FORMAT('2018-01-02','%Y-%m')
 WHERE y.user_id ISNULL
 LIMIT 1;
Query OK, 1row affected (0.00 sec)

SELECT*FROM my_table;
+----+---------+------------+| id | user_id |date|+----+---------+------------+|1|1|2018-01-01||2|1|2018-01-02||3|1|2018-01-02|+----+---------+------------+INSERTINTO my_table (user_id,date)
SELECT1
     , '2018-01-03'FROM (SELECT1) x
  LEFTJOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUPBY user_id
             , ym
        HAVINGCOUNT(*) >=3
       ) y
    ON y.user_id =1AND y.ym = DATE_FORMAT('2018-01-03','%Y-%m')
 WHERE y.user_id ISNULL
 LIMIT 1;
Query OK, 0rows affected (0.00 sec)

SELECT*FROM my_table;
+----+---------+------------+| id | user_id |date|+----+---------+------------+|1|1|2018-01-01||2|1|2018-01-02||3|1|2018-01-02|+----+---------+------------+INSERTINTO my_table (user_id,date)
SELECT1
     , '2018-02-03'FROM (SELECT1) x
  LEFTJOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUPBY user_id
             , ym
        HAVINGCOUNT(*) >=3
       ) y
    ON y.user_id =1AND y.ym = DATE_FORMAT('2018-02-03','%Y-%m')
 WHERE y.user_id ISNULL
 LIMIT 1;
Query OK, 1row affected (0.00 sec)

SELECT*FROM my_table;
+----+---------+------------+| id | user_id |date|+----+---------+------------+|1|1|2018-01-01||2|1|2018-01-02||3|1|2018-01-02||4|1|2018-02-03|+----+---------+------------+