Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Browsing a sql table on the web

by BerntB (Deacon)
on May 13, 2006 at 11:54 UTC ( #549212=perlquestion: print w/replies, xml ) Need Help??

BerntB has asked for the wisdom of the Perl Monks concerning the following question:

I have a standard problem -- browse data stored in sql tables.

The interface is a web browser. There will always be a relevant index on the sql table.

The user should be able to see the first 10-40 items, go forward or backwards in the list, etc. If the user goes for lunch, s/he should be able to just continue a browsing session afterwards. (In sum, just like browsing Questions or Meditations on PM.)

My idea is to make a representation of how far into the table the user was and save that in the Session information. When the user asks for the next/previous page of data, then the program does a new sql request.

Since most web applications has this kind of functionality, I wonder how it is usually implemented? I must have a simpler problem, since I don't need temporary tables with complex search results (I hope).

(Update: Made it a little bit clearer.)

Replies are listed 'Best First'.
Re: Browsing a sql table on the web
by eXile (Priest) on May 13, 2006 at 13:24 UTC
    A simple way of doing this is using 'LIMIT/OFFSET' type of SQL statements. I don't think they are in the official SQL specs, but most SQL servers have some kind of way to just return a limited subset of what you want. MySQL for instance uses this syntax:
    to limit your query to items 100-139 in the BLAH table. This makes it easy to write a pager for a table, see for instance Class::DBI::Plugin::Pager, which is a pager for the Class::DBI framework.

    This approach doesn't take insertion of rows into account, which may or may not be a problem, depending on how and how often your table changes.

Re: Browsing a sql table on the web
by jonadab (Parson) on May 13, 2006 at 13:26 UTC

    As far as where to store the information about how far along through the list the user was, I generally prefer to put it in the next/previous links themselves as a query string argument. I usually also put in an option for how many to show per page, and since you want the user to be able to change that partway through, it implies that the where-to-start argument should be based on the actual position in the list, not the number of pages.

    The hard part relates to the design of how you're going to order (sort) the records. In particular, there are significant performance benefits if you can get the query to only return the records you actually want, rather than having it return all of them and then throwing out the irrelevant ones in program logic. However, that requires that some knowledge of the order you are using be put into the SQL, not just in terms of an ORDER BY clause but also in terms of constructing a WHERE clause to only grab the records you want. (If your SQL dialect supports things like SELECT TOP 50 then the WHERE clause only has to tell it where to start.) This is generally straightforward for a specific table where you know your data, but doing it in a general way is a more interesting exercise. One supposes your query will have to have a field name interpolated at prepare time, and then of course you'll bind a value at execute time. The value can be one of the query arguments contained in the next/previous links, but the field name has to come from somewhere as well, and you may want to pre-select it (or at least pre-select a default for it) and store that in some configuration file.

    Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. Why, I've got so much sanity it's driving me crazy.
Re: Browsing a sql table on the web
by TedPride (Priest) on May 13, 2006 at 20:05 UTC
    The following will display records 41-80. The first number is the starting point; the second how many records to return (if possible).
    SELECT <fields> FROM <table> WHERE ... LIMIT 41, 40
    As for saving where you were in the current session, don't bother with fancy session doohickies. Just take the ID of the latest record so far in the database (MAX(ID)?) before you do the first query, and add that to the page 2, 3, etc. page urls. Then you'd do something like this:
    SELECT <fields> FROM <table> WHERE id <= 34534 && ... LIMIT 41, 40
Re: Browsing a sql table on the web
by neniro (Priest) on May 13, 2006 at 12:37 UTC
    You could define a table-object, with a render-method you could call from TT or HTC. The table-object has a resultset that you could store using Pixie. As most webapps are stateless (ignoring fancy AJAX-stuff), you must use kinda session-keys (or the cookie Pixie gives you) and pass that between the states your table-object could handle.
      Thanks, but I get the part about persistence.

      (-: Well, I was stupid enough to write my own storage engine instead of using Pixie etc, but this is partly a hobby project. :-)

      What I need is a neat and general way to get back to the same place in a sql table.

Re: Browsing a sql table on the web
by BerntB (Deacon) on May 13, 2006 at 13:47 UTC
    Thanks 'eXile', 'jonadab'!

    (-: But before I touch my program again, I'll buy/borrow a sql book less than a decade old and check it for more features that will improve my life/code quality! :-)

Re: Browsing a sql table on the web
by pajout (Curate) on May 14, 2006 at 09:42 UTC
    As TedPride wrote, the usage of LIMIT and/or OFFSET in sql query can help. My experience is that the real implementation very depends on GUI abilities, so I would like to roughly explain my requirement and implementation.
    The user can navigate through very large table. He must be able to move to the first and last (!!!) page, he must know index of current page and he must be able to jump forward and backward not only one page, but also 5, 10, 50, 100, ... pages (to be able to "divide and conquer" when finding something by eyes).
    The challenge is related with db->program data flow. In PostgreSQL 7.x, which we used via DBI, we are able to use LIMIT and OFFSET, but we are not able to get the total count of rows when LIMIT is used. So I decided to not use LIMIT and let the sql machine construct the all rows (from OFFSET to the end). Hopefully, in that combination of PostgreSQL+DBI I am able to run that query, fetch only 100 rows in one command and get information about total rowcount of this query.
    So my html form posts desired index of page (for instance 47th of 112) and my perl subroutine counts proper OFFSET, runs the query, fetches only 100 rows, create some array like (0, 36, 41, 46, 51, 56, 96, 111) which is in HTML interpreted as ('first', -10, -5, 'current', +5, +10, +50, 'last')
    A little bit tricky, but it works.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://549212]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2020-07-09 11:36 GMT
Find Nodes?
    Voting Booth?

    No recent polls found