Page 1 of 1

Retrieve record closest to x

Posted: Wed Aug 19, 2009 9:05 am
by papa
Hello,

Say I have a table with
id | points
1 | 1999
2 | 2000
3 | 3000
4 | 4000

The user submits a point value. Say for example he/she submits 250, how do I match that with my id 1. Or maybe the user submits 3700, then I want id 3 to be fetched and not 4.

How do I go about this?

thanks

edit:

What I got so far works, but if you submit 2800 for example. It fetches id 3 and not 2.

Code: Select all

            $sql = "SELECT ABS(points - $points) as distance, max_tot, lords, heroes, core, special, rare
                FROM a_army_size ORDER BY distance LIMIT 1";

Re: Retrieve record closest to x

Posted: Wed Aug 19, 2009 9:29 am
by lord_webby
How about:
Id use a php switch and forget the sql comparison. Something like:

Code: Select all

 
<?php
$points = 370;
 
switch ($points) {
    case ($points < 2000):
        $i =1;
        break;
    case (1999 < $points < 3000):
        $i =2;
        break;
   case (2999 < $points < 4000):
        $i =3;
        break;
   Default:
        $i =4;
        break;
 
}
?>
 
Then:

Code: Select all

 
 $sql = "SELECT * FROM a_army_size WHERE ID = '$i' ORDER BY distance LIMIT 1";
 
Does that help?

Re: Retrieve record closest to x

Posted: Wed Aug 19, 2009 9:31 am
by papa
Thanks for your answer, though I'd prefer using mySQL for this as I'd need to change in several places if I did an update in the mysql table for example.

Re: Retrieve record closest to x

Posted: Wed Aug 19, 2009 9:36 am
by jackpf
I'm not entirely sure how to do this, but you could subtract the value from the field, and then select whichever one is closest to 0.

So, say the value is 2200, 2000 will become -200 which is closest to 0.

Although, you'll have to make some sort of case for two values equidistant from 0. I suppose you could combine it with MAX() to select the highest value, closest to 0...so it'll round up.

This may not be the best way of doing it...or even possible, but that's all I can think of.

Re: Retrieve record closest to x

Posted: Wed Aug 19, 2009 9:50 am
by pickle
So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?

Re: Retrieve record closest to x

Posted: Wed Aug 19, 2009 11:00 am
by VladSun
Like this:
[sql]SELECT    *FROM    a_army_sizeWHERE     points = COALESCE    (        (SELECT max(points) FROM a_army_size WHERE points < 2700),        (SELECT min(points) FROM a_army_size)    )LIMIT 1[/sql]
?

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 2:40 am
by papa
pickle wrote:So you want to round up if the value is smaller than the smallest value in the DB, and round down otherwise?
Jepp correct.

@Vlad: Unfortunately I get an errror: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 2:49 am
by VladSun

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 2:53 am
by lord_webby
Maybe:

Code: Select all

 
SELECT * FROM army_builder WHERE points < '$points' AND (('$points' - points) < 1000)  ORDER BY ID DESC LIMIT 1
 
But thinking on it, it won't work for any values under 999.

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 3:08 am
by VladSun
papa wrote:@Vlad: Unfortunately I get an error: "FUNCTION army_builder.COALESCE does not exist"
I'm running Mysql 5.
Blah!!!

Remove the new line before the opening bracket after COALESCE like this:

[sql]SELECT    *FROM    a_army_sizeWHERE     points = COALESCE(        (SELECT max(points) FROM a_army_size WHERE points < 2700),        (SELECT min(points) FROM a_army_size)    )LIMIT 1[/sql]

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 3:34 am
by papa
Thanks Vlad!

It works almost perfectly.

id points max_tot lords heroes core special rare
1 1999 3 0 3 2 3 1
2 2000 4 1 4 3 4 2
3 3000 6 2 6 4 5 3
4 4000 8 3 8 5 6


If I put in 2000 points i still display records from id 1. If points is 2001 it works. Same for 3000 and 4000.

Code: Select all

 
$sql = "SELECT
    *
FROM
  a_army_size
WHERE
 points = COALESCE(
(SELECT max(points) FROM a_army_size WHERE points < $points),
(SELECT min(points) FROM a_army_size)
)
LIMIT 1";

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 3:35 am
by VladSun
So ... what do you suggest :P
C'mon - that's too easy!

Re: Retrieve record closest to x

Posted: Thu Aug 20, 2009 3:46 am
by papa
Haha sorry, got lazy :)

<=

Thanks again!