I am making a ranking system which checks if the user's XP meets a certain level then updates the database with their new rank. Here is my code currently.
<?php
session_start();
$servername = "localhost";
$username = "USERNAME";
$password = "PASSWORD";
$dbname = "DB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$id=$_SESSION['user']['id'];
$sl = "SELECT * FROM users WHERE id='$id'";
$reult = $conn->query($sl);
while($ro = $reult->fetch_assoc()) {
if($ro['xp']>2000){
$sql = "UPDATE users SET rank='2' WHERE id='$id'";
if ($conn->query($sql) === TRUE) {
echo"Success";
}
} //ENDS THE IF
elseif($ro['xp']>"5000"){
$sel = "UPDATE users SET rank='3' WHERE id='$id'";
if ($conn->query($sel) === TRUE) {
echo"Success";
}
}//ENDS ELSEIF
}//WHILE LOOP ENDS
$conn->close();
?>
But PHP wont allow the elseif's to keep running if the first if is met. Is ther a better way to do this in PHP?
Just change the order you use. 1st check if the XP is > 5000, then 2000.
Just add another condition in the if :
if ($ro['xp'] > 2000 && $ro['xp'] <= "5000")
You're actually bringing back more data than you actually need (you're only using the xp column that you select). So your statement:
$sl = "SELECT * FROM users WHERE id='$id'";
can actually be reduced to the following:
$sl = "SELECT xp, rank FROM users WHERE id = '$id' AND xp > 2000";
(In general, using SELECT * is not a best practice.)
The AND clause is there because you don't need to update users with xp of 2000 or less -- so no need to retrieve them!
Why am I getting rank? Well, I'm also going to suggest you restructure your if:
while ($ro = $reult->fetch_assoc()) {
$newrank = 2; // This is the default since any user retrieved will have xp > 2000
if ($ro['xp'] > 5000) { // N.B. you have "5000" in quotes here - remove them!
$newrank = 3;
}
if ($newrank != $ro['rank']) {
// Update the user - no need to update if existing rank same as new!
$sql = "UPDATE users SET rank='$newrank' WHERE id='$id'";
if ($conn->query($sql) === TRUE) {
echo"Success";
}
}
} // end while
P.S. There is also the option of updating the users all at once rather than doing it user-by-user:
UPDATE users
SET rank = CASE WHEN xp > 5000 THEN '3' ELSE '2' END
WHERE xp > 2000;
It might be a good idea, in this vein, if you had a separate table of ranks and XP values:
RANKS
--------------------
rank min_xp max_xp
1 0 2000
2 2001 5000
3 5001 etc.
Then you can do the following:
UPDATE users u JOIN ranks r
ON u.xp >= r.min_xp
AND u.xp <= r.max_xp
SET u.rank = r.rank;