Finding a row with greater or equal value to the given number (mysql)

Question

I'm creating a rank system and ranks are stored in a database, my problem is about finding rows with greater or equal value to given user xp:

It's something like this:

list of ranks in ranks table:

$xp = "500";

$find = $db->query("SELECT * FROM ranks WHERE exp >= '$xp' LIMIT 1");

if($find->num_rows > 0){
 // if a rank with greater than or equal value was found
  $rdata = $find->fetch_assoc();
 // return rank name
  echo $rdata["name"];
}

this returns Rank1, i don't know why :(

Can anyone help me? I'm not yet good in mysql.

if the exp is numeric you should avoid the single quotes around $xp in query and assing a numeric value to $xp — ScaisEdge
– ScaisEdge, Commented Mar 7, 2017 at 18:30

clearshot66 · Accepted Answer · 2017-03-07 19:12:19Z

1

Remove the " " around 500, but for future reference I'd turn this into a prepared statement as your query isn't as secure as it could be

$xp = 500;

$query = "SELECT name FROM ranks WHERE exp >= ?' LIMIT 1";
$stmt = $db->prepare($query);
$stmt->bind_param("s",$xp);
$stmt->execute();
$stmt->bind_param($name);
while($stmt->fetch()){
   if($stmt->num_rows > 0 ){
      echo $name;
    }
}

Another suggestion. Instead of selecting where >= $xp and limiting by 1, Why not select the range you're actually looking for? between 500 and 700 so it doesn't bring back 700?

edited Mar 7, 2017 at 19:12

answered Mar 7, 2017 at 18:33

clearshot66

2,2821 gold badge10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

clearshot66 Over a year ago

I didn't remove the ) at the end of the query line. Come on you should've picked up on that :P

Peter B · Accepted Answer · 2023-06-06 14:07:00Z

0

You did put single quotes around $xp in the query, but it is probably a numeric value, not a string. Now you are probably comparing a numeric value with a string. Try:

$find = $db->query("SELECT * FROM ranks WHERE exp >= $xp LIMIT 1");

edited Jun 6, 2023 at 14:07

answered Mar 7, 2017 at 18:31

Peter B

1,6591 gold badge17 silver badges31 bronze badges

Collectives™ on Stack Overflow

Finding a row with greater or equal value to the given number (mysql)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related