Trading Applications Developer (IL)
Next Step Systems
US-IL-Chicago

Justtechjobs.com Post A Job | Post A Resume

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:
This code with php4.2.2Angelo07/09/03 07:26
function of next-prev buttonsdrej01/26/03 01:24
Here is how to do it in OracleJonathan George12/28/02 17:15
Search Result ClassOnez11/06/02 00:04
What about ADOdbKent V.10/28/02 16:40
Help with many results??FidoDido10/27/02 22:54
Query Results Windows to Onunload flash buttoRose Roland09/25/02 20:43
RE:I modified some code, it works greatxichen09/22/02 20:22
RE: I've made my own, works great with mysql...Phil Atkinson09/08/02 11:16
RE: Code i am usingderksen08/19/02 10:13
RE: Code i am usingderksen08/19/02 09:10
Prev/Next Button Code FixDave08/18/02 13:57
RE: Display ProblemDave08/18/02 13:54
RE: Code i am usingderksen08/14/02 11:29
Display ProblemLeon Tran08/01/02 22:22
RE: "NEXT 1 2 3 4 5 PREV" Marek07/25/02 16:18
RE: PREV / NEXT WORKING CODETyler07/22/02 17:43
RE: PREV / NEXT WORKING CODETyler07/22/02 16:37
Another improvementPeter Kuiper07/12/02 05:43
I need helpandrew06/29/02 04:40
"NEXT 1 2 3 4 5 PREV" renato06/25/02 05:06
RE: command button to display new pageericka06/21/02 10:21
Validation & Previous buttonAndy Lancaster05/23/02 07:36
RE: How to limit records in ORACLE?lee05/23/02 03:14
Randomized Data and Next and PreviousElizabeth Alderton05/21/02 12:55
how this code works in a function??Ioannis04/28/02 23:06
RE: How to do it in PostgresqlOussama Zarif04/24/02 05:39
RE: Help!!Dan Snik04/22/02 10:51
Help!!Simon04/19/02 15:22
How to do Next 1 2 3 4 Pervious in PostgresqlOussama Zarif04/18/02 06:15
How to do it in Postgresql? Oussama Zarif04/18/02 03:20
RE: And Interbase?????sielim04/09/02 10:09
How to do it in Postgresqlzeus04/05/02 17:25
Please clarify last line of article.c. douglass04/04/02 15:19
Code modification - limiting pages displayedDat03/26/02 17:56
1,2,3...100, 101 nextDerrick03/15/02 23:51
RE: Greate, Suggest me something more..Bhupendra Banodhe03/07/02 05:05
RE: Pb with Next !Ryan Housand03/04/02 16:47
Pb with Next !Aphikit03/01/02 04:58
RE: Hows this code?randy02/19/02 23:04
Variable for $recordLimitGary02/17/02 20:56
RE: Yet another approach (THANKS)Daniel02/13/02 08:12
More efficient: SQL_CALC_FOUND_ROWSMarshall02/09/02 17:32
RE: Code i am usingcarole02/06/02 04:36
RE: Hows this code?KillerX01/24/02 16:11
RE: Too Many Nav Link Pages ProblemDan Snik01/24/02 10:12
Getting Funny Results?Dustin Frost01/21/02 05:39
Performance issueskhayll01/18/02 10:03
RE: PHP Brainiac's eh? "data here" ?? 2UNoY?fabrice01/14/02 15:32
Thanks, Rod!Tina P.01/11/02 10:03
And Interbase?????Elieser Leão01/07/02 11:46
PHP Brainiac's eh? "data here" ?? 2UNoY?Matt01/04/02 18:08
RE: I've made my own, works great with mysql...Toly12/25/01 20:50
RE: Next - Prev for ORA8patinya12/11/01 21:27
RE: How to limit records in ORACLE?patinya12/11/01 21:25
Hows this code?Huw12/06/01 19:26
RE: How to limit records in ORACLE?patinya12/04/01 04:27
Next - Prev for ORA8Pravietis11/27/01 12:12
next/prevpatinya11/21/01 19:02
displaysands11/21/01 11:07
RE: Codemichael11/20/01 01:36
RE: $offset problemVadim11/19/01 13:35
Mistyped?Batara Kesuma11/11/01 03:19
RE: I've made my own, works great with mysql...David Day11/10/01 12:27
Flat FileTom D.11/08/01 00:26
SQL Server 7Mark Stewart11/03/01 08:28
RE: Code i am using -> more results in nextMenno10/31/01 09:36
Code i am usingJester10/21/01 10:49
RE: PREV / NEXT WORKING CODECharles Bailey10/19/01 02:43
little tweekJester10/18/01 19:12
RE: $offset problemMichael O'Neal10/16/01 18:05
RE: Too Many Nav Link Pages Problemanitha10/16/01 15:05
RE: I've made my own, works great with mysql...Rescue910/07/01 22:48
Too Many Nav Link Pages Problemgary09/26/01 19:25
$offset problemStiaan09/20/01 02:56
RE: Another modified script of a scriptgary09/19/01 18:00
RE: Need helpStiaan09/17/01 06:02
thank for your php codechai mei ying09/07/01 02:20
Need helpStéphane Joos09/07/01 01:36
next search resultkimmi09/06/01 20:39
RE: I've made my own, works great with mysql.Jeff09/06/01 16:28
RE: Gah! So inefficient!Michael O'Neal09/06/01 09:25
RE: Gah! So inefficient!sam09/02/01 22:32
RE: Code error = YES and it's fixedFrancis Shanahan09/02/01 08:16
That's it!Stone Cold08/31/01 13:54
Need a php source code joseph chidiac08/29/01 22:21
RE: I've made my own, works great with mysql...manny08/26/01 14:36
RE: I've made my own, works great with mysql...manny08/25/01 10:14
RE: count(*)Steve Grecni08/24/01 11:16
OK. I GIVE UP. Little Help???Michael O'Neal08/21/01 17:02
How to add location info like "viewing 10-19"Shawna Gibbs08/19/01 16:04
RE: I've made my own, works great with mysql...gary08/03/01 15:47
RE: I've made my own, works great with mysql...Andrew Taylor07/31/01 03:43
RE: SQL Server 7Khunnee07/29/01 18:22
RE: I've made my own, works great with mysql...Jaret07/21/01 20:52
RE: SQL Server 7Khunnee07/19/01 20:42
RE: count(*)TMN07/17/01 06:20
sorting in 3 rowskid07/05/01 22:11
little thoughtLance Winter07/05/01 01:02
Random result set & NEXT/PREVElek07/04/01 01:31
Code error?Lenny06/27/01 20:06
RE: I've made my own, works great with mysql...john06/21/01 10:36
How to get the position of an RecordPeter06/21/01 05:03
Please Help :(Greg:)06/20/01 15:07
RE: SQL Server 7Tim G06/15/01 20:49
RE: SQL Server 7dunsun06/07/01 05:50
Oracle Situationchris05/29/01 13:28
RE: How to limit records in ORACLE?jaewook Kim05/28/01 17:02
Doing it in Oracle?kaiser05/14/01 13:09
RE: Another modified script of a scriptAdam S05/07/01 17:09
RE: I've made my own, works great with mysql...Adam Spigel05/07/01 15:28
RE: How to limit records in ORACLE?KnightHawk05/07/01 11:22
RE: I've made my own, works great with mysql...mrgibson05/04/01 04:30
How to limit records in ORACLE?kaiser05/03/01 10:23
With Oraclekaiser05/02/01 15:41
few more questions :)fandelem04/24/01 03:31
RE: Gah! So inefficient!YeeHaW Jelte04/17/01 05:29
RE: Gah! So inefficient!Joe Laffey04/16/01 15:51
RE: Try this (correction)Mathew04/06/01 08:34
RE: I've made my own, works great with mysql...copon04/06/01 07:21
RE: I've made my own, works great with mysql...copon04/04/01 07:31
How can we do with Oracle?Papi LO04/02/01 11:00
parameters for where conditional (?)David Norton04/01/01 01:54
so far so good but one catch.Marc03/29/01 12:59
RE: Try this (correction)Mikel Williams03/23/01 13:11
Problem with SQL code?Gabriel03/19/01 12:07
what if no mysql?tuxpow3r03/15/01 01:11
RE: Is there something wrong with this snippet?rod k03/14/01 12:45
Is there something wrong with this snippet?Liam03/13/01 14:42
RE: I am having trouble. Please help me.Mr T03/13/01 05:21
RE: count(*)Brent03/12/01 21:17
count(*)Ridge03/12/01 17:04
thanks rodbryan03/09/01 11:59
try this for prevbryan03/09/01 11:38
Passing Records thru pages in ORACLERicardo Vercesi03/09/01 09:32
1.000 links ?Teo Danardi03/03/01 12:14
RE: Can the sql be executed once?Vladimir Shapiro02/26/01 01:14
Please helpRowan Trimmer02/19/01 07:29
RE: I've made my own, works great with mysql...Jimbo02/16/01 22:01
What am I doing wrong?Bob02/15/01 18:43
Can the sql be executed once?Alex Lam02/15/01 05:16
RE: global variables valueJaved02/13/01 01:43
Last result on page repeats itselfTom Garcia02/07/01 16:06
RE: I've made my own, works great with mysql...ateng02/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...Robert02/02/01 03:46
Good!!! Thank you RodJorge Terrero01/30/01 15:53
RE: I've made my own, works great with mysql...Danny Hill01/28/01 15:46
I've made my own, works great with mysql...David01/23/01 14:07
CooollDendy Bagus S01/20/01 01:26
limit --- in oracleParas pradhan01/16/01 05:55
RE: Gah! So inefficient! - Toddrod k01/15/01 09:10
RE: Gah! So inefficient - Johnrod k01/15/01 09:03
RE: global variables valuerod k01/15/01 08:48
RE: Passing Query String Bug <security prob>Mark Kinsella01/14/01 04:16
RE: <Mike D.> Yet another approachPieter W.D.Kroon12/30/00 23:10
Thanks Rod!Vince12/30/00 19:21
RE: <Mike D.> Yet another approachzeny ortiz12/25/00 22:01
global variables valuesanddy cepero12/23/00 11:10
RE: Yet another approach... small problemChris Meiering12/20/00 16:58
How implent it in OracleJesús Carmona12/19/00 11:37
RE: Yet another approachMike D.12/15/00 10:09
RE: Gah! So inefficient!Thomas Hurst11/28/00 11:29
Yet another approachzeny ortiz11/28/00 03:21
RE: Gah! So inefficient!Todd F incannon11/16/00 11:49
RE: Gah! So inefficient!Jason Boyd11/15/00 12:27
RE: Passing Query String BugMichael I. Buen11/15/00 08:47
Gah! So inefficient!John Robinson11/14/00 07:04
Try this (correction)B. Angelo Molizane11/06/00 11:52
The Next LinkRichie TM11/04/00 09:52
I only see one messageRick Waalders10/31/00 10:16
RE: problem with mysql query adn those scriptRoy cowboy10/30/00 00:07
RE: Small Problem - FIXJohn Bloomfield10/16/00 02:17
and what if?Lukas Smith10/06/00 06:22
Passing Query String BugJames09/29/00 17:17
RE: Missing rows under 20 ...Caroline Gaceru09/25/00 03:54
Missing rows under 20 ...Eyal09/19/00 14:58
RE: improved versionKimmie Dicaire09/14/00 20:59
improved versionScott Molinari09/10/00 10:26
problem with mysql query adn those scriptTorsten Schrammen09/08/00 12:14
RE: LIMIT function in MS SQLMike Squires09/04/00 09:12
RE: What is empty?Matthew Howey08/29/00 15:38
What is empty?Chris Williams08/28/00 21:29
LIMIT function in MS SQLGreg Sohl08/18/00 10:11
pages being miscalculated - w/fix (I think)Mark Donchek08/06/00 21:52
LIMIT function missingJohn Schuhr08/06/00 17:28
$offsetMark Donchek08/06/00 16:04
RE: Small Problem - FIXTim Patterson08/01/00 00:26
RE: Small Problem - FIXJonathan Griffin07/19/00 15:51
Updated VersionBrett Stimmerman07/18/00 16:51
Small Problemchris07/14/00 02:36
SQL Server 7Tim Patterson07/06/00 16:35
Missing Stuff! (You might need this..)Tim Patterson07/06/00 16:21
Past comments????Alex Darke06/30/00 22:19
SQL Server 7Ian Evans06/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.