Published: Wednesday, October 27, 1999
Ordering Links Taken from a Database
By Bob
I set up the admissions site at my college as what I like to think of as
a web application as opposed to a bunch of web pages. It is an ASP application that
pulls all of its information- menu titles and links, sub menu titles and
links, and page content- from a database. The information retrieved from the database is
determined by parameters passed through the query string. All of the menu links on the page link
back to this main page with a query string. For example, when you click
on a main menu link on the nav. bar, the page reloads with the main menu
item's corresponding content and all of the sub menu links listed under
the main menu link. If you are interested in how the tables are set up
or the links are generated, please email me.
There are administration pages that allow non-computer savvy people to add and
remove menu items and content to and from the database, thus modifying
the web site. These people were not satisfied with the menu items simply
being displayed in alphabetical order. They wanted to come up with
their own order.
I started out by adding an additional numerical field to the menu tables
in the database called zorder. A text box corresponding to this field
was added to the admin pages and the main page was modified so that
when it loaded the menu links, it sorted them by their zorder field first
and then their title field, whereas before they were sorted only by the
title field. If the user left the zorder field blank, I could have done
one of two things: give that item the next available zorder number
making it the last in the list; or give all items with blank zorder
values the same maximum zorder value thus making them all be displayed
alphabetically at the end.
I would have liked to just leave it at this, but I foresaw two problems:
these same non-computer savvy people having problems with the idea of
using a zorder field on the admin pages, and I would get tired of
manually updating all of the zorder values every time I wanted to insert
a menu link.
First of all, I labeled the zorder field on the entry and edit forms
Menu Position and I used a drop down box of numbers to prevent users
from entering erroneous values. The drop down box contained the numbers
from 1 to the maximum zorder number and defaulted to the last possible
number (1 plus the maximum zorder numer) when they were adding a new
item and to the existing zorder number when they were modifying an item.
If the user enters a zorder number that is already in use, you will have
to update the following zorder numbers accordingly. Given that
selectedzorder refers to the zorder number (menu position) that the
user selected for the current record then the following SQL query will
perform the required update on the following menu records:
Update main_menu set zorder=zorder+1where zorder >= selectedzorder;
|
To retrieve the maximum zorder value for use in your drop down box you
could use this query:
Select Max(zorder) from main_menu;
|
The same rules apply to the sub menu table with obvious modifications.
Happy Programming!
