Thursday, July 26, 2007

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');

Tuesday, July 10, 2007

OSS Camp

OSS Camp, Delhi is the India's biggest conference, yet. Organized on the lines of Bar Camps, OSS Camps are intense events with discussions, presentations, and interactions with the participants. Motivated by the desire of learning by sharing and to share your work with the community



When & Where
The OSS Camp, Delhi is being organized on September 8Th-9Th, 2007. The Venue is yet to be decided. More info available in the Venue section.

Register
Registrations for OSS Camp, Delhi are open. We are inviting participants from around India and overseas. You can register for the event here. If you are from outside Delhi, please help spread the word in you city or country.

Note: If you'd also like to present a session, please visit the specific Camp Site (see above) and register your session there.

Volunteer
OSS Camp, Delhi is a community event. There is no specific group of organizers - Every participant is an organizer. So take up the responsibility of making it a success.
If you'd like to help with organizing the camp, volunteer here and take up some task.

Sponsor
All details regarding sponsorship and our sponsors are available here.

Tagging
Maintaining information on the Internet is not an easy task! To help us make information regarding OSS Camp, Delhi easily accessible kindly tag all your blogs, mails, posts, articles, etc with the OSS Camp, Delhi Tag, which is: OSSCampDelhiSep07

For Blogs you can also use the Track back URI: click here


ANYTHING TO DO WITH
1. PHP
2. MySQL
3. Drupal
4. Linux
5. PhpMyAdmin
6. Python
7. Joomla

!! Chalo Delhi SEP 8 2007 !!

Check this out for further info: http://www.osscamp.in/

Rate This Post

trying to put some star ratings, service links like technoratti and del.icio.us ... do u think this would work? comment it.

what is this for?

i will try to put some public poll afert some time