MySQL query: Closest < result from query

Question

Table: cm_rank
|id|rankpoeng|tittel  |
-----------------------
|1 |        0 |Sivil  |
|2 |      322 |Wannabe|
|3 |     1450 |Hitman |

What I'm trying to do:

$currentXp = 500;

$query = mysql_query("SELECT * FROM cm_rank WHERE $currentXp > rankpoeng LIMIT 1");

while ($rows = mysql_fetch_array($query)):
    $levelTitle = $rows['title'];
    echo $levelTitle;
endwhile;

The $levelTitle variable should only hold the text "Wannabe", since the users has more xp than it requires, but not enought for the next rank.

How may I structure up a query like this?

So what defines but not enought for the next rank. ? Is there a bench mark ? — Abhik Chakraborty
– Abhik Chakraborty, Commented May 13, 2015 at 8:48

pavel · Accepted Answer · 2015-05-13 08:48:46Z

1

You should order by rankpoeng column to take the highest valid value.

$query = mysql_query("SELECT * FROM cm_rank WHERE $currentXp > rankpoeng ORDER BY rankpoeng DESC LIMIT 1");

answered May 13, 2015 at 8:48

pavel

27.3k11 gold badges47 silver badges63 bronze badges

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

1 Comment

pavel Over a year ago

@Hansen: and note that you don't need while loop when you have LIMIT 1, use directyl $row = mysql_fetch_array($query);

Jens · Accepted Answer · 2015-05-13 08:50:22Z

1

You have to order the result:

"SELECT * FROM cm_rank WHERE $currentXp > rankpoeng order by rankpoeng desc LIMIT 1"

But you should not use the deprecated mysql_* API. You should use mysqli_*or PDO with prepared statement.

answered May 13, 2015 at 8:50

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

MySQL query: Closest < result from query

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