CODEWITHSUNDEEP
phpmysql
Storm Spirit
Storm Spirit

Reputation: 1520

How to lookup range value from database

How to find a value based on a range stored in the database.

My friend_levels table:

enter image description here

and on my profile table:

enter image description here

When I have friend_points = 1, I will get Normal Rate, then if I had friend_points = 180 I will get Great Friend, so it's basically like this on programming

if($profile_points >= 0 && $profile_points < 50) {
    return 'Normal Rate';
} else if($profile_points >= 50 && $profile_points < 100) {
    return 'Friend';
} else if($profile_points >= 100 && $profile_points < 150) {
    return 'Good Friend';
}....

my question too is does it possible on QUERY? or I just make it on the PHP?

EDIT1: Is there a way to get the next target value? For ex. If I'm on the Friend rate with 68 points how to get the 100 = Good Friend ? nevermind the substraction, I just want to get the next row.

enter image description here

Upvotes: 1

Views: 143

Answers (3)

symcbean
symcbean

Reputation: 48357

The CASE method proposed by Sagi works, but means that you need to update your code (SQL in this case rather than PHP) if you want to change your ranges.

Safin's method is only part of a solution - in order to find the corresponding description, Safin's query needs to be embedded in a select with joins/subselects as per Sagi's update, but this is somewhat inefficient.

Personally I wouldn't use aggregation for this:

SELECT user_id, 
(SELECT fl.name
   FROM friends_level fl
   WHERE fl.points_needed<profiles.friend_points
   ORDER BY fl.point_needed DESC
   LIMIT 0,1) as level
FROM profiles

Upvotes: 0

sagi
sagi

Reputation: 40481

If I understood you correctly, you can use CASE EXPRESSION like this:

SELECT id,user_id,
       case when friend_points between 0 and 49 then 'Normal rate'
            when friend_points between 50 and 99 then 'Friend'
            when friend_points between 100 and 149 then 'Good friend'
            .......
       end as 'Friend_Status'
FROM profile

EDIT:

Or, if this names can change dynamicly then with a join:

SELECT t.id,t.user_id,s.name
FROM profile t
INNER JOIN friend_levels s ON(t.friend_points >= s.points_needed)
WHERE s.points_needed = (select min(f.points_needed)
                         from friend_levels f
                         where t.friend_points >= f.points_needed)

Upvotes: 2

safin chacko
safin chacko

Reputation: 1390

next smallest point

SELECT MAX (DISTINCT points_needed) 
FROM friend_levels
WHERE points_needed < =$profile_points;

next highest point 

SELECT MIN (DISTINCT points_needed) 
FROM friend_levels
WHERE points_needed > $profile_points;

Upvotes: 0

Related Questions