Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Multi-page database search results display

Posted on 2003-02-26
6
Medium Priority
?
170 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
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

571 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