finding records relative to the primary key

jennyp

To enable 'previous' and 'next' arrows on a page, I need a way to query my database and find the two records that have next lowest and the next highest value of the primary key field "primaryid".

I realize that these values won't necessary be the current record "-1" or "+1", since after deletions a newly created record doesn't have just the next value up from the existing highest value for the primary key field.

If it helps, the database has another unique field "uniqueid"m apart from the primary key field, with random but unique 6-digit numbers. A variable ("$CurrentUnique") is already set on the page for the current record of this "uniqueid" field. So up to now all I have to identify the current record (being displayed on the page) is:

$CurrentRecord = mysql_query("SELECT * FROM mytable WHERE uniqueid='$CurrentUnique'");

What I want to do is to make a query to select (values from) the records with the next lowest and next highest values of the primary key fields, relative to this current record (if they both exist, of course, since one may by chance be the lowest or highest value), and to assign their values to variables.

jheitz

There are several ways to do this. One such way (not extremely efficient way but very quick and easy to understand) would be to do the following:

SELECT * FROM mytable WHERE uniqueid= (SELECT MIN(uniqueid) FROM mytable WHERE uniqueid > $CurrentUnique)

and

SELECT * FROM mytable WHERE uniqueid= (SELECT MAX(uniqueid) FROM mytable WHERE uniqueid < $CurrentUnique)

jennyp

I think I confused my question by mentioning the "uniqueid" field - I shouldn't have mentioned this field, since it has no relation to any other record. So I didn't word my question too well - sorry !

Basically, in the page I can retrieve the value of the currently displayed record's primary key field (the field is called "id", and the current value is in a variable called "$CurrentPrimaryKey"), but I need to find the 2 records that have the next lowest and next highest values of this primary key "id" field.

(This retrieval should also be limited to a certain field, so it should also incorporate the use of "WHERE category='$Cat'", where $Cat is the variable corresponding to the category field that I'm narrowing the search down to).

So can I use your suggestion but using my $CurrentPrimaryKey?

Hope this makes sense!

HeyRay2

Next lowest:

SELECT id FROM mytable WHERE id < $CurrentPrimaryKey AND category = '$Cat' ORDER BY id LIMIT 1

Next highest:

SELECT id FROM mytable WHERE id > $CurrentPrimaryKey AND category = '$Cat' ORDER BY id LIMIT 1

jennyp

Thankyou - looks promising, except the way I set it up only the "next highest" works... the "next lowest" always returns 1.

Given $CurrentPrimaryKey,

$Lower = mysql_query("SELECT id FROM mytable WHERE id < $CurrentPrimaryKey AND category = '$Cat' ORDER BY id LIMIT 1 ");

$LowerValue = mysql_result($Lower,0,"id");

$Greater = mysql_query("SELECT id FROM mytable WHERE id > $CurrentPrimaryKey AND category = '$Cat' ORDER BY id LIMIT 1 ");

$GreaterValue = mysql_result($Greater,0,"id");

jennyp

Well I figured out a way to get the lesser "previous" value ("$LessThanValue") by this tortuous route getting the index number of the array:

$LessThanValueArray = mysql_query("SELECT id FROM mytable WHERE id < $CurrentPrimaryKey AND category = '$Cat' ORDER BY id");

$LessNum=mysql_num_rows($LessThanValueArray);

if ($LessNum > 1)
{
$LessIndex = ($LessNum-1);
}

if ($LessNum == 1)
{
$LessIndex = 0;
}

if ($LessNum >= 1)
{
$LessThanValue = mysql_result($LessThanValueArray,$LessIndex,"id");
}

if ($LessNum == 0)
{
$LessThanValue = 'none';
}

Now the greater "next" value..?!

jennyp

Oh well - an equally inelegant solution for the "greater" or "next" value:

$GreaterThanValueArray = mysql_query("SELECT id FROM mytable WHERE id > $CurrentPrimaryKey AND category = '$Cat' ORDER BY id");

$GreaterNum=mysql_num_rows($GreaterThanValueArray);

if ($GreaterNum >0)
{
$GreaterThanValue = mysql_result($GreaterThanValueArray,0,"id");
}

if ($GreaterNum == 0)
{
$GreaterThanValue = 'none';
}

I'm sure there's a better solution, but it seems to work. Thanks for the tips.

Roger_Ramjet

jennyp wrote:
Thankyou - looks promising, except the way I set it up only the "next highest" works... the "next lowest" always returns 1.

That is because you, or HeyRay, forgot to REVERSE the order so that the highest id is listed first instead of the lowest.

Given $CurrentPrimaryKey,

$Lower = mysql_query("SELECT id FROM mytable WHERE id < $CurrentPrimaryKey AND category = '$Cat' ORDER BY id DESC LIMIT 1 ");

$LowerValue = mysql_result($Lower,0,"id");

Now that the record id you want.