Pagination PHP and MySQL

I have been asked this question many times. Pagintion is a concept every programmer handles at some point of time. Yet understanding is simple, conceptual programming here varies from db to db,MySQL Provides a simple and powerful solution for this problem. Friends let us see how it is done.
Please post your comments no matter wether right or wrong. And please try to find out mistakes and fix them in the comments. as that can correct me a lot.

Question

Hello, I am looking for a code...suppose I have 50 records in a table and I want to show them on page 10 recordes per page. Can any one give me the code? I need this code...please help me

http://www.orkut.com/CommMsgs.aspx?cmm=385&tid=2545725937110064956&start=1


My Answer
Assuming that u r using PHP and MySQL technologies, the solution for ur problem is `LIMIT` in select statement, limit keyword accompanies with the start and length parameters.

eg: select * from tblname LIMIT start 0, length 30;
above statement fetches 30 records starting from the first record. for your requirement these stmts might help
Pg1: select * from tblname LIMIT start 0, length 10;
Pg2: select * from tblname LIMIT start 10, length 10;
Pg3: select * from tblname LIMIT start 20, length 10;
Pg4: select * from tblname LIMIT start 30, length 10;
Pg5: select * from tblname LIMIT start 40, length 10;

now how do you automate this process?

now as u said each page needs to have 10 records.
1. so put max_recds_per_page = 10
2. next do count(*) from table to get total records say total_recds=50
3. get total_pages= total_recds/max_recds_per_page
Caution this can lead to floating digits, so ceil or make your own function that gets relevent data out of the division.
now say total_pages=5
4. now we need to display the links for the 5 pages
for($incr_pages=1;$incr_pages<=$total_pages;$incr_pages++)
{
echo("anchor href=disp.php?start=$incr_pages ");
}
so the output will be
page 1
page 2
page 3
page 4
page 5

note disp.php is the display page where record navigation is required

now as the structure is ready we need to display records as we are dependent on the GET varable
start we have to validate it properly
if (isset($_GET["start"]))
{
$start=(int)$_GET["start"];
}
else
{
$start=0;
}
now we prepare select statement to get the records from the table with the limit

$getRecds="SELECT * FROM tbl_name LIMIT ".$start." , ".$max_recds_per_page;

When the page has served fro the first time, the start variable is not set and the value for
$start will be 0

now we query the database
$res2=mysql_query($getRecds);

now we print the results in a loop

while($data2=mysql_fetch_array($res2))
{
echo($data2[0]);
}

this ends the conceptual programming of page navigation or record navigation or in my terms pagination.the complete demo can be found in the attached file,

Steps to duplicate:

1. create a database called test,
2. create table as mentioned below along with the data.
3. assuming that localhost, the following demo fits in.

Test table used for this demo is

CREATE TABLE `us_states` (
`id` int(11) NOT NULL auto_increment,
`statename` varchar(50) NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;

-- -- Dumping data for table `us_states`--INSERT INTO `us_states` (`id`, `statename`) VALUES (1, 'Alabama'),(2, 'Alaska'),(3, 'Arizona'),(4, 'Arkansas'),(5, 'California'),(6, 'Colorado'),(7, 'Connecticut'),(8, 'Delaware'),(9, 'Florida'),(10, 'Georgia'),(11, 'Hawaii'),(12, 'IdahoState'),(13, 'Illinois'),(14, 'Indiana'),(15, 'Iowa'),(16, 'Kansas'),(17, 'Kentucky'),(18, 'Louisiana'),(19, 'Maine'),(20, 'Maryland'),(21, 'Massachusetts'),(22, 'Michigan'),(23, 'Minnesota'),(24, 'Mississippi'),(25, 'Missouri'),(26, 'Montana'),(27, 'Nebraska'),(28, 'Nevada'),(29, 'NewHampshire'),(30, 'NewJersey'),(31, 'NewMexico'),(32, 'NewYork'),(33, 'NorthCarolina'),(34, 'NorthDakota'),(35, 'Ohio'),(36, 'Oklahoma'),(37, 'Oregon'),(38, 'Pennsylvania'),(39, 'RhodeIsland'),(40, 'SouthCarolina'),(41, 'SouthDakota'),(42, 'Tennessee'),(43, 'Texas'),(44, 'Utah'),(45, 'Vermont'),(46, 'Virginia'),(47, 'Washington'),(48, 'WestVirginia'),(49, 'Wisconsin'),(50, 'Wyoming');