in reply to Moving through records

MySQL has a LIMIT command which makes implementing forward/next a breeze. See docs for SELECT:
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

gav^

Replies are listed 'Best First'.
Re: Re: Moving through records
by thecrypto (Scribe) on Mar 07, 2002 at 23:46 UTC
    OK, I see how that works but how could I find the row number of the record I'm looking at?

    -- Security is only as strong as the weakest link
[reply]
      Keep track. Pass a skip parameter and skip that many records with LIMIT skip, records-per-page. For example: 
      # get number to skip over
my $skip = $cgi->param('skip') || 0;

# build SQL statement
$sql .= "LIMIT $skip, 10";

# build up links
$cgi->param('skip', $skip + 10);
my $next_link = (@results >= $10 ? $cgi->self_url : undef);

$cgi->param('skip', $skip - 10);
my $prev_link = ($skip > 0 ? $cgi->self_url : undef);

      [download]
      Hope this helps...

      gav^

[reply]
[d/l]
      In a relational database you normally can't.

      What you do instead is use the values in the columns of the row you currently have and apply them judiciously in a WHERE clause to get the preceding or following row, based on the ORDER BY clause that you specify.

      Remember that a relational database is based on set theory, so rows in a table aren't in a specific position - they are part of a set.

      That said a lot of RDBMS systems have pseudo rownumber indicators (I know that Oracle has this) that you can use. It's possible that MySQL has something similar.

      Michael

[reply]

In Section Seekers of Perl Wisdom