divide records into multiple pages 1, 2, 3..
Moderator: General Moderators
-
jiehuang001
- Forum Commoner
- Posts: 39
- Joined: Mon May 12, 2003 12:53 pm
divide records into multiple pages 1, 2, 3..
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
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
- Wayne Herbert
- Forum Commoner
- Posts: 34
- Joined: Tue Apr 29, 2003 3:13 pm
- Location: Houston, Texas
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.
Haven't run this code but it is a derivative of what I use so it should work.
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.
-
jiehuang001
- Forum Commoner
- Posts: 39
- Joined: Mon May 12, 2003 12:53 pm
I need to do it in PHP with Oracle
Unfortunately, in Oracle, there is no function like "LIMIT" in a sql statement.
Jie Huang
Jie Huang
- Wayne Herbert
- Forum Commoner
- Posts: 34
- Joined: Tue Apr 29, 2003 3:13 pm
- Location: Houston, Texas
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.
Or you could use a MINUS operation on the same fudge key
I dunno... makes me glad to not be using Oracle. LIMIT is a useful command.
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;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);How can you avoid multiple hits to the DB?
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
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?
Why not?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?
Re: I need to do it in PHP with Oracle
No?jiehuang001 wrote:Unfortunately, in Oracle, there is no function like "LIMIT" in a sql statement.
Jie Huang
Code: Select all
select * from table1 where ROWNUM < 10-
markbeadle
- Forum Commoner
- Posts: 29
- Joined: Tue Dec 02, 2003 2:50 am
- Location: Aachen, Germany
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
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
-
markbeadle
- Forum Commoner
- Posts: 29
- Joined: Tue Dec 02, 2003 2:50 am
- Location: Aachen, Germany
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.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...