1 | 2

6 - 7   [7]

Selecting MySQL Next And Previous Revisited

Relates to MySQL and Databases

The other day I discussed a simple technique for selecting the next and previous records from a MySQL database using variables. Here is a little tip for extending this to limit the results to a defined number of most recent entries.

Having selected the unique ID for the next and previous records, as discussed in the previous blog entry, another variable can be defined which counts the number of records posted more recently than the previous record. Then the previous entry is only returned if it is within a predefined boundary for recent entries. Here is the full SQL:

# set the boundary to the most recent five results
SET @lower_bound := 5;

# set the id of the previous entry
SELECT @prev := uid 
       FROM table 
       WHERE uid < {x} 
       ORDER by uid DESC 
       LIMIT 0, 1;

# set the id of the next entry
SELECT @next := uid 
       FROM table
       WHERE uid > {x}
       ORDER by uid ASC
       LIMIT 0, 1;
# get the number of records posted more 
# recently than the previous record
SELECT @records := COUNT(uid)
       FROM table
       WHERE uid > @prev
       ORDER by uid DESC;

# create a return result array, but only include
# the previous record if it falls within the boundary
# of most recent records
SELECT IF(uid = @prev,'p','n') as pos, uid, title 
       FROM table
       WHERE (uid = @prev AND @records < @lower_bound) 
              OR uid = @next

A typical application of this solution is distinguishing latest news items from the news archive in a small scale News Desk (i.e. where new items may only be added occasionally so the date is not a reliable factor for determining the latest posts). By integrating a couple of simple mod-rewrite rules in the configuration or .htaccess file, and a cache module (eg PEAR::Cache_DB) to compensate for the additional database overhead a complete News Desk with latest items and archive items can be readily simulated through a single source.

Posted on Feb 08, 2004 at 04:30:40. [Comments for Selecting MySQL Next And Previous Revisited- 1]

CSS Zoom Factor

Relates to Accessibility and CSS Design

Recently I have been considering the CSS Zoom Factor (or Full Page Zoom) as a very viable option for controlling the size of the root container in a design layout. There are several examples of this technique on the CSS Wiki, and it has already produced extensive discussion on Simon Willison's Blog but it does not seem to have been widely adopted as an alternative to fixed width or percentages yet.

In short, CSS Zoom declares the root container dimensions in a unit relative to the font size (em). This allows the whole page to scale up and down when font-size is adjusted. The new design of my business (and blog) site utilise this method. I actually stumbled on it by chance initially - one of those trial and error situations - but as the design developed, it became clear that this method resolved a number of limitations of fixed dimensions and percentage dimensions:

  1. Text breaking out of columns above several levels of magnification.
  2. Resolution limitations - fixed dimension columnar designs can become squashed and barely legible at super high resolutions with several magnifications of zoom. Percentage dimension designs will resize to the resolution, but they suffer the same problem at a single resolution when font is increased extensively. Similar problems can arise when down sizing resolution and font-size.
  3. Precise positioning can easily be broken in fixed and percentage dimensions. A typical example I have experienced several times is the absolute placement of a horizontal navigation bar. As font size increases the content may fold over to two lines, hence breaking the synchronisation of following content..

CSS Zoom also has disadvantages:

  1. Images do not resize with the text (except Opera's internal zoom mechanism), so blank spaces will soon appear where the design is image specific.
  2. Nesting fixed width elements (eg floated elements) inside the relative parent can become impossible.
  3. If a user is re sizing the font to a very large size at a small resolution, much of the page is going to be forced out of the viewable area requiring horizontal scrolling.

I think the last point mentioned above is a major issue. There are likely to be two groups of users who resize fonts - those with extra large screen resolutions and those with poor visibility. The application of CSS Zoom is ideal for the first group but forcing an impaired user to scroll horizontally impinges negatively on the accessibility of a page. Arguably, many fixed width/percentage width designs can become quite illegible at extra large font-sizes anyway (3 to 4 times zoom), while the Zoom factor will still maintain its form. Also with careful design planning, a columnar layout can allow a single column to display clearly within the viewable area at high resolutions.

The three column layout displayed on Severn Solutions has been designed to take this into account. As an example, the home page can be resized in Firebird to five times magnification at 1024 by 768 screen resolution and allow each column to be read clearly with only vertical scrolling, once it has been screen centred. While the larger blog column layout can be resized to three times magnification without the need for horizontal scroll.

UPDATE - 2004-02-10. Recent testing has shown that a centre-aligned root container in the document body must have a border declared to prevent the container from disappearing off the left edge of the screen as the font is increased.

Posted on Feb 08, 2004 at 04:29:35. [Comments for CSS Zoom Factor- 0]

Breadcrumbs Trail

[ Home ] -> TW Blog -> Archives for February 2004
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.007 seconds