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:

SELECT COUNT(*) FROM `images` WHERE `user_name` = ? GROUP BY MONTH(`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...

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,user_id INT NOT NULL
,date DATE NOT NULL
);

INSERT INTO my_table (user_id,date)
SELECT 1
     , '2018-01-01'
  FROM (SELECT 1) x
  LEFT
  JOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUP
            BY user_id
             , ym
        HAVING COUNT(*) >=3
       ) y
    ON y.user_id = 1
   AND y.ym = DATE_FORMAT('2018-01-01','%Y-%m')
 WHERE y.user_id IS NULL;
 LIMIT 1;

SELECT * FROM my_table;
+----+---------+------------+
| id | user_id | date       |
+----+---------+------------+
|  1 |       1 | 2018-01-01 |
+----+---------+------------+

INSERT INTO my_table (user_id,date)
SELECT 1
     , '2018-01-02'
  FROM (SELECT 1) x
  LEFT
  JOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUP
            BY user_id
             , ym
        HAVING COUNT(*) >=3
       ) y
    ON y.user_id = 1
   AND y.ym = DATE_FORMAT('2018-01-02','%Y-%m')
 WHERE y.user_id IS NULL
 LIMIT 1;

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

INSERT INTO my_table (user_id,date)
SELECT 1
     , '2018-01-02'
  FROM (SELECT 1) x
  LEFT
  JOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUP
            BY user_id
             , ym
        HAVING COUNT(*) >=3
       ) y
    ON y.user_id = 1
   AND y.ym = DATE_FORMAT('2018-01-02','%Y-%m')
 WHERE y.user_id IS NULL
 LIMIT 1;
Query OK, 1 row 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 |
+----+---------+------------+

INSERT INTO my_table (user_id,date)
SELECT 1
     , '2018-01-03'
  FROM (SELECT 1) x
  LEFT
  JOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUP
            BY user_id
             , ym
        HAVING COUNT(*) >=3
       ) y
    ON y.user_id = 1
   AND y.ym = DATE_FORMAT('2018-01-03','%Y-%m')
 WHERE y.user_id IS NULL
 LIMIT 1;
Query OK, 0 rows 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 |
+----+---------+------------+

INSERT INTO my_table (user_id,date)
SELECT 1
     , '2018-02-03'
  FROM (SELECT 1) x
  LEFT
  JOIN (SELECT user_id
             , DATE_FORMAT(date,'%Y-%m') ym
          FROM my_table
         GROUP
            BY user_id
             , ym
        HAVING COUNT(*) >=3
       ) y
    ON y.user_id = 1
   AND y.ym = DATE_FORMAT('2018-02-03','%Y-%m')
 WHERE y.user_id IS NULL
 LIMIT 1;
Query OK, 1 row 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 |
+----+---------+------------+