Add row in one table for every nth entry in another table

Question

Basically what I have is a system where users get rewarded for every 10 comments. So, in the db, one table captures the comments, and the other a reward for every 10th comment added. For instance, if a user has added 40 comments, they will have 4 rewards/4 rows in table 2.

It sounds so simple to me, but I can't seem to figure it. Thought maybe a modulus operator in there could help, but i need to add a new row in table 2 each time, not overwrite, or rewrite the rows each time.

Help appreciated; I've a feeling it's right in front of me!

Answer 1

You will have to count the number of comments added by the user every time he/she posts a comment. If the comment%10 == 0, then you will have to add a new row in your table 2 (for the reward).

Answer 2

• insert
• select count() the comments for one user
• if number_of_comments % 10 == 0 --> do insert in rewards-table

that's the way I should do it.

Answer 3

Are the rewards unique such that you actually have to track them in a separate table? Or can you just count the number of comments a user has made when you need the number?

In the event that you do need to track the rewards in a separate table, consider a trigger:

create trigger insert_trigger after insert on comments_table
for each row begin
    if (select count(*) from comments_table where user_id = new.user_id) mod 10 = 0 then
        insert into rewards_table (user_id) values (new.user_id);
    end if;
end;

If you allow deletions from the comments table, you would also need a corresponding on delete trigger on the comments table that tests whether there are still the correct number of rewards for a user, and if not, you'd have to decide which to remove.

Answer 4

i am guessing you have a model for the first table, or at least a function that does the insert. You cand some more functionality to that function, and do something like:

$current = count_your_current_coments();
if (fmod($current, 10) == 0) {
    insert_into_second_Table();
}

Answer 5

My idea is that you need to make additional field as flag. You can name it rewarded which is enum. 'Y' for Yes or 'N' for No. Each time you insert a comment, you can save a default 'N' and check DB.

SELECT count(my_comment) FROM comments WHERE rewarded = 'N';

Use modulo to check if it reaches 10 comments. If yes, insert a row to the other table and update rewarded field with 'N' to 'Y'