PHP / SQL：网站用户的Leveling / XP系统。 我该如何存储数据？ 考虑会议

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

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.