How to program an experience-based levelup system where experience carries over to the next level?

I'm new into programming, and i've tried to do a system of level up. Here it is :

if ($exp >=$max_exp)
{
$query=$db->prepare('UPDATE users SET level_up = 1 WHERE username = "'. $username .'"');$query->execute();
$query->CloseCursor(); } if ($level_up >= 1)
{
$query=$db->prepare('UPDATE users SET exp= 0, level = (level + 1 * level_up), max_exp = (max_exp * 1.2), spoint = (spoint +5 * level_up), level_up = 0 WHERE username = "'. $username .'"');$query->execute();
$query->CloseCursor(); } Explaination : • If the user get more xp (exp) than xp needed to level up (max_exp), he get a level up (level_up) • When the user level up, his XP is set to 0, his level goes up by (1 * the number of times he leveled up), same thing for his spoint (Special points). And next, his xp needed to level up goes up by 1.2. Here are the problems with my badly coded system : • If max_exp is 100 and the user get 1000 xp, he will only get 1 level and loose the 900 xp because when he level up, the xp go down to 0. Any way to fix this ? • If the user level up twice, the max_exp will only multiply once by 1.2, so if a user is level 1 and level up to 3, he will get the max_exp of a level 2 user. • If there is another way than doing it without the level_up, i will gladly take it. Thanks for the help ! • I'm not that into sql, but can't you just set$exp = $exp -$max_exp and then repeat until the first if isn't fulfilled? – PSquall Jul 22 '17 at 11:30

Solution A: Don't store the level or the experience points from the last levelup. Only store the total number of experience points the player collected from the start of the game. When you want to know the character's level, calculate it from the experience.

Solution B: Replace exp= 0 with exp = exp - max_exp. That way excess experience will be carried over to the next level. If you want one call to this code section to result in more than one levelup, you need to replace the if ($exp >=$max_exp) with a while ($exp >=$max_exp) loop, so you do levelup events until the experience points are used up:

You should avoid doing that many SQL updates. Each UPDATE requires a network roundtrip to the database and a search query on the database server. Those latency times add up! Do everything you need to do with the player-object in your PHP code and then do one UPDATE with all the changed fields when you are finished.

This is how I would have written this:

$hadLevelup = false; while ($exp >= $max_exp) { // update level$exp = $exp -$max_exp;
$level =$level + 1;
$max_exp =$max_exp * 1.2;

// update player stats
$spoint =$spoint + 5;

$hadLevelup = true; } if ($hadLevelup) {
// persist the character to the database
$query=$db->prepare('UPDATE users SET exp= (1), level = (2), max_exp = (3), spoint = (4) WHERE username = (5)');
$query->bindParam(1,$exp);
$query->bindParam(2,$level);
$query->bindParam(3,$max_exp);
$query->bindParam(4,$spoint);
$query->bindParam(5,$username);
$query->execute();$query->CloseCursor();
}

By the way: Another thing you might notice is that I am using placeholders and bindParam instead of just concatenating strings to an SQL statement. The reason for that is that if you don't use placeholders, you miss out on the main reason why PHP deprecated the old $mysql_ API and introduced the new$mysqli API with its prepared statements: What if I name my character "; DROP TABLE users?

• Hi, thanks for you answer. First of all, you can't name your character and you can't edit it , but thanks for the warning :) My problem with your code (Which seems way better than mine) is that i got "undefined variable" when i create my account. He just don't know what is $hadLevelup. – Eleyox Jul 22 '17 at 12:25 • @Eleyox It helps if you set$hadLevelup to false before the start. Sorry, I didn't write any PHP in a long time. – Philipp Jul 22 '17 at 12:54
• Ok so i'm sorry for the late answer, but it seems i have a problem : the while can't be stopped, as the if is happening after i guess, and then the while is never stopping, making the website crash. – Eleyox Jul 22 '17 at 15:59
• @Eleyox there might be an infinite loop if max_exp is zero or negative. Please make sure that's never the case (because it makes no logical sense). If you still have problems, please do some proper debugging effort to pinpoint the problem. – Philipp Jul 22 '17 at 16:08
• Thanks, i resolved the problem (my fault). The problem is that it does not level up. I can't really do some debugging effort as i have no clues and it shows no error on this, i am really sorry. If you want some informations, i have a button who gives 200 xp on click. On level 1, the max_exp is set to 100. When i click, the xp is 200/100, and it goes 400/100, 600/100... I think it's maybe because the \$hadLevelup = true; is only true in the "while", but i don't know how to fix it properly.. – Eleyox Jul 22 '17 at 16:22