Multi-page database search results display

Posted on 2003-02-26
Medium Priority
Last Modified: 2013-12-25

I need to do a query against some database and display results row by row with CGI (perl) script. It's simple. But if I need to show only a certain portion of results, and then provide "next>>" and "<<prev" links? What technique can be used for this task? Temporary tables with search results? Too expensive.

Database can be Oracle or MySQL.

Question by:dda
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 17

Accepted Solution

dorward earned 400 total points
ID: 8025048
Where $number is the number of rows you want to return, and $offset is the number of rows to skip (a multiple of $number for paged data).

SELECT * FROM data ORDER BY id DESC LIMIT $number OFFSET $offset

Add one to the number to generate the link to the next page, subtract one for the link to the previous page.

Author Comment

ID: 8025105
Ok, and what about Oracle Database? Will LIMIT and OFFSET work for conditioal queries effectively?
LVL 17

Expert Comment

ID: 8025151
Offset and Limit certainly work in MySQL, but a google search _suggests_ that Oracle doesn't have this functionality.

A google search turns up:

Turn JavaScript on and toggle the radio buttons on the final textarea to see equilivent code.
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?


Author Comment

ID: 8025217
Thanks for the link! You answer is good.
But I'd like to hear about other possible approaches as well.

Assisted Solution

amitabhrai earned 400 total points
ID: 8028759
I guess this should work; I have not tried it though.

my_id := argument_passed ; // for pag1=0 , for any other page page=id of last displayed record on previous page
my_number_of_row_required := argument_passed ;

row_count := 0;
for my_rec in (
     select id, f1, f2, from my_table where id > my_id
     order by id
          row_count := row_count +  1;
          -- print the variables you want
          if ( row_count = my_number_of_row_required ) then
          end if;
     end loop;


Author Comment

ID: 8031384
Hmm.. What is 'id' here? I'm selecting rows from several tables (joined), filtered and ordered. I think that using 'id' is not possible here, unless you are talking about some temporary table with search results, already ordered the way I need.

Any other ideas? Platforms, frameworks?

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question