[SOLVED] PHP Mysql query help

[tqla]

Hello.

 

I know that I can find the MIN and MAX values of a field by using this:

SELECT MIN(field) AS minfield FROM table
SELECT MAX(field) AS maxfield FROM table

 

But how can I find out the NEXT highest or next lowest number?

 

For example, if my field has numbers like 2, 3, 5, 7, 9. I want to be able to look at the row with number 5 and determine the value of the the next highest and lowest number.  Which in this case would be 7 and 3. BUT I don't need to know exactly what the number is, I just need to know it so I can add or subtract 1 to it.

 

Anybody know how? Thanks.

[revraz]

You can ORDER BY field, then just cycle through the array.  If you only want say the top 5, just do a limit 5.

[tqla]

Thanks revraz.

 

So by doing this:

$thisposition= mysql_query("SELECT position FROM table WHERE id = $ID;");
$thisposition = mysql_fetch_array($thisposition);

$allpositions= mysql_query("SELECT position FROM table");
$allpositions = mysql_fetch_array($allpositions);

 

Now I know the position number of the current row id.

 

I also now know the position numbers of all of the other rows.

 

But now, how do I use what I know to find the next position number higher and add 1 to it?

[void]

it's easier than it looks.

ORDER and LIMIT commands will do the trick.

 

SELECT id FROM table ORDER BY id DESC LIMIT 1,1 # 2nd biggest id

SELECT id FROM table ORDER BY id ASC LIMIT 1,1 # 2nd lowest id

[tqla]

Thanks Void.

 

I'm trying to wrap my head around this but my code aint working

 

$ID = $_GET['id'];
$higherposition= mysql_query("SELECT position FROM table WHERE id = $ID ORDER BY DESC LIMIT 1,1");
$lowerposition= mysql_query("SELECT position FROM table WHERE id = $ID ORDER BY DESC LIMIT 1,1");

echo $higherposition;
echo '<br>';
echo $lowerposition;

 

Nothing is echoing?

??? ??? ??? ??? ???

 

 

 

[DeanWhitehouse]

What are you expecting it to echo exactly?

 

That is just the query to select the rows, once selected they need to be retrieved.

[tqla]

For example, if my field has numbers like 2, 3, 5, 7, 9. I want to be able to look at the row with number 5 and determine the value of the the next highest.  Which in this case would be 7. BUT I don't need to know exactly what the number is, I just need to know it so I can add or subtract 1 to it and make that number the new value for the current field.

[DeanWhitehouse]

First off that was a rhetorical question and second off did you read the last bit of my reply.

[tqla]

Hello Blade, yeah I read the second part but I'm still lost.  The LIMIT advice was not thorough enough and has me confused.

[DeanWhitehouse]

use something like mysql_fetch_assoc(); to get the data in the rows

[tqla]

got it. it's showing the second highest or the second lowest numbers in the field but I am looking for the number that's immediately lower than the current number. and also the one that is immediately higher. hmmmm.

[DeanWhitehouse]

then do

$ID= 1;

 

$higherposition= mysql_query("SELECT position FROM table WHERE id = ($ID+1) ORDER BY DESC LIMIT 1,1");

$lowerposition= mysql_query("SELECT position FROM table WHERE id = ($ID-1) ORDER BY DESC LIMIT 1,1");

[tqla]

Got it! Thanks people. You all put me on the right path. SOLVED!

 

$ID = $_GET['id'];
$query = mysql_query("SELECT field FROM table WHERE id = $ID;");
$row = mysql_fetch_assoc($query);
$thisfield= $row['field'];
//higher
$query= mysql_query("SELECT field FROM table WHERE field > $thisfield LIMIT 1");
while($row = mysql_fetch_assoc($query)) {


echo '<br>';
echo $row['field];
echo '<br>';


}

[tqla]

ooops. don't forget ORDER BY DESC or ASC just before the LIMIT 1