This code is for Oracle, I use it with Oracle 8i...
Just offerring this up for anyone who is interested....
This code will allow you to set an offset, limit, and the 'order by' column. It uses Dynamic SQL to allow the ORDER BY clause to work (thus the quotes around most of the statement). The code itself below is actually a PL/SQL package, so you will need to use a cursor in your PHP code to retrieve results.
The offset works as n+1... that is, if you pass in 0 it will start at row 1. The upper end of the results is determined as offset+limit or the end of the resultset, whichever comes first.
My thanks go out to the people on dbasupport.com for helping me to get this working.
Assuming you know how to use cursors, you can call this from PHP and it will work, I use it every day (well, the people using the intranet here at work do).
Cheers,
Keith.
==== begin package =====
CREATE OR REPLACE PACKAGE aPkg AS
TYPE return_cur IS REF CURSOR;
PROCEDURE getLimResults(p_offset IN NUMBER,p_lim IN NUMBER,p_order IN VARCHAR,p_cur IN OUT return_cur);
END aPkg;
/
CREATE OR REPLACE PACKAGE BODY aPkg AS
PROCEDURE getLimResults(p_offset IN NUMBER,
p_lim IN NUMBER,
p_order IN VARCHAR,
p_cur IN OUT return_cur) IS
BEGIN
OPEN p_cur FOR
'SELECT outer.*
FROM( -- outer
SELECT inner.*,ROWNUM AS rn
FROM( -- inner
SELECT acol_id,
acol_name,
acol_desc
FROM acol_table
ORDER BY '||p_order||'
) inner
WHERE ROWNUM <= :lim + :offset
) outer
WHERE outer.rn > :offset'
USING
p_lim,
p_offset,
p_offset;
END getLimResults;
END aPkg;
==== end package ====
