Re: Fwd: DBI sql and pagination for a flat-file
From: Jeff Zucker (jeff_at_vpservices.com)
Date: 05/27/04
- Previous message: Leegold: "Fwd: DBI sql and pagination for a flat-file"
- In reply to: Leegold: "Fwd: DBI sql and pagination for a flat-file"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 26 May 2004 18:25:05 -0700 To: leegold <leegold@fastmail.fm>
leegold wrote:
> If someone could just tell me very schematically what they
> think...I'm not looking for anyone to write the code for me.
> ie. what's the best way to paginate a very big flat-file db?
You'll get better results for general CGI questions on perlmonks or
comp.lang.perl.modules. The part of your question that has some DBI
relevance is that AnyData supports the LIMIT clause using the same
syntax as MySQL so that you can easily make requests that gather say
only records 20 through 40 of a given search resultset.
SELECT id,title,url
FROM pages
WHERE contents LIKE '%keyword%'
ORDER BY id
LIMIT 20,20
-- Jeff > Actually it's looking like: > http://www.stonehenge.com/merlyn/WebTechniques/col02.html > to get pagination on a web page. Ok, I got more reading to do... > > > On Wed, 26 May 2004 09:47:44 -0400, "leegold" <leegold@fastmail.fm> said: > >> I can access a flatfile with the DBI sql and have the pager "working", >>But I'm not sure how to blend them. In reality i'll have a flat file >>with over 1000 recs eventually paginated in CGI. There are some points >>that confuse me. my newbie 1st try see below. >> >>*Question:* What I really want to do: If I just read the flatfile into an >>array, or used tie-file, the pager as it is, would make more sense. But I >>want to interface with the AnyData and use SQL - keep it on that level, >>abstracting the fact I got a text file db. So, is there a way I can >>fetch via the DBI/SQL *only* the recs I need for the pager - ie as the >>pager needs them - more dynamically vs. a static monolithic array that >>the >>pager feeds off of as it needs. So DBI/SQL fetching 3 recs at a time per >>below to get "stuff" for the pager. Wouldn't that be the way to go? How >>can i do this? >> >>---------------------code----------------------------- >>#!/usr/bin/perl -w >> >># use strict; >>use DBI; >>use Data::Page; >>use Tie::File; >> >>my $file_name = 'H:\threesharp\datatest.threesharp'; >>my $table_name = 'pips'; >> >>tie my @rec_array, 'Tie::File', $file_name; >>my $n_rec = @rec_array - 1; >> >>my $dbh = DBI->connect('dbi:AnyData(RaiseError=1):'); >>$dbh->func( $table_name, 'ThreeSharp', $file_name, 'ad_catalog'); >>my $sth = $dbh->prepare("SELECT * FROM $table_name"); >>$sth->execute(); >> >>while (my @row = $sth->fetchrow_array) { >> print "@row\n"; >>} >>print "\n\n"; >>my $i=1; >>my $last; >>do { >> my $page = Data::Page->new($n_rec, 3, $i); >> $i++; >> print " Current Page: ", $page->current_page, "\n"; >> print "First entry on page: ", $page->first, "\n"; >> print " Last entry on page: ", $page->last, "\n\n"; >> $last =$page->last_page; >>} until $i > $last; >> >>-----------------------output--------------------------- >> >>H:\THREES~1>perl ThreeSharp.pl >>o20473418 5th dimensional relativistics Johnson City, N.Y. >>o09222777 A.S.M. review of metal literature Cleveland, Ohio >>ocm13035605 AAOHN journal Atlanta, Ga. >>ocm03569079 AAPG bulletin Tulsa, Okla. >>o03050993 AAS photo-bulletin Gainesville, Fla., >>ocm10445058 AAVSO alert notice Cambridge, MA >>o02884114 Abhandlungen der Akademie Gottingen,Vandenhoeck >>o01478689 Abhandlungen der Deutschen Berlin >>o05149137 American Mathematical Providence, R.I. >>ocm00853306 Geological Society of America Boulder, Colo. >>ocm38492184 Accident analysis and prevention New York, NY >>ocm41383033 Accounting Journal Bradford, England >>ocm37342142 Accounts of chemical research Easton, Pa. >>o01731026 ACM computing surveys New York >> >> >> Current Page: 1 >>First entry on page: 1 >>Last entry on page: 3 >> >> Current Page: 2 >>First entry on page: 4 >>Last entry on page: 6 >> >> Current Page: 3 >>First entry on page: 7 >>Last entry on page: 9 >> >> Current Page: 4 >>First entry on page: 10 >>Last entry on page: 12 >> >> Current Page: 5 >>First entry on page: 13 >>Last entry on page: 14 >> >> >>H:\THREES~1> >> >>-- >>http://www.fastmail.fm - Sent 0.000002 seconds ago > >
- Previous message: Leegold: "Fwd: DBI sql and pagination for a flat-file"
- In reply to: Leegold: "Fwd: DBI sql and pagination for a flat-file"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
