sortable_table db select_string display_spec vars_to_export sort_var current_sort_order { table_length " " } { extra_table_parameters " " } { stripe_color_list " " } { max_results " " } { header_font_params " " } { row_font_params " " }What it does:
Procedure to format a database query as a table that can be sorted by clicking on the headers. Arguments are:Defined in: /web/philip/packages/acs-core/utilities-procs.tcl
- db: database handle
- select_string: SQL statement that selects all columns that will be displayed in the table.
- display_spec: a "display specification" that consists of a list of column specs. Column specs are lists with the following elements:
- primary column name (name of column which determines sorting for this table column)
- header (header to display for this column)
- display string (optional; if provided, a string with variable references to column names that will be interpolated for each row)
- default sort order (optional; really used to say when something needs to sort "desc" by default instead of "asc")
- column width (optional).
- vars_to_export: an ns_set of variables to re-export to the current page. Generally, [ns_conn form]
- sort_var: a variable name which stores the sorting information for this table. You can use different sort_vars for multiple sortable tables in the same page.
- current_sort_order: a list of column names that determine the current sorting order. Each element is either a column name that can be optionally followed by " desc" to specify descending order. Generally, just the current value of $sort_var.
- table_length (optional): where to insert table breaks. Leaving unspecified or empty specifies no table breaks.
- extra_table_parameters: Any extra parameters to go in the <table> tag
- stripe_color_list: a list of color specifications for table striping. If specified, should specify at least two, unless a single color is desired for every row.
- max_results (optional): Indicates to truncate table after so many results are retreived.
- header_font_params (optional): Sets the font attributes for the headers.
- row_font_params (optional): Sets the font attributes for any old row.
Source code:
# Run the SQL
set order_clause ""
if { ![empty_string_p $current_sort_order] } {
set order_clause " order by [join $current_sort_order ","]"
}
set selection [ns_db select $db "$select_string$order_clause"]
# Start generating the table HTML.
set table_start "<table $extra_table_parameters>\n"
set table_html ""
set primary_sort_column [lindex $current_sort_order 0]
# Put in the headers.
set headers "<tr>"
foreach col_desc $display_spec {
# skip any blank columns
if { [llength $col_desc] < 1 } { continue }
set primary_column_name [lindex $col_desc 0]
# set the default sort order
set primary_column_sort ""
if { [llength $col_desc] > 3 } {
set primary_column_sort "[lindex $col_desc 3]"
}
set column_header [lindex $col_desc 1]
# Calculate the href for the header link.
set this_url [ns_conn url]
set exported_vars [export_ns_set_vars "url" $sort_var $vars_to_export]
if { ![empty_string_p $exported_vars] } {
append exported_vars "&"
}
set just_the_sort_column [lindex $primary_sort_column 0]
set sort_icon ""
if { $primary_column_name == $just_the_sort_column } {
# This is the column that is being sorted on. Need to reverse
# the direction of the sort by appending or removing " desc".
# Relies on the fact that indexing past the end of a list
# is not an error, just returns the empty string.
# We're treating a string as a list here, since we know that
# $primary_sort_column will be a plain column name, or a
# column name followed by " desc".
if { [lindex $primary_sort_column 1] == "desc" } {
append exported_vars "$sort_var=[ns_urlencode [sortable_table_new_sort_order $current_sort_order $just_the_sort_column]]"
set sort_icon "<img border=0 src=\"/graphics/up.gif\">"
} else {
append exported_vars "$sort_var=[ns_urlencode [sortable_table_new_sort_order $current_sort_order "$just_the_sort_column desc"]]"
set sort_icon "<img border=0 src=\"/graphics/down.gif\">"
}
} else {
# Clicked on some other column.
append exported_vars "$sort_var=[ns_urlencode [sortable_table_new_sort_order $current_sort_order "$primary_column_name $primary_column_sort"]]"
}
if { [empty_string_p "[lindex $col_desc 4]"] } {
append headers "<th>"
} else {
append headers "<th width=\"[lindex $col_desc 4]\">"
}
append headers "<a href=\"$this_url?$exported_vars\"><font face=\"helvetica,verdana,arial\" $header_font_params>$column_header</font>$sort_icon</th>"
}
append headers "</tr>\n"
# Do the data rows.
set i 0
set color_index 0
set n_colors [llength $stripe_color_list]
set n_results 0
while { [ns_db getrow $db $selection] } {
set_variables_after_query
# check to see if we have reached our max results limit
if { [exists_and_not_null max_results] } {
if { $n_results >= $max_results } { break }
incr n_results
}
# Handle table breaks.
if { $i == 0 } {
append table_html "$table_start$headers"
} elseif { ![empty_string_p $table_length] } {
if { $i % $table_length == 0 } {
append table_html "</table>\n$table_start$headers"
set i 0
}
}
# Handle row striping.
if { ![empty_string_p $stripe_color_list] } {
append table_html "<tr bgcolor=\"[lindex $stripe_color_list $color_index]\">"
set color_index [expr ($color_index + 1) % $n_colors]
} else {
append table_html "<tr>"
}
# Handle each display column.
foreach col_desc $display_spec {
# skip any blank columns
if { [llength $col_desc] < 1 } { continue }
set primary_column_name [lindex $col_desc 0]
set col_display [lindex $col_desc 2]
if { [empty_string_p $col_display] } {
# Just use the sort column as the value.
set col_display "\$$primary_column_name"
}
# Insert for empty rows to avoid empty cells.
set value [subst $col_display]
if { [empty_string_p $value] } {
set value " "
}
append table_html "<td><font face=\"helvetica,verdana,arial\" $row_font_params>$value</font></td>"
}
append table_html "</tr>\n"
incr i
}
ns_db flush $db
if { ![empty_string_p $table_html] } {
append table_html "</table>"
}
return $table_html