PHP/SQL: Leveling/XP System for users on a website. How should I store the data? Thinking about sessions

Question

I want users to be able to gain XP (experience points) on my website. After a certain amount they level up.

I need advice on how to handle all the data. To be more exact: the amount of XP and what level that represents.

• The users level is going to be displayed in a bar at the top of every page.
• Maybe even the exact XP
• It should be efficient (not very database heavy)
• The required XP changes every level (the higher the level, the more xp is needed)

I use CodeIgniter for my webiste.
My PHP skills are somewhat between beginner and experienced I guess :)

---

My first thought:

"Database only"
Store the xp in a column in my already existing "user" table. To get the corresponding level I would create another table with 2 columns: Level --- required XP
For example:

1 --- 0
2 --- 100
3 --- 500
etc.

Have to admit that I don't really know how to get the level from that, yet. But I guess it would somehow work. BUT that would mean I have to connect to the DB every time and retrieve data from two tables. I don't think that's a good solution. So here is

My second thought:

"Updated sessions"
Store the XP as session data and create a column for the XP in my "user" table. Everytime the XP changes I would have to update the session data AND the database, but I don't have to access the DB to retrieve the amount of XP. I could do that from the session data. (security issue?! I use CodeIgniters encryption for sessions)

To get the level I would probably add another column to my "user" table that stores the level. Instead of an extra table in the DB that stores the required XP I could use a php function that gets the corresponding level(?). Does that make sense? Would save me the additional db connection.
Something like: get_level($xp)

To update the level I would have to check the level everytime I change the XP. Is that efficient?

How exactly can I get the corresponding level to a known XP number? Can I use PHP switch?

---

As you can see, I have no idea what I'm doing ;P
A lot of thoughts, but nothing seems to be as good as it could be.

Any help/advice is appreciated :)

regards
DiLer

Answer 1

I'd have a separate table which tracks events and their corresponding XP adjustments per-user:

• xp_events
  • event: The event as a string or foreign keyed ID or whatever.
  • user: The user in question.
  • delta: The corresponding XP change.

Then store both the total XP and the current level with your users. Add triggers to xp_events to update the total XP and current level in the user table whenever xp-events are added. I'd recommend against ever altering or removing entries in xp_events, an audit trail is quite useful and you can always undo one event by adding another event.

This approach gives you your summary information pretty much for free so you don't have to do a bunch of extra aggregation to compute a listing page of your users. You also get a nice history in xp_events for auditing or just in case people want to see their progress. As an added bonus, you get an audit trail in xp_events that you can use to regenerate each user's total XP and current level when you have bugs in your triggers.

You'd also want to have the xp-to-level information in the database, your triggers will need it and it can't hurt to have all your data in the database anyway. Something like this:

min_xp | level
-------+------
     0 | 1
   100 | 2
   500 | 3
  1000 | 4
...

Then you can get the level for $xp XP with:

select max(level)
from xp_levels
where $xp - min_xp >= 0

Answer 2

You need to store XP in the database and query it everytime. You can store it in sessions and such, but it just doesn't make a lot of sense other than possibly to act as a short-lived cache... but I doubt you'll notice any performance cost anyway (sessions aren't free either). Very simple and repetitive queries are basically free.

It would probably be a good idea to store Level in the database as well, rather than derive it from XP... but that's up to you. If you don't want to store it, just keep an array with all levels and their XP requirements. Then you simply go through the array until you find the Level that corresponds to the amount of XP the user has.

I find it hard to be more in-depth than this without more specific questions.

But to sum it up, store XP in the database, Level too if you want... and query every time.

PS. Also, if you're somewhat of a beginner, make sure to escape all user data before you put it in the SQL queries! mysql_real_escape_string() ... or you will find some users doing all sorts of things you really don't want them to.

PS2. Forget about performance issues on this level, you just won't have them... if you start doing loops in loops and querying thousands upon thousands of records every request... then you need to double check what you are doing.