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