Here's what I'm using to do paged queries in Oracle:
$min = minimum of range of records
$max = maximum of range of records
$field_list = the fields from the table separated by commas
$table = the table from where you're selecting
$where_clause and $order_by should be self-explanatory
SELECT linenum, $field_list
FROM (SELECT rownum AS linenum, $field_list
FROM (SELECT $field_list
FROM $table
WHERE $where_clause
ORDER BY $order_by))
WHERE linenum BETWEEN $min AND $max;
I hope it helps,
Anthony Carlos
-----Original Message-----
From: Graeme Merrall [mailto:[EMAIL PROTECTED]]
Sent: Sunday, August 19, 2001 7:34 PM
To: Cynic
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] oracle (oci8) intro
Quoting Cynic <[EMAIL PROTECTED]>:
> Hi there,
>
> I'm in a situation where I need to produce a small app
> on top of an Oracle server really quickly. I'm quite a
> seasoned developer, but have only experience with MySQL
> so far. It's my understanding that Oracle lacks the
> MySQL's "LIMIT" feature. Looking at the OCI section of
> the PHP manual, it also looks like there's no
> OCIDataSeek() or some equivalent. Since the app I need
> to build will be a standard report builder with paging,
> I need this functionality. What is the common way to
> achieve this? Always fetch all rows, cycling through the
> resultset, discarding the records that preceed the one
> I want to start displaying with, and quit when I reach
> the one where the page should end?
>
> Is there a PHP + OCI tutorial somewhere?
>
> I need an intro to Oracle, and I need it now. :(
Thies has an Oracle/PHP tutorial online at http://conf.php.net/ which may be
of
some assitance.
The LIMIT problem is a real bitch is Oracle. There are a few ways to get
around
it, the most obvious people use being ROWNUM. However, ROWNUM does not
listen
to sorting which makes life amusing.
One option is to try a query like the following:
"SELECT * FROM (SELECT field1, field2 FROM table WHERE id>10 ORDER BY field1
DESC) WHERE ROWNUM<11"
which gives you 10 rows, but still leaves the question of paging behind
unless
you use between values. I can't say I've tried paging record sets though.
Cheers,
Graeme
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
