RE: [PHP-DB] oracle (oci8) intro

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:graeme@inetix.com.au] Sent: Sunday, August 19, 2001 7:34 PM To: Cynic Cc: php-db@lists.php.net Subject: Re: [PHP-DB] oracle (oci8) intro Quoting Cynic <cynic@mail.cz>: > 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: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net