xTxT Documentation

TableSql

Generates a table from any SQL-compliant query source, such as SQL Server, Oracle, or MySql. If you can define a valid database connection string to connect to the data source, you can query it.

/tablesql
$conn=(database connection string)
(sql query begins here)

Settings

$conn
(required) The database connection string.

Content

This element requires content, which is the SQL SELECT query that will populate the table.

Special Content Capabilities

The /tablesql element allows you to use specially-named columns in your SQL SELECT, which affect the generated output.

In general, any

Special columns include those with any of the following column names;

_link
Any column with this name describes a hyperlink that should be applied to the content of the following column.
_target
Any column with this name describes the target of the generated link. This column is always used with the _link column. If no _target is specified, the default target will be _self.
_css
A CSS string applied to the following column. This is applied to the generated <TD> element.

Examples

/tablesql
$conn=my connection string
select *
from mytable
/tablesql
$conn=my connection string
select 
'http://google.com' _link,
title
from mytable
/param match
zzz
/tablesql
$conn=server=(local);user id=bob;password=dzooba1;database=mydb
select 
	'font-weight: bold;' _css,
	'http://' + h.Code _link,
	'_blank' _target,
	h.Code,
	w.Name
from 
	[Host] h
	join [Wiki] w on w.WikiID = h.WikiID
where 
	h.Enabled = 1
	and w.Enabled = 1
	and h.Code like '%{match}%'
order by
	h.Code asc

The /param element can be used to modify the SQL content;

To do this, declare a param element, and in your sql, use the corresponding param element name(s) in curly braces like {this}. The param value will be embedded as a literal.

CASE can be used to control styling programmatically;

case 
		when f.Enabled = 1 then 'background-color: #99ED6F; font-weight: bold;'
		else 'background-color: #D0D1D6'
		end _css,

Notes

A header row is always generated. It can be hidden using the CSS display: none; style.