Building Next/Prev Buttons for Query Results

Rod Kreisler picture of Rod

There have been numerous requests on the Support Forums recently asking how to do "NEXT 1 2 3 4 5 PREV" type links from a search result. I hope the following script will help you add this functionality to your search result pages. This example is written for MySQL but can be adapted quite easily to other SQL engines.

Since each application is different I've used some generic statements for the MySQL queries. TABLE should be replaced with your table name. YOUR CONDITIONAL HERE should be replaced by your where conditions and WHATEVER should be replaced by the column you wish to order your results (don't forget to add DESC if applicable to your needs).



<?php

$limit=20; // rows to return
$numresults=mysql_query("select * from TABLE where YOUR CONDITIONAL HERE order by WHATEVER");
$numrows=mysql_num_rows($numresults);

// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
    $offset=1;
}

// get results
$result=mysql_query("select id,name,phone ".
    "from TABLE where YOUR CONDITIONAL HERE ".
    "order by WHATEVER limit $offset,$limit");

// now you can display the results returned
while ($data=mysql_fetch_array($result)) {
    // include code to display results as you see fit
}

// next we need to do the links to other results

if ($offset==1) { // bypass PREV link if offset is 0
    $prevoffset=$offset-20;
    print "<a href=\"$PHP_SELF?offset=$prevoffset\">PREV</a> &nbsp; \n";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
    // has remainder so add one page
    $pages++;
}

for ($i=1;$i<=$pages;$i++) { // loop thru
    $newoffset=$limit*($i-1);
    print "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \n";
}

// check to see if last page
if (!(($offset/$limit)==$pages) && $pages!=1) {
    // not last page so give NEXT link
    $newoffset=$offset+$limit;
    print "<a href=\"$PHP_SELF?offset=$newoffset\">NEXT</a><p>\n";
}

?>

That should do the trick for you. Of course, you'll probably want to clean up the HTML output...

Also, note that the links to $PHP_SELF only include the $offset. If you need to pass parameters for the where conditional of your query, you'll need to tack those on as well.

--Rod

Comments:
Result show problem	Mario	11/09/11 03:27
RE: here is the fix for your code	Sergio Teixeira	10/21/11 07:12
next and previous button	sunny	01/31/11 08:40
here is the fix for your code	sylvain	08/20/10 14:07
need help	majed	04/07/10 04:57
need help	majed	04/07/10 04:57
need help	majed	04/07/10 04:42
$offset problem	Brad	12/10/09 22:15
RE: function of next-prev buttons	hemant	02/10/09 23:04
This code with php4.2.2	Angelo	07/09/03 07:26
function of next-prev buttons	drej	01/26/03 01:24
Here is how to do it in Oracle	Jonathan George	12/28/02 17:15
Search Result Class	Onez	11/06/02 00:04
What about ADOdb	Kent V.	10/28/02 16:40
Help with many results??	FidoDido	10/27/02 22:54
Query Results Windows to Onunload flash butto	Rose Roland	09/25/02 20:43
RE:I modified some code, it works great	xichen	09/22/02 20:22
RE: I've made my own, works great with mysql...	Phil Atkinson	09/08/02 11:16
RE: Code i am using	derksen	08/19/02 10:13
RE: Code i am using	derksen	08/19/02 09:10
Prev/Next Button Code Fix	Dave	08/18/02 13:57
RE: Display Problem	Dave	08/18/02 13:54
RE: Code i am using	derksen	08/14/02 11:29
Display Problem	Leon Tran	08/01/02 22:22
RE: "NEXT 1 2 3 4 5 PREV"	Marek	07/25/02 16:18
RE: PREV / NEXT WORKING CODE	Tyler	07/22/02 17:43
RE: PREV / NEXT WORKING CODE	Tyler	07/22/02 16:37
Another improvement	Peter Kuiper	07/12/02 05:43
I need help	andrew	06/29/02 04:40
"NEXT 1 2 3 4 5 PREV"	renato	06/25/02 05:06
RE: command button to display new page	ericka	06/21/02 10:21
Validation & Previous button	Andy Lancaster	05/23/02 07:36
RE: How to limit records in ORACLE?	lee	05/23/02 03:14
Randomized Data and Next and Previous	Elizabeth Alderton	05/21/02 12:55
how this code works in a function??	Ioannis	04/28/02 23:06
RE: How to do it in Postgresql	Oussama Zarif	04/24/02 05:39
RE: Help!!	Dan Snik	04/22/02 10:51
Help!!	Simon	04/19/02 15:22
How to do Next 1 2 3 4 Pervious in Postgresql	Oussama Zarif	04/18/02 06:15
How to do it in Postgresql?	Oussama Zarif	04/18/02 03:20
RE: And Interbase?????	sielim	04/09/02 10:09
How to do it in Postgresql	zeus	04/05/02 17:25
Please clarify last line of article.	c. douglass	04/04/02 15:19
Code modification - limiting pages displayed	Dat	03/26/02 17:56
1,2,3...100, 101 next	Derrick	03/15/02 23:51
RE: Greate, Suggest me something more..	Bhupendra Banodhe	03/07/02 05:05
RE: Pb with Next !	Ryan Housand	03/04/02 16:47
Pb with Next !	Aphikit	03/01/02 04:58
RE: Hows this code?	randy	02/19/02 23:04
Variable for $recordLimit	Gary	02/17/02 20:56
RE: Yet another approach (THANKS)	Daniel	02/13/02 08:12
More efficient: SQL_CALC_FOUND_ROWS	Marshall	02/09/02 17:32
RE: Code i am using	carole	02/06/02 04:36
RE: Hows this code?	KillerX	01/24/02 16:11
RE: Too Many Nav Link Pages Problem	Dan Snik	01/24/02 10:12
Getting Funny Results?	Dustin Frost	01/21/02 05:39
Performance issues	khayll	01/18/02 10:03
RE: PHP Brainiac's eh? "data here" ?? 2UNoY?	fabrice	01/14/02 15:32
Thanks, Rod!	Tina P.	01/11/02 10:03
And Interbase?????	Elieser Le�o	01/07/02 11:46
PHP Brainiac's eh? "data here" ?? 2UNoY?	Matt	01/04/02 18:08
RE: I've made my own, works great with mysql...	Toly	12/25/01 20:50
RE: Next - Prev for ORA8	patinya	12/11/01 21:27
RE: How to limit records in ORACLE?	patinya	12/11/01 21:25
Hows this code?	Huw	12/06/01 19:26
RE: How to limit records in ORACLE?	patinya	12/04/01 04:27
Next - Prev for ORA8	Pravietis	11/27/01 12:12
next/prev	patinya	11/21/01 19:02
display	sands	11/21/01 11:07
RE: Code	michael	11/20/01 01:36
RE: $offset problem	Vadim	11/19/01 13:35
Mistyped?	Batara Kesuma	11/11/01 03:19
RE: I've made my own, works great with mysql...	David Day	11/10/01 12:27
Flat File	Tom D.	11/08/01 00:26
SQL Server 7	Mark Stewart	11/03/01 08:28
RE: Code i am using -> more results in next	Menno	10/31/01 09:36
Code i am using	Jester	10/21/01 10:49
RE: PREV / NEXT WORKING CODE	Charles Bailey	10/19/01 02:43
little tweek	Jester	10/18/01 19:12
RE: $offset problem	Michael O'Neal	10/16/01 18:05
RE: Too Many Nav Link Pages Problem	anitha	10/16/01 15:05
RE: I've made my own, works great with mysql...	Rescue9	10/07/01 22:48
Too Many Nav Link Pages Problem	gary	09/26/01 19:25
$offset problem	Stiaan	09/20/01 02:56
RE: Another modified script of a script	gary	09/19/01 18:00
RE: Need help	Stiaan	09/17/01 06:02
thank for your php code	chai mei ying	09/07/01 02:20
Need help	St�phane Joos	09/07/01 01:36
next search result	kimmi	09/06/01 20:39
RE: I've made my own, works great with mysql.	Jeff	09/06/01 16:28
RE: Gah! So inefficient!	Michael O'Neal	09/06/01 09:25
RE: Gah! So inefficient!	sam	09/02/01 22:32
RE: Code error = YES and it's fixed	Francis Shanahan	09/02/01 08:16
That's it!	Stone Cold	08/31/01 13:54
Need a php source code	joseph chidiac	08/29/01 22:21
RE: I've made my own, works great with mysql...	manny	08/26/01 14:36
RE: I've made my own, works great with mysql...	manny	08/25/01 10:14
RE: count(*)	Steve Grecni	08/24/01 11:16
OK. I GIVE UP. Little Help???	Michael O'Neal	08/21/01 17:02
How to add location info like "viewing 10-19"	Shawna Gibbs	08/19/01 16:04
RE: I've made my own, works great with mysql...	gary	08/03/01 15:47
RE: I've made my own, works great with mysql...	Andrew Taylor	07/31/01 03:43
RE: SQL Server 7	Khunnee	07/29/01 18:22
RE: I've made my own, works great with mysql...	Jaret	07/21/01 20:52
RE: SQL Server 7	Khunnee	07/19/01 20:42
RE: count(*)	TMN	07/17/01 06:20
sorting in 3 rows	kid	07/05/01 22:11
little thought	Lance Winter	07/05/01 01:02
Random result set & NEXT/PREV	Elek	07/04/01 01:31
Code error?	Lenny	06/27/01 20:06
RE: I've made my own, works great with mysql...	john	06/21/01 10:36
How to get the position of an Record	Peter	06/21/01 05:03
Please Help :(	Greg:)	06/20/01 15:07
RE: SQL Server 7	Tim G	06/15/01 20:49
RE: SQL Server 7	dunsun	06/07/01 05:50
Oracle Situation	chris	05/29/01 13:28
RE: How to limit records in ORACLE?	jaewook Kim	05/28/01 17:02
Doing it in Oracle?	kaiser	05/14/01 13:09
RE: Another modified script of a script	Adam S	05/07/01 17:09
RE: I've made my own, works great with mysql...	Adam Spigel	05/07/01 15:28
RE: How to limit records in ORACLE?	KnightHawk	05/07/01 11:22
RE: I've made my own, works great with mysql...	mrgibson	05/04/01 04:30
How to limit records in ORACLE?	kaiser	05/03/01 10:23
With Oracle	kaiser	05/02/01 15:41
few more questions :)	fandelem	04/24/01 03:31
RE: Gah! So inefficient!	YeeHaW Jelte	04/17/01 05:29
RE: Gah! So inefficient!	Joe Laffey	04/16/01 15:51
RE: Try this (correction)	Mathew	04/06/01 08:34
RE: I've made my own, works great with mysql...	copon	04/06/01 07:21
RE: I've made my own, works great with mysql...	copon	04/04/01 07:31
How can we do with Oracle?	Papi LO	04/02/01 11:00
parameters for where conditional (?)	David Norton	04/01/01 01:54
so far so good but one catch.	Marc	03/29/01 12:59
RE: Try this (correction)	Mikel Williams	03/23/01 13:11
Problem with SQL code?	Gabriel	03/19/01 12:07
what if no mysql?	tuxpow3r	03/15/01 01:11
RE: Is there something wrong with this snippet?	rod k	03/14/01 12:45
Is there something wrong with this snippet?	Liam	03/13/01 14:42
RE: I am having trouble. Please help me.	Mr T	03/13/01 05:21
RE: count(*)	Brent	03/12/01 21:17
count(*)	Ridge	03/12/01 17:04
thanks rod	bryan	03/09/01 11:59
try this for prev	bryan	03/09/01 11:38
Passing Records thru pages in ORACLE	Ricardo Vercesi	03/09/01 09:32
1.000 links ?	Teo Danardi	03/03/01 12:14
RE: Can the sql be executed once?	Vladimir Shapiro	02/26/01 01:14
Please help	Rowan Trimmer	02/19/01 07:29
RE: I've made my own, works great with mysql...	Jimbo	02/16/01 22:01
What am I doing wrong?	Bob	02/15/01 18:43
Can the sql be executed once?	Alex Lam	02/15/01 05:16
RE: global variables value	Javed	02/13/01 01:43
Last result on page repeats itself	Tom Garcia	02/07/01 16:06
RE: I've made my own, works great with mysql...	ateng	02/04/01 22:44
RE: I've made my own, works great with mysql...	raffie	02/04/01 09:47
Is regexp compatible with this script?	raffie	02/04/01 04:22
I am having trouble. Please help me.	raffie	02/04/01 03:16
RE: I've made my own, works great with mysql...	Robert	02/02/01 03:46
Good!!! Thank you Rod	Jorge Terrero	01/30/01 15:53
RE: I've made my own, works great with mysql...	Danny Hill	01/28/01 15:46
I've made my own, works great with mysql...	David	01/23/01 14:07
Coooll	Dendy Bagus S	01/20/01 01:26
limit --- in oracle	Paras pradhan	01/16/01 05:55
RE: Gah! So inefficient! - Todd	rod k	01/15/01 09:10
RE: Gah! So inefficient - John	rod k	01/15/01 09:03
RE: global variables value	rod k	01/15/01 08:48
RE: Passing Query String Bug <security prob>	Mark Kinsella	01/14/01 04:16
RE: <Mike D.> Yet another approach	Pieter W.D.Kroon	12/30/00 23:10
Thanks Rod!	Vince	12/30/00 19:21
RE: <Mike D.> Yet another approach	zeny ortiz	12/25/00 22:01
global variables value	sanddy cepero	12/23/00 11:10
RE: Yet another approach... small problem	Chris Meiering	12/20/00 16:58
How implent it in Oracle	Jes�s Carmona	12/19/00 11:37
RE: Yet another approach	Mike D.	12/15/00 10:09
RE: Gah! So inefficient!	Thomas Hurst	11/28/00 11:29
Yet another approach	zeny ortiz	11/28/00 03:21
RE: Gah! So inefficient!	Todd F incannon	11/16/00 11:49
RE: Gah! So inefficient!	Jason Boyd	11/15/00 12:27
RE: Passing Query String Bug	Michael I. Buen	11/15/00 08:47
Gah! So inefficient!	John Robinson	11/14/00 07:04
Try this (correction)	B. Angelo Molizane	11/06/00 11:52
The Next Link	Richie TM	11/04/00 09:52
I only see one message	Rick Waalders	10/31/00 10:16
RE: problem with mysql query adn those script	Roy cowboy	10/30/00 00:07
RE: Small Problem - FIX	John Bloomfield	10/16/00 02:17
and what if?	Lukas Smith	10/06/00 06:22
Passing Query String Bug	James	09/29/00 17:17
RE: Missing rows under 20 ...	Caroline Gaceru	09/25/00 03:54
Missing rows under 20 ...	Eyal	09/19/00 14:58
RE: improved version	Kimmie Dicaire	09/14/00 20:59
improved version	Scott Molinari	09/10/00 10:26
problem with mysql query adn those script	Torsten Schrammen	09/08/00 12:14
RE: LIMIT function in MS SQL	Mike Squires	09/04/00 09:12
RE: What is empty?	Matthew Howey	08/29/00 15:38
What is empty?	Chris Williams	08/28/00 21:29
LIMIT function in MS SQL	Greg Sohl	08/18/00 10:11
pages being miscalculated - w/fix (I think)	Mark Donchek	08/06/00 21:52
LIMIT function missing	John Schuhr	08/06/00 17:28
$offset	Mark Donchek	08/06/00 16:04
RE: Small Problem - FIX	Tim Patterson	08/01/00 00:26
RE: Small Problem - FIX	Jonathan Griffin	07/19/00 15:51
Updated Version	Brett Stimmerman	07/18/00 16:51
Small Problem	chris	07/14/00 02:36
SQL Server 7	Tim Patterson	07/06/00 16:35
Missing Stuff! (You might need this..)	Tim Patterson	07/06/00 16:21
Past comments????	Alex Darke	06/30/00 22:19
SQL Server 7	Ian Evans	06/30/00 17:10
If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly. Add A Comment: Name: Email: Subject: Message: To reduce spam posts, messages are now manually approved You are not [logged in]. That means your account will not get credit for this post.

Find a programming school near you

Add A Comment: