how can i page through dababase results ?? if possible close to the way this forums ones do. ??
any help greatly apreciated. :)
Printable View
how can i page through dababase results ?? if possible close to the way this forums ones do. ??
any help greatly apreciated. :)
I trawled the net for ages trying to find the answer to this but never found a satisfactory one. So I decided to figure it out myself and eventually came up with the following.
First you should do this somewhere near the top of the script so if no page number is passed to the script it will default to page 1. You should also specify how many rows to show per pageYou have to query the db to find out how many records there are in total to begin with, something likePHP Code:if ($page == '' || !is_numeric($page)) {
$page = 1;
}
$per_page = 10; // shows 10 rows per page
We now know how many "pages" are needed to show all the rows.PHP Code:$sql_max = "SELECT * FROM table WHERE something=something else";
$max_result = mysql_query($sql_max); // execute query
$max_rows = mysql_numrows($max_result); // get no. of rows
$pages = ceil($max_rows / $per_page); // divide by number per page to get pages required
You should then calculate the offset for the sql query that will actually get the rows we want. The offset is the row to start at which is done like so
Now we get the rows requiredPHP Code:$offset = ($page - 1) * $per_page; // get offset for SQL query
All you have to do now is loop through those records returned printing the contents of each as normal.PHP Code:$sql_products = "SELECT * FROM table WHERE something=somethingelse LIMIT ${offset}, ${per_page}";
You'll need to put hyperlinks in so users can select other pages, which can be done like this
Hopefully that is all of it; I cut and pasted the relevent bits from a ~300 line script so I might have missed something out.PHP Code:if ($pages > 1) {
// more than 1 page required
for ($count_page = 1; $count_page <= $pages; $count_page++) { // loop through pages required printing links
print "<a href=\"$PHP_SELF?page=$count_page\">Page $count_page</a><br>";
}
}
:p Michael, did you check my site out.
I have some snippets and tutorials that explain what and how to do this. if you have trouble you can see that I do this on my site as well.
My way is similar to Chris's but with a few more things.
I must confess I didn't look at your site before, looks good though.
lol, sorry scoutt. i havnt had time to do searching lately cos my time on the net is limited to like half and hour and i have other things to be doing too :(Quote:
Originally posted by scoutt
:p Michael, did you check my site out.
I have some snippets and tutorials that explain what and how to do this. if you have trouble you can see that I do this on my site as well.
My way is similar to Chris's but with a few more things.
anyway, thanx for ur contributions guys :)
That's one problem for me. I'm using MS SQL (not as choice) and there is no "LIMIT" in there.Quote:
Originally posted by chrisjk
I trawled the net for ages trying to find the answer to this but never found a satisfactory one. So I decided to figure it out myself and eventually came up with the following.
(...)
You should then calculate the offset for the sql query that will actually get the rows we want. The offset is the row to start at which is done like so
Now we get the rows requiredPHP Code:$offset = ($page - 1) * $per_page; // get offset for SQL query
(...)PHP Code:$sql_products = "SELECT * FROM table WHERE something=somethingelse LIMIT ${offset}, ${per_page}";
This is the ESSENCE, the main thing about paging, because, when the number of results and pages raise, withtout this, the buffer needed will raise too, at 1:1, which is awfull.
Well, I will (now) search for such solution for MS SQL, but... I still wish to know if there is a way to do it through PHP.
And, by the way, I couldn't find scoutt's site (maybe because I'm one year late). Anyone got updated info about it?
You can access his site here: http://www.snippetlibrary.com/Quote:
Originally posted by Cawas
And, by the way, I couldn't find scoutt's site (maybe because I'm one year late). Anyone got updated info about it?