paging through db results ??

[ubunreal69]

how can i page through dababase results ?? if possible close to the way this forums ones do. ??

any help greatly apreciated.

[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.

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 page

if ($page == '' || !is_numeric($page)) {
    $page = 1;
}

$per_page = 10; // shows 10 rows per page 

You have to query the db to find out how many records there are in total to begin with, something like

$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 

We now know how many "pages" are needed to show all the rows.

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

$offset = ($page - 1) * $per_page; // get offset for SQL query 

Now we get the rows required

$sql_products = "SELECT * FROM table WHERE something=somethingelse LIMIT ${offset}, ${per_page}"; 

All you have to do now is loop through those records returned printing the contents of each as normal.

You'll need to put hyperlinks in so users can select other pages, which can be done like this

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>";
    }
} 

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.

[scoutt]

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.

[chrisjk]

I must confess I didn't look at your site before, looks good though.

[ubunreal69]

Originally posted by scoutt
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.

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

anyway, thanx for ur contributions guys

[Cawas]

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

$offset = ($page - 1) * $per_page; // get offset for SQL query 

Now we get the rows required

$sql_products = "SELECT * FROM table WHERE something=somethingelse LIMIT ${offset}, ${per_page}"; 

(...)

That's one problem for me. I'm using MS SQL (not as choice) and there is no "LIMIT" in there.

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?

[The Hobo]

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?

You can access his site here: http://www.snippetlibrary.com/