?
Solved

Multi-page database search results display

Posted on 2003-02-26
6
Medium Priority
?
169 Views
Last Modified: 2013-12-25
Hello.

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.

Thanks.
0
Comment
Question by:dda
[X]
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
6 Comments
 
LVL 17

Accepted Solution

by:
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.
0
 
LVL 4

Author Comment

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

Expert Comment

by:dorward
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:
  http://gentle.org.uk/howto/xmetapostgres.htm

Turn JavaScript on and toggle the radio buttons on the final textarea to see equilivent code.
0
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?

 
LVL 4

Author Comment

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

Assisted Solution

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

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
     )
     loop
          row_count := row_count +  1;
          -- print the variables you want
          if ( row_count = my_number_of_row_required ) then
               exit;
          end if;
     end loop;
end;

=============================
0
 
LVL 4

Author Comment

by:dda
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?
0

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