[web] PHP Question - Dividing Database results into pages
Author
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.
With MySQL you can use the LIMIT keyword. The syntax is
SELECT * FROM a_db WHERE bla = bla LIMIT offset, maxrows;
offset is the offset from the first row, starting at 0.
SELECT * FROM a_db WHERE bla = bla LIMIT offset, maxrows;
offset is the offset from the first row, starting at 0.
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);
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);
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement