Next and Previous record

I am moving throught a set of records, my next works great but previous move
to the first record. Also what is the best way to check and see if it is the
first or last record in the set.

if($move == "Next")
{
$next_rec = "select * from people where contactid > $rec_id limit 1";
$results = mysql_query($next_rec);
$row = mysql_fetch_row($results);
include("http://localhost/keith-homes/hyperlink.php?val=$row[0]");
exit;
}
elseif($move == "Previous")
{
$next_rec = "select * from people where contactid < $rec_id limit 1";
$results = mysql_query($next_rec);
$row = mysql_fetch_row($results);
include("http://localhost/keith-homes/hyperlink.php?val=$row[0]");
exit;
}

I understand why it goes to the first record and would like a better way to
navigate backwards through the set. It starts it query at the first record
and the first is less than the current ID so it stops there. Correct.


"Rick" <vfrrick@cox.net> wrote in message
news:xmerb.7185$Zb7.6789@fed1read01...
> I am moving throught a set of records, my next works great but previous
move
> to the first record. Also what is the best way to check and see if it is
the
> first or last record in the set.
>
> if($move == "Next")
> {
> $next_rec = "select * from people where contactid > $rec_id limit 1";
> $results = mysql_query($next_rec);
> $row = mysql_fetch_row($results);
> include("http://localhost/keith-homes/hyperlink.php?val=$row[0]");
> exit;
> }
> elseif($move == "Previous")
> {
> $next_rec = "select * from people where contactid < $rec_id limit 1";
> $results = mysql_query($next_rec);
> $row = mysql_fetch_row($results);
> include("http://localhost/keith-homes/hyperlink.php?val=$row[0]");
> exit;
> }
>
>
>
>
>
>

Mr. Clean, speaking through Rick, wrote:
>
> $next_rec = "select * from people where contactid > $rec_id limit 1";

try:

SELECT * FROM people
WHERE contactid > $rec_id
ORDER BY contactid
LIMIT 1

> $next_rec = "select * from people where contactid < $rec_id limit 1";

SELECT * FROM people
WHERE contactid < $rec_id
ORDER BY contactid DESC
LIMIT 1

/joe
--
In lbbs, El Scorcho's plain radio from Robert Jameson is blue?? Krispy
Kreme is retarded.

On Sat, 08 Nov 2003 16:10:15 -0700, Rick wrote:

> I understand why it goes to the first record and would like a better way
> to navigate backwards through the set. It starts it query at the first
> record and the first is less than the current ID so it stops there.
> Correct.
>
>
You are correct and, if rec_id is the lead column in an index it returns
that first record very, very quickly.

Depending on which RDBMS product you are using Joe's suggestion to use
ORDER BY DESC may not work. The reason is that some RDBMS do the ORDER BY
as the final step in the retrieval and presentation process. If it works
with the RDBMS you are using great. If it doesn't look into using a
subquery whose inner query retrieves the records in descending sequence
with an outer query that does your select less than.

Jerry Gitomer, obviously a huge fan of Mean Ween, wrote:
>
> Depending on which RDBMS product you are using Joe's suggestion to use
> ORDER BY DESC may not work.

I should offer the caveat that all of my suggestions only refer to
MySQL, which is the only one I use.

> The reason is that some RDBMS do the ORDER BY as the final step in the
> retrieval and presentation process.

I don't understand this. Are you saying that some products do the LIMIT 1
before the ORDER DESC? That is ridiculously poor functionality. In such
a system, would the only way to do it (without using PHP arrays/sorting)
be to do a subquery as you suggested? That is terribly inconvenient.

Do you know any specific database products offhand which do this?

/joe
--
Brian McNamara's terrorist cell from gtpj is nauseous and educational in
the Righteous Room. Miotch's processor is blue. David Maynor emasculates
the non-descript memory and the network cable from matlab, and then
interestingly loathes the masturbatory mp3 collection from Sarah Moore.

On Sun, 9 Nov 2003 15:58:22 +0000 (UTC), Disco Plumber <scag@moralminority.org>
wrote:

>> The reason is that some RDBMS do the ORDER BY as the final step in the
>> retrieval and presentation process.
>
>I don't understand this. Are you saying that some products do the LIMIT 1
>before the ORDER DESC? That is ridiculously poor functionality. In such
>a system, would the only way to do it (without using PHP arrays/sorting)
>be to do a subquery as you suggested? That is terribly inconvenient.
>
>Do you know any specific database products offhand which do this?

The closest I know of is Oracle's ROWNUM pseudocolumn, which is assigned
before the ORDER BY, so if you naively use this to emulate LIMIT you'll get the
wrong results.

But ROWNUM is not really the same as LIMIT in the first place so probably not
a fair comparison.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)