php / mysql result in pages

for example i have in database 50 rows, how i can get mysql results in pages, 5 results on each page, and display pages ,like : [1] , 2 , 3 , 4 .. 10 if page 5 => 3 , 4 , [5], 6, 7 .. 10, and do this without r开发者_C百科efreshing all content of page.

Use the SQL LIMIT and OFFSET clauses. Example:

First query:

SELECT col1,..,colN FROM table WHERE ... LIMIT 10;

Next query:

SELECT col1,..,colN FROM table WHERE ... LIMIT 10 OFFSET 10;

etc. Build it programmatically depending on what page they're on, multiplying their page number with the offset and results-per-page.

$page = 0;
$rrp = 5; // Rows per page

$res = mysql_query ("SELECT count(*) FROM table");
$maxrows = (int)@mysql_result($res, 0);

$limit = ($page*$rrp).", $rrp";
$res = mysql_query ("SELECT * FROM table LIMIT $limit");

$pages = ceil($maxrows/$rpp);

echo "<a href='?pg=0'>first</a>";
for ($i=0; $i<=$pages; $i++) {
    echo "<a href='?pg=$i'>".($i+1)."</a>";
}
echo "<a href='?pg=$pages'>last</a>";

Thats very basic pagination.
The formatting of how the page number appears is up to you.

However, loading the pages dynamically is a different story.
For that you will be required to use Ajax. Thats where you load external content through JavaScript to update the current page.

Just do a search on google for Pagination for what ever language or framework you are using. The answer to your questions is way to long to explain here fully.