If im showing all the records off my mysql database and its pretty huge, how do i create a simple pagination (e.g. Page: 1 2 3 4 ... 50) so i can have like first 10 records on the main page records.php and then rest continues on the records.php?page=2 and then records.php?page=3 and so on?

I atempted it my self and it didnt quite work like i planned



<?PHP

$query = "SELECT * FROM main";
$result = mysql_query($query);

// Rest of the snip goes here where all the searching records and then displaying records

// This is the bottom of the page where the pagination begins

if (isset($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
}

$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 3;
$lastpage = ceil($numrows/$rows_per_page);

$page = (int)$page;
if ($page < 1) {
$page = 1;
} elseif ($page > $lastpage) {
$page = $lastpage;
}

$limit = 'LIMIT ' .($page - 1) * $rows_per_page .',' .$rows_per_page;

if ($page == 1) {
echo " FIRST PREV ";
} else {
echo " <a href='{$_SERVER['PHP_SELF']}?page=1'>FIRST</a> ";
$prevpage = $page-1;
echo " <a href='{$_SERVER['PHP_SELF']}?page=$prevpage'>PREV</a> ";
}

echo " ( Page $page of $lastpage ) ";

if ($page == $lastpage) {
echo " NEXT LAST ";
} else {
$nextpage = $page+1;
echo " <a href='{$_SERVER['PHP_SELF']}?page=$nextpage'>NEXT</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?page=$lastpage'>LAST</a> ";
}

?>


Script isnt producing any error, this is the problem.

Total records in the database is 7, on this page records.php currently its showing all 7 records with the pagination like this

FIRST PREV ( Page 0 of 0 ) NEXT LAST

It should show only 3 records and the rest of it shud be shown on the records.php?page=2 and records.php?page=3

Why isnt it working?

MySQL makes paging results very easy using LIMIT. You simply specify the starting record and the offset (number of results to display). You will still need to do a record count of the complete result in another query though since you need to know the total results for correctly building the paged link numbers.

http://dev.mysql.com/doc/mysql/en/select.html

Jacob

This should give you an idea of the functionality:

<?php
$_GET['offset'] = (isset($_GET['offset'])) ? $_GET['offset'] : 0;

/*
$query = mysql_query("SELECT * FROM main LIMIT {$_GET['offset']}, 3");
while($data = mysql_fetch_assoc($query))
{
// echo row data.
print_r($data);
}
*/

// create next/prev links.

$page_count = 15 / 3;


if($_GET['offset'] / 3 > 1)
{
?>

<a href="?offset=<?=$_GET['offset']-3?>">Previous</a>

<?php
}

for($i = 1; $i < $page_count+1; $i++)
{
if($_GET['offset'] / 3 != $i)
{

?>

<a href="?offset=<?=$i*3?>"><?=$i?></a>

<?php
}
else
{
echo $i;
}
}

if($_GET['offset'] / 3 < $page_count)
{
?>

<a href="?offset=<?=$_GET['offset']+3?>">Next</a>

<?php
}
?>

Hey 2detailed,

Your example seems intresting, although i quite do not understand how to apply it :(

Never mind, i got it to work.

Thanks for the great example.

I got one question about the script tho.

The script creates the page numbers like this

1 2 3 4 5 Next

How do i make the page numbers for only the records i have within my database?

Also, if i enter the page records.php which is same as records.php?offset=0 it shows the very first 3 records (depending on my query). But, when i click on the page number "1" which is same as records.php?offset=1, it is showing the next 3 records.

How do i make the script to show the first records when i click the page number "1" that is records.php?offset=1 ?????

simply:
<?php
include("dbconfig.php");
$page = $_GET[page];
$min = $page*10;
$max = $min+10;
$db_query = mysql_query("SELECT * FROM 'tablename' LIMIT '$min', '$max'", $db);
if($db_result) {
while ($db_result == mysql_fetch_array($db_query)) {
$i = 0;
$x =0;
echo "<b>Result:$i</b><br>";
foreach($db_result as $result) {
echo "<b>Row:$x</b> $result<br>";
$i++;
}
$x++;
}
?>

this might help a little:
Pagination - what it is and how to do it By Tony Marston (5th January 2004) (http://www.tonymarston.net/php-mysql/pagination.html)

brilliant site with a lot of very good tips.

Hey Koobi,

Thanks for the link, but thats the site i optained the code and atempted it my self. That site's example didnt work. So far the only code that is still working nicely is 2detailed's code.

But im having a lil problem with the code and i posted the problem above waiting for to reply :rolleyes:

Originally posted by latheesan
Hey Koobi,

Thanks for the link, but thats the site i optained the code and atempted it my self. That site's example didnt work. So far the only code that is still working nicely is 2detailed's code.

But im having a lil problem with the code and i posted the problem above waiting for to reply :rolleyes: Sorry for delay in response. I sort of left it with "fill-in-the-blanks". As you can see is the number 3 is the $rows_per_page variable -- essentially. Likewise with the $page_count = 15 / 3; // 15 being the total amount of rows or the mysql_num_rows($query), and once again the 3 being the $rows_per_page.

Let me know if you still need any help!

Is there a way to only show page numbers for only the total number of records i have in the database?

for example, lets say i have 30 records, and i set the script to show 3 records per page. So the page numbers should be from 1 2 3 4 ... 10

:( sorry 2detailed, i didnt quite understand what you meant above

There is one problem in getting total number of result records - load and usage of server, if database is huge. Thant's why it's good to limit max search using prev and next links.

Ohhh, Good idea xelav,

lolz, i neva thought like that

Get a count first with somethign like "select count(*) as cnt from ..."
Use count for calculating number of pages, then use MySql Limit offset,start feature to get the page wanted.
don't worry about extra query. The count query execute very fast compare with other query in Mysql.

Why don't you just use mysql_num_rows(); and limit the pages with LIMIT?

Originally posted by Kijit Solutions
Why don't you just use mysql_num_rows(); and limit the pages with LIMIT?
mysql_num_rows() give you number of row in query result, not count of rows in the whole table(unless of course you query get rows of whole table)