PHP Oracle Pagination Cant使用ROWNUM显示数据

Question

I just try using Oracle database and found problem in pagination. Last time I using in MySQL it work good.

See my code :

<?php
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 3;
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;

$query_pag_data = "SELECT P.FORM_NO, P.MODEL_NO, P.PRODUCTION_STATUS, P.REMARKS, P.DATE_ADDED, P.TIME, P.QTY_PLAN, M.MODEL_NO, M.MODEL_NAME
                    FROM SEIAPPS_PRODUCTION_STATUS P, SEIAPPS_MODEL M
                    WHERE ROWNUM BETWEEN $start AND $per_page AND P.MODEL_NO = M.MODEL_NO ORDER BY P.DATE_ADDED DESC, P.TIME";

$result_pag_data = oci_parse($c1, $query_pag_data);
oci_execute($result_pag_data);

I've used ROWNUM, but when I want to open page 2 or next page, it was not show any data. Whereas still have more data in table.

Anyone please help to advice. Thanks

Answer 1

ROWNUM in Oracle is not like LIMIT in MySQL.

In MySQL,

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement... With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

In Oracle,

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

SELECT *
  FROM (SELECT * FROM employees ORDER BY employee_id)
  WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

So the problems of your code is:

• WHERE ROWNUM BETWEEN :startrow AND :perpage is logically incorrect, as ROWNUM is a row index indicator, not a "number of rows" indicator. ROWNUM BETWEEN a AND b means to return rows ordering from a to b, e.g. row #1 to row #3, NOT row #1 and 3 rows down.

  So even if ROWNUM BETWEEN a AND b works (it actually doesn't), to get "page 2" of the query, logically you want ROWNUM BETWEEN :startrow AND :endrow, e.g. ROWNUM BETWEEN 4 AND 6;

• ROWNUM BETWEEN :startrow AND :endrow actually wouldn't work for start row larger than 1, because ROWNUM is a pseudocolumn that represents the order of the row in the selected set,

  so when you execute e.g. SELECT...WHERE ROWNUM BETWEEN 4 AND 6,

  • first match, Oracle thinks, "Will the rownum be in 4 to 6?" Because there's no rows selected yet, its ROWNUM will be 1 if selected, so Oracle abandon it;
  • second match, "Will this be in 4 to 6?" No because there's no rows selected so this will still be 1;
  • third match, same;
  • ...

  There will be no result at all.

  To get the desire output, you'll have to wrap your query in subquery, and check row number outside, like this:

SELECT * FROM
  (SELECT ROWNUM rn, t.*
    FROM
      (SELECT ...
        FROM ...
        WHERE ...
        ORDER BY ...) t
  )
  WHERE rn>=:startrow AND rn<=:endrow

to guarantee that the row ordering is settled before checking the row order.

• And about coding style, as OCI supports prepared statement and variable binding, you should use it:
  

  Binding allows the database to reuse the statement context and caches from previous executions of the statement,...Binding reduces SQL Injection concerns because the data associated with a bind variable is never treated as part of the SQL statement.

$statement=oci_parse("... WHERE rn>=:start AND rn<=:end");
oci_bind_by_name($statement,":start",$start,-1,SQLT_INT);
oci_bind_by_name($statement,":end",intval($start+$per_page-1),-1,SQLT_INT);
oci_execute($statement);