Displaying Data Page by Page.

Anon

I'm Selecting data from an Oracle DB and Outputting it to the Web Page using WHILE with OCIFetch and FOR to count down the total number of columns and display the Table Data line by line.

The problem is there are more than 10,000 records, and I want them to be displayed one page of 100 at a time, for obvious reasons.

Can any one suggest an efficient way of doing this ?

If you want more details on the code structure please let me know.

Thanks in advance.
Tony

Anon

In MySQL there is a LIMIT statement that lets you specify which of the resulting records you want to get.

I'm sure there is such a statement in OracleSQL too.

Anon

Thxs for the quick reply, but unfortunately I've been down this road to no avail.
There is a 'ROWNUM' OracleSQL command but strangely you can only specify a Starting Range beginning at 0!

If you specify say, 100-200, re-execute the statement you get sweet FA as output.

I'd like to be able to do it within the WHILE loop.

Regards, Tony.

Anon

I'm sure there are people saying search the ><?#@ forum, I have.

The difference between my requirments and otherts posted is that I can't assign all that data to an array, it's a messy way of formating the data anyway.

Much better to do it via the SQL staments, but I can't.

So that leaves.....well I'm not 100% sure, which is why I have asked.

Regards, Tony.

Anon

My Oracle thingy tells me that rownum is a pseudo column that counts how many results you are getting (1 for the first result, 2 for the second etc)

Can't you use that in a sub select?
something like this:

SELECT FROM (SELECT rownum, FROM table) where rownum>100 and rownum<200;

Anon

Good point, but ROWNUM is only evaluated 'after' the data has been retrieved.

It won't process any logical operators because the data isn't available yet.
I've tried it just in SQL+, just in case, very strange behavior if you ask me.

Keep em commin :-)
Regards, Tony.

Anon

Sorry I mean, ROWNUM is NOT evaluated after the data available.

...

Anon

Hi,
One desporate way i found is to fetch the whole records using OCIFetchStatment(). and then displaying only the range of data that we want. the next set of range values should be stored in some hidden fields in your html form.
The fetching code can be like this.

$nrows = OCIFetchStatement($stmt,$results);
if ( $nrows > 0 ) {
print "<TABLE BORDER=\"1\">\n";
print "<TR>\n";
while ( list( $key, $val ) = each( $results ) ) {
print "<TH>$key</TH>\n";
}
print "</TR>\n";

for ( $i = 0; $i < $nrows; $i++ ) {

/***********************************
in this for loop you can set your
range as begining and ending values
**************************************/
reset($results);
print "<TR>\n";
while ( $column = each($results) ) {

$data = $column['value'];
print "<TD>$data[$i]</TD>\n";
}
print "</TR>\n";
}
print "</TABLE>\n";
} else {
echo "No data found<BR>\n";
}

If you need I can come up with a full example
or another SQL query that will fetch a range
..
Regards
Ranjith

Anon

Ok, looks promising, let me work on embedding this code into my existing function, I'll let you know the results.

Thanks Ranjith

Anon

The problem is there are more than 10,000 records

Did I mess where you said whether or not oracle supports the LIMIT clause?
If so, that's all you need. Otherwise, if your data is uniquely ordered by some column, you can use that column to filter the starting point of each successive query, by remembering the last (or highest, if you're not ORDERing the query by the same unique column) value used and using it in a WHERE ID > x statement.

For example, if I didn't have LIMIT available in PostgreSQL, here's how I might code that ($start is passed as a GET variable, and thus is automatically 0 if it's not present--thus the starting page doesn't even need ?start=0 on the URL)

$qry = 'select name, date, id from table where id > $start order by date';

Then perform the query. Initialize a variable $high_id to 0, and as you're retreiving results, update $high_id to the highest ID received. When you've displayed 100 results, break out of the loop, and create a link to the next page:

echo "<a href=\"$PHP_SELF?start=$high_id\">More data</a>";

Anon

ORACLE doesn’t support INDEX.
The data is ordered by Date/Time, but it's not accurate to use this as a key to display 100 records per page, because there are double entries.

So your row-counter in the loop idea sounds good, I'm currently looking at all possibilities and will get back to you all tomorrow, I'm 5 hours ahead of you guys.

Cheers.

Anon

ORACLE doesn’t support INDEX.

Did you perhaps mean LIMIT rather than INDEX?