Promoting users to a new role after xx number of points (user-points module)

Anne

I work with Neil and he has asked me to give a brief run down on our user promotion triggers.

First, you need to bear in mind that this will likely not be a workable solution for you unless you are on a dedicated server. Hardly any shared server providers will allow the user of triggers. Second, I don't know how well acquainted you are with the database tables but it will help if you have been interacting with them so you are familiar with the schema. It isn't necessary but helpful.

All of our tables are prefixed dru_ and I have used these names in the description below. Replace the prefix with your own.

In essence what I did was to add a new field to the dru_users table ('NumPosts' - mediumint(9)) which stores a snapshot of the number of posts made by a member.

I added a couple of triggers to the dru_forum and dru_comments tables which increments this value each time a post is made. A final trigger updates the dru_users_roles table with a new role once the NumPosts value reaches the magic number. We have in fact set up multiple user roles this way (something like Junior, Senior, Guru etc) based upon different numbers of posts.

One thing to bear in mind is that posts can be deleted/unpublished and so the triggers take the status into account. Each time a post is unpublished or deleted the NumPosts is decremented by 1. Once the NumPosts falls below the magic numbers mentioned previously the user roles are removed. Similarly an unpublished post becoming published will increment the NumPosts value.

We have used triggers in multiple areas of the database to automate lots of things. They are an often overlooked feature within MySQL which when used properly can be very powerful.

If you need further info or the trigger code feel free to drop me a PM.

Regards,
Paul