Sign in to follow this  
murdock

[web] PHP Question - Dividing Database results into pages

Recommended Posts

I have written a blog for my CMS and I was wondering what would be the best way to take all the entries out of the database and format them on separate pages with say five entries per page.

BLOG

Entry 1 Entry 2 Entry 3 page1 | page 2 | page 3 is something like I would like to setup and what I want to know is how to divide the db results into the separate pages.

Share this post


Link to post
Share on other sites
if you use LIMIT and OFFSET in your mySQL SELECT statement
you should be able to select just the rows you require

eg.

SELECT * FROM TABLE_NAME LIMIT 10 OFFSET 10

will return rows 10 - 19
(I think this syntax is correct - I haven't tested it though)

Visit: dev.mysql.com/doc/mysql/en/select.html
for more details.

If you would to find out how many pages of data you have
try:

$query = "SELECT count(*) FROM TABLE_NAME";
$result = SQLquery($query); // this just executes the sql query
$count = ($result > 0) ? mysql_result($result,0,0) : 0; // this is to make sure you get a number as a result

// calc number of pages plus 1 page for any left-overs
$num_pages = ($count / $rows_per_page) + ($count % $rows_per_page == 0 ? 0 : 1);

Share this post


Link to post
Share on other sites
Search google for "mysql paging" or "paged results". There are a couple different ways of doing it - some people say that the extra COUNT(*) query is overkill and others say that it's the fastest way. Right now I'm not using it with my site and it seems fast enough but we'll see how things go heh.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this