Page 1 of 1

divide records into multiple pages 1, 2, 3..

Posted: Thu May 15, 2003 10:06 am
by jiehuang001
I have a question on how to divide the pages in PHP. For
example, in JSP, I have used the following code:
if (current_page == 1) rs.beforeFirst();
else rs.absolute((current_page-1)*record_per_page);
that is, if the the current page is 1, then retrieve the records from
beginning. if the current page is something else such as 3, then retrieve the record beginning from (3-1)*record_per_page.

Is there some thing similar as this in PHP/Oracle?

Thanks.

Jie Huang

Posted: Thu May 15, 2003 12:00 pm
by Wayne Herbert
If I understand your question correctly, you want to display mutliple web pages with a certain number of records on each page. For example, if you want to display 25 records per page, and there are 113 records in the database, then you would want to output five pages, perhaps with a "next page" and "previous page" links. If that is the case, then you could do it this way.

The key is passing a paged if variable with the script name so you know which page you are working on, as in

sample.php?pageid=1

Then, the code to break the database into pages looks like this.

Code: Select all

<?php
// clean up input
$pageid = substr($pageid, 0, 2);
$pageid = EscapeShellCmd($pageid);

// set some number to output per page
// $recstoread is one more than the number we will output  so that
// we don't put out a next page link when there is no next page
$recsperpage = 25; 
$recstoread = $recsperpage + 1;

// number of thumbs to skip in query
$skiprows = (($pageid - 1) * $recsperpage); 

// build the query into the table
$query = "SELECT * FROM $tablename
                LIMIT $skiprows, $recstoread";

// execute the query and output the results
// this example just sticks each row into a table 
if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database");

// if we got some rows back then output page
$numrows = mysql_num_rows($result);
if ($numrows > 0)
  echo "<table BORDER WIDTH="100%" >";
  {
  for ($i = 1; $i <= $recsperpage; $i++)
    {
    if ($row = @ mysql_fetch_array($result))
      {
      echo "<td valign=top WIDTH="100%">";
      // format your fields here for echoing to the table row
      echo "</td>;
      } // if there are rows to put out
    } // for the number of rows on the page
  echo "</table>"; // end the table
  } // if there were rows retrieved
else
  {   
  echo "There are no rows from the query";
  }

// now put out next page and last page links.

// if not on the first page, make link to previous page
if ($pageid > 1)
  {
  $oldpageid = $pageid - 1;
  echo "<a href="sample.php?pageid=$oldpageid">";
  echo "Go Back to Page $oldpageid</a>";
  }

// if the row count is less than the records per page we know we are
// on the last page, so don't do a next page link.
if ($numrows == $recstoread)
  {
  $newpageid = $pageid - 1;
  echo "<a href="sample.php?pageid=$newpageid">";
  echo "Go Next to Page $newpageid</a>";
  }
?>

Haven't run this code but it is a derivative of what I use so it should work.

I need to do it in PHP with Oracle

Posted: Thu May 15, 2003 12:40 pm
by jiehuang001
Unfortunately, in Oracle, there is no function like "LIMIT" in a sql statement.

Jie Huang

Posted: Thu May 15, 2003 2:21 pm
by Wayne Herbert
Ahhh... one more reason for me to dislike Oracle!

If there is no LIMIT statement, then one obvious workaround is to create a numeric key that is a row number that you can use in a WHERE statement with a compound condition, looking for row numbers that fall within a range that you compute based upon pageid and records per page.

Code: Select all

// number of thumbs to skip in query 
$skiprows = (($pageid - 1) * $recsperpage); 
$lastrow = $skiprows + $recstoread;

// build the query into the table 
$query = "SELECT * FROM $tablename 
                WHERE rowid between $skiprow and $lastrow;
Or you could use a MINUS operation on the same fudge key

Code: Select all

$query = SELECT * FROM   $tablename
              WHERE  rowid in (
              SELECT rowid FROM tableX
                WHERE rowid <= 7
                MINUS
                  SELECT rowid FROM tableX
                  WHERE rowid < 5);
I dunno... makes me glad to not be using Oracle. LIMIT is a useful command.

How can you avoid multiple hits to the DB?

Posted: Wed Nov 26, 2003 11:45 pm
by charliew
The problem with this approach is that it creates a hit on the database each time you move to a new page, which significantly increases throughput (as I've found out the hard way).

I was thinking that if you did a SELECT statement and returned all the records, you could keep track of that user's array values, and then just display the appropriate array values for the next page.... ?

But, how do you keep track of that user's array when re-entering the script? For example, when the prev/next navigation links were something like 'xxx.php?page=2'. If the variable is registered for the user's session, and not 'empty', then could you display records 25-50? otherwise do the SELECT because the user hadn't fetched records before?

I just need some design concept guidance, not any code..........

Thanks,
Charlie

Re: How can you avoid multiple hits to the DB?

Posted: Thu Nov 27, 2003 8:49 am
by Weirdan
charliew wrote:But, how do you keep track of that user's array when re-entering the script? For example, when the prev/next navigation links were something like 'xxx.php?page=2'. If the variable is registered for the user's session, and not 'empty', then could you display records 25-50? otherwise do the SELECT because the user hadn't fetched records before?
Why not? ;)

Posted: Thu Nov 27, 2003 9:06 am
by charliew
I can't use a persistent connection, and so I get large number of connects and very high overhead.......

Posted: Thu Nov 27, 2003 1:33 pm
by Weirdan
I did mean that you can retrieve recordset, then store it to session.

Re: I need to do it in PHP with Oracle

Posted: Sat Nov 29, 2003 5:46 pm
by JAM
jiehuang001 wrote:Unfortunately, in Oracle, there is no function like "LIMIT" in a sql statement.

Jie Huang
No?

Code: Select all

select * from table1 where ROWNUM < 10
Isn't ROWNUM something to be used? Abit rusty on oracle, so It might be wrong depending on version altho I belive Oracle versions 7, 8.0 and 8i should support it.

Posted: Tue Dec 02, 2003 3:11 am
by markbeadle
You could try selecting everything and store in array or object.
Have a hidden form with $page_number, $full_list:array/object.
Previous button/link has javascript to modify the page_number and submit form,
Next button/link also has javascript to modify the page_number and submit form,

It may be necessary to have the form as POST as array may be too large to use with GET.

When loading the page check if $_POST(items) is set if it is use it else interrogate db.
Not need to use sessions in this case.

Just an idea hope it helps

Posted: Tue Dec 02, 2003 4:01 am
by charliew
Thanks for the idea. That's the kind of approach ideas I was looking for. Question tho, wouldn't the array be a session variable? or just a global...

Posted: Tue Dec 02, 2003 4:20 am
by markbeadle
charliew wrote:Thanks for the idea. That's the kind of approach ideas I was looking for. Question tho, wouldn't the array be a session variable? or just a global...
If the idea you are talking about is mine then yes, if you are using sessions the array could be held as a session variable. If not then you need to pass it from one page to the next so it is not a global but a POST as globals get replaced per page.