在现有的PHP / MySQL网站上集成经验值（EXP）？

Question

I have a website which allows members to upload videos, comment on videos, rate videos and post in the forum.

I also have a "hide" feature (for moderation) available to the admin usergroup (which is basically a column called hidden which is a boolean (1 or 0), which I have in each table. For example; if hidden was set to 1 it would not display the content however if it was set to 0 the content would be displayed), this is just a feature to manage abuse.

What I mean when I say each table; is that the forum topics have a seperate table, the video content has its own table etc. - but they all (atleast those which I want the feature to be used on) have something in common they all have a column called hidden (which I explained above on what it does) in each of them.

Now I'm thinking of adding basic EXP (Experience Points) functionality so members can earn X amount of points for doing a specific action e.g. uploading a video, posting a topic in forum, rating a video, commenting on a video etc.

The total EXP will be displayed on the member profiles and also on their activity stream (e.g. Earnt X amount of points for posting a topic).

But I'm unsure as to how to implement this because I'd only want the total EXP to consider content which is not hidden and same with the activity stream (only display it for EXP attained from content which is not hidden).

The dillema for me is due to me having multiple tables containing the different content and that I do regular cron jobs to clean up (delete) hidden content, I'll want the EXP given for each action to be easily changable - so if one day I decide I'm no longer going to be generous an give 50 points to those who post a topic and changed it to 2 all those who created a topic in the past will also be automatically effected (so perhaps having the EXP for a certain action be in a class or config file?).

Summarise:

1. The EXP points are given for certain actions.
2. The EXP points can easily be changed (and all those who recieved the changed EXP previously will be effected automatically).
3. The EXP points take into account the "hidden" feature (so only consider content which is not hidden).
4. Want to make it easy as possible to add new EXP points in the future.
5. Only want a a max of e.g. 4 EXP (regardless of which action) to be given per day (to make it more challenging).

I have an idea of perhaps doing it on the fly (as in not storing the EXP in a DB at all - and just doing COUNT() queries WHERE hidden = 0 and then * it to how much EXP - so if the content was hidden the EXP will automatically be effected (which is how I'd like it) but the downfall would be that it would perhaps mean complex JOINs and the max of 4 per day may be an issue)?

I would appreciate if you could perhaps post on how you would do this (along with an example - if possible), as I've been banging my head for hours.

Appreciate your responses.

Answer 1

I would create a class dedicated to managing a log in the database. I'm thinking the database keeps track of:

• user_id
• points
• reason
• entity
• entity_id
• counted
• timestamp

entity is an identifier for the type of content the points were awarded for, e.g. "video". *entity_id* is the ID number of the entity (e.g. 2 points rewarded for adding video id #24). counted is a boolean that tells your system whether or not to count the points.

When an admin hides anything, be it a video, post, or rating, your log manager looks up the associated points given for the entity, matching the entity_id, and sets the counted field to 0. This lets you easily get a SUM of points for a particular user, but only points that should be counted.

When a user does anything that warrants receiving points, the log manager can find how many points have been awarded for the user on this particular day, and add either the full amount or the difference if the full amount pushes the total above 4.

I haven't fully thought this out, so there may be a flaw in my logic, but that may be enough to get you started.