I have a database containing 7 digit numbers. When someone enters a number into a form I'd like to find the 4 closest matches to that number, up or down. Any help for this would be greatly appreciated. It would also be nice to display an error message if the user enters a number that is not 7 digits.

I'm not too good with SQL Yet, but heres my thought to make it as quick as possible.

Lets say the person types 34753

You can always do a

SELECT * FROM TABLENAME WHERE ID >= $variable

make it so it only shows the 4 above it. Because it will be greater then it will find the first items bigger than it. Trying to view the 4 closest that are smaller than it may be trickier though.

And to check if the number is no bigger than 7 digits, thats pretty easy you can just say if($variable > 9999999 or $variable < -9999999)

If its greater than that 999999 then it will be 8 digits, and obviously less than that will be less digits, and same for the negative side.

l3lueMage is definitely on the right track. Assuming the column that is storing the numbers you want to compare against is called 'guess' in the table named 'table', I would probably do something like:

SELECT * FROM `table` WHERE `guess` < $the_number ORDER BY `guess` DESC LIMIT 4;
SELECT * FROM `table` WHERE `guess` >= $the_number ORDER BY `guess` ASC LIMIT 4;

so then you would have (up to) 8 guesses either side of the number. Then for each of those numbers you would subtract the number you are comparing to and get the absolute value of the result. The 4 with the lowest result here are the closest.

I'm no whiz at MySQL, but you may actually be able to do this in MySQL with something like:
SELECT *, ABS(`guess` - $the_number) AS `absolute_value` FROM `table` ORDER BY `absolute_value` ASC LIMIT 4;

Question for twist, cant you just do this:

SELECT * FROM `table` WHERE `guess` < $the_number ORDER BY `guess DESC LIMIT 4 AND ASC LIMIT 4?

l3lueMage, basically, no

I'm 99.9% certain you can't order by ASC and DESC at the same time (there may be a weird thing in MySQL that let's you do it, but it doesn't really make sense). Even if you could, the '`guess` < $the_number' still needs to account for the possibility of guess being GREATER than (or equal to, I assume) the number.

But yeah, I don't think the 'DESC LIMIT 4 AND ASC LIMIT 4' makes sense.

Okay, was just checking :P As I said before I'm new to SQL so just finding out stuff hehe

Thanks for the help. I've got the 4 numbers above and below the user-entered number, but I don't know how to sort out the 4 closest to the user-entered number. I have the difference between the numbers and need to find the 4 with the lowest difference. I put them in an array and used sort() but don't know where to go from there. If I could pull the variable names instead of values out of the array I could use that, or if there's another way I'm all for it.

I would do it in PHP, loop through them in an array, take the closest, pop that, next closest, pop that etc.

UPDATED:
I didn't mean pop, but array_splice could work

I just read up on array_splice() but don't see how it would help me. I see that it can remove elements in an array but I don't see any way to specify removing the four values that have the most difference from the user-entered number or anything like that. Maybe I'm obtuse?

I wasn't very clear, I apologise. What I meant was to construct two arrays, one holding the values themselves, one holding their difference with the set number;

eg. (Target is 1500)

PHP Code:

$numbers[0] = 1497;
if($numbers[0] < 1500){
$differences[0] = 1500 - $numbers[0];
} else{
$differences[0] = $numbers[0] - 1500;
} 


then loop through the array of differences; find the biggest difference:

PHP Code:

$indexof = 0;
$oldlowest = 150000;
for(...){ //all in array
if($differences[i] < $oldlowest) $indexof = i;
} 


then get $numbers[indexof] and set a variable to that as the closest; use array_splice to remove that from the array; then run three mor etimes to find the second, third, and fourth closest.

That should work I think.

Harry

Right. Here is the finished, working code:

PHP Code:

<?php
$target = 1500;
$numbers[0] = 1497;
$numbers[1] = 1400;
$numbers[2] = 1502;
$numbers[3] = 1490;
$numbers[4] = 1450;
for($i = 0; $i < count($numbers); $i++){
if($numbers[$i] < $target){
$differences[$i] = $target - $numbers[$i];
} else{
$differences[$i] = $numbers[$i] - $target;
} 
}
for($a = 0; $a < 4; $a++){
    $indexof = 0;
    $oldlowest = 150000000000000000000;
    for($i = 0; $i < count($numbers); $i++){
        if($differences[$i] < $oldlowest){
            $indexof = $i;
            $oldlowest = $differences[$i];
        } 
    }
    $closest[$a] = $numbers[$indexof];
    array_splice($differences,$indexof,1);
    array_splice($numbers,$indexof,1);
}
echo $closest[0];
echo $closest[1];
echo $closest[2];
echo $closest[3];
?>

That looks to be working perfectly, thanks so much for the help!

Though I am the pinnacle of modesty, I am also quite partial to rewards...

TP is always nice - it did take me a while to think about. You're lucky I did it for free, come to that.

What's a TP?

Talkupation. Just press the "like my post?" button the left hand side of the relevant post.

Oh, I did that the second I read your post

I thank you, my friend

I mean, look at your options - if you went to a professional developer, he wouldn't have done anything for free just to get a bit of TP, would he? Oh no...

I'm curious if the following sql would work:

SELECT * FROM table WHERE $selected_number BETWEEN MIN(id) AND MAX(id) LIMIT 4

i think you should use a for loop like this :

You can subtract each number from the given number and give the minimum result to variable

I'm pretty sure we already had this one solved. Pray read the thread before posting next time gos1...