Location: PHPKode > scripts > dbHtmlTable > dbHtmlTable.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" >
<head>
<title>dbHtmlTable (PHP class)</title>
<style type="text/css">
p,td,th,li,a { font-family:verdana,arial,sans-serif; font-size:13px; }
h1,h2 { font-family:verdana,arial,sans-serif; }
h1 { font-size:24px; color:#006; }
h2 { font-size:18px; color:#006; }

table { border:1px solid #ccc; }
th { border-bottom:1px solid #aaa; text-align:left; background:#f0f0f0; }
td { border-bottom:1px solid #ccc; vertical-align:top; padding:2px 5px; }
.section { background:#ddf; }
.section td { font-size:16px; font-weight:bold; padding:10px 5px; padding:10px 5px; }
pre { margin-left:20px; background:#eee; width:650px; padding:5px; }
pre.clean { background:#fff; margin:5px 0; padding:0; }
.indent { margin:0; margin-left:30px;}
.mono { font-family:monospace; }
tt { background:#f0f0f0; }
#nav { float:right; background:#ddf; padding:2px 5px; position:fixed; top:2px; right:10px; }
</style>
</head>
<body>
<a name="top"></a>
<div id="nav">
    <a href="#top">(top)</a> | <a href="#gen">General options</a> |
    <a href="#row">Row options</a> | <a href="#cel">Cell options</a> | <a href="#lnk">Link options</a>
</div>


<h1>dbHtmlTable (PHP class)</h1>


<p>dbHtmlTable is a PHP class that returns or echoes an html table from a database query result.</p>
<ul>
    <li>Requires PHP5 or later (but could be converted for use with PHP4)</li>
    <li>Requires an existing database connection</li>
    <li>Currently supports MySQL only, but easily modified for other data sources (see dbHtmlTable.class.extend.php)</li>
</ul>

<p>See also: 
    <a href="http://tweezy.net.au/doco/dbHtmlTable/">more usage examples</a> and the
    <a href="http://tweezy.net.au/dbHtmlTable.html">dbHtmlTable download page</a>
</p>
    
    
<h2>PUBLIC METHODS</h2>
<pre>__construct ..... called when instance is created
createTable ..... return or echo the html table (depending on EchoHtml option)
getRowCount ..... return the number of rows (only after calling createTable)
getColumnCount .. return the number of columns (only after calling createTable)
setQuery ........ change the SQL query string
setOption ....... set a single option
setOptions ...... set one or more options
resetOptions .... reset all options to defaults</pre>


<h2>USAGE</h2>
<p>After connecting to your database, a table can be generated as follows:</p>
<pre>$HtmlTable = new dbHtmlTable(sqlString, optionsArray);
$HtmlTable->createTable();</pre>
<p>or</p>
<pre>$HtmlTable = new dbHtmlTable(sqlString);
$HtmlTable->setOptions(optionsArray);
$HtmlTable->createTable();</pre>
<p>or</p>
<pre>$HtmlTable = new dbHtmlTable(sqlString);
$HtmlTable->setOption(optionName, optionValue);
$HtmlTable->setOption(optionName, optionValue);
$HtmlTable->setOption(optionName, optionValue);
$HtmlTable->createTable();</pre>
<p>or some combination thereof. It's also possible (though not necessarily recommended!) to set everything 
at once and echo the table in one line, by adding a third "force" parameter:</p>
<pre>$HtmlTable = new dbHtmlTable(sqlString, optionsArray, true);</pre>

<p>The class object can be re-used simply by calling the setQuery() method:</p>
<pre>$HtmlTable->setQuery('select MORE from anothertable');
$HtmlTable->createTable();</pre>

<p>Calling setQuery() automatically resets some options (those that apply to fields in the previous query, eg field alignment, etc)</p>
<p>See <a href="http://tweezy.net.au/doco/dbHtmlTable/">more usage examples</a></p>


<h2>OPTIONS</h2>
<p>The following table documents all the options supported by dbHtmlTable.</p>
<ul>
    <li>Option names are NOT case sensitive.</li>
    <li>Some options accept a <b>comma-separated list of fields</b> (HideFields, AlignRight, CellClass, etc).
        For these:
        <ul>
            <li>fields can be specified either by their numerical position or fieldname</li>
            <li>fieldnames ARE case sensitive</li>
            <li>the special keyword FIRST (any case) means the first field in the row</li>
            <li>the special keyword LAST (any case) means the last field in the row</li>
        </ul></li>
    <li>Some option names have aliases/shortcuts, as noted below.</li>
</ul>

<table cellspacing="0">
    <tr class="section"><td><a name="gen"></a>&nbsp;</td><td colspan="4">General options</td></tr>
    <tr>
        <th>Option name</th>
        <th>Type</th>
        <th>Description</th>
        <th>Default</th>
        <th>Alias</th>
    </tr>
    <tr>
        <td>EchoHtml</td>
        <td>boolean</td>
        <td>If true, the html is echoed to the browser by createTable(), and nothing is returned. If false, the html is returned as a string and nothing is echoed to the browser.</td>
        <td>true</td>
        <td>Echo, EchoIt</td>
    </tr>
    <tr>
        <td>TableAttr</td>
        <td>string</td>
        <td>Attribute for the HTML table. You might want to set this to something like <tt>id="mytable"</tt> 
        or <tt>class="mytable"</tt> to assist with CSS styling.</td>
        <td>border="1"</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>Caption</td>
        <td>string</td>
        <td>String to be used as a table caption (HTML &lt;caption&gt; tag). If empty, no caption tag will be used.</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>ShowFieldNames</td>
        <td>boolean</td>
        <td>If true, field names will be shown at the top of the table (if false, they won't!).</td>
        <td>true</td>
        <td>ShowHeader, header</td>
    </tr>
    <tr>
        <td>ShowFooter</td>
        <td>boolean</td>
        <td>If true, field names will be shown at the bottom of the table.</td>
        <td>false</td>
        <td>Footer</td>
    </tr>
    <tr>
        <td>HideFields</td>
        <td>string</td>
        <td>Normally every field in the query result is shown in the table.
        HideFields can be a comma-separated list of fields to hide; you might use this if one of the fields is only
        to be used as part of a LinkField or tooltip.</td>
        <td>(empty)</td>
        <td>Hide</td>
    </tr>
    <tr>
        <td>NumberFormat</td>
        <td>string</td>
        <td>This sets the default format for fields listed in the FormatAsNumber option.
        The value should be a 4-digit string, with each digit having a special meaning:
        <ol>
            <li>currency symbol at start (1=yes, 0=no)</li>
            <li>use thousands separator (1=yes, 0=no)</li>
            <li>decimal places (0 to 9)</li>
            <li>currency symbol at end (1=yes, 0=no)</li>
        </ol>
        EXAMPLES:<br />
        If this option is <tt>0020</tt> it means no currency symbol at the start, 
        no thousands separator, 2 decimal places and no currency symbol at the end, ie like: <tt>12345.67</tt><br />
        Likewise, <tt>1100</tt> means put currency symbol at the start, 
        include thousands separator, no decimal places and no currency symbol at the end, ie like: <tt>$12,345</tt>
        <br /><br />
        For historical reasons, the NumberFormat can also be a single-digit shortcut
            <p class="indent"><tt>1</tt> means two decimal places, no comma for thousands eg 12345.67 (same as <tt>0020</tt>)<br />
            <tt>2</tt> means dollar sign, two decimal places, with comma for thousands eg $12,345.67 (same as <tt>1120</tt>)</p>
            <br />
        The characters used for the currency symbol, thousands separator and decimal can be changed with the
        Currency, ThousandsSep and DecimalChar options respectively. For example you can format a number as
        <tt>152,50 &euro;</tt> by setting NumberFormat to <tt>0121</tt> with Currency=<tt> &amp;euro;</tt>
        ThousandsSep=<tt>.</tt> and DecimalChar=<tt>,</tt>.
        </td>
        <td>1</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>DateFormat</td>
        <td>string</td>
        <td>This sets the default format for fields listed in the FormatAsDate option.
        It must be a format string as used by the PHP date() function.
        Example format strings:
            <p class="indent mono">j M Y ........ 5 Dec 2010<br />
            j M Y H:i .... 5 Dec 2010 15:30<br />
            j M Y H:i:s .. 5 Dec 2010 15:30:53<br />
            j M Y g:ia ... 5 Dec 2010 3:30pm<br />
            j/n/Y ........ 5/12/2010<br />
            Y-m-d ........ 2010-12-05<br />
            Y-m-d H:i:s .. 2010-12-05 15:30:53<br />
            D j F Y ...... Sat 5 December 2010</p></td>
        <td>j M Y</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>Currency</td>
        <td>string</td>
        <td>Currency symbol used by the NumberFormat and FormatAsNumber options.</td>
        <td>$</td>
        <td>currencysymbol, money</td>
    </tr>
    <tr>
        <td>ThousandsSep</td>
        <td>string</td>
        <td>Thousands separator character used by the NumberFormat and FormatAsNumber options.</td>
        <td>, (comma)</td>
        <td>thousandsep, thou</td>
    </tr>
    <tr>
        <td>DecimalChar</td>
        <td>string</td>
        <td>Decimal character used by the NumberFormat and FormatAsNumber options.</td>
        <td>. (dot)</td>
        <td>decimal</td>
    </tr>
    <tr>
        <td>GroupFirst</td>
        <td>boolean</td>
        <td>If the first column of your resultset is a repeating value, set this option to true 
            so the value is only shown when it changes.</td>
        <td>false</td>
        <td>group, grp</td>
    </tr>
    <tr>
        <td>GroupHeadCount</td>
        <td>integer</td>
        <td>If GroupFirst is true, this option can specify how many fields should 
            have field names shown (counting from the right). By default, this will be one less 
            than the number of columns, so all field names except the first (repeating) one are shown.
            This applies to fieldnames both at the top (ShowFieldNames) and bottom (ShowFooter).</td>
        <td>false</td>
        <td>&nbsp;</td>
    </tr>
    <tr class="section"><td><a name="row"></a>&nbsp;</td><td colspan="4">Row options</td></tr>
    <tr>
        <th>Option name</th>
        <th>Type</th>
        <th>Description</th>
        <th>Default</th>
        <th>Alias</th>
    </tr>
    <tr>
        <td>TrAttr</td>
        <td>string</td>
        <td>Attribute for each table row (&lt;tr&gt; tag).</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>OddRowClass</td>
        <td>string</td>
        <td>CSS class name for &lt;tr&gt; tags in odd rows (use this for alternating colours)</td>
        <td>(empty)</td>
        <td>oddrow, oddclass</td>
    </tr>
    <tr>
        <td>EvenRowClass</td>
        <td>string</td>
        <td>CSS class name for &lt;tr&gt; tags in even rows (use this for alternating colours)</td>
        <td>(empty)</td>
        <td>evenrow, evenclass</td>
    </tr>
    <tr>
        <td>TrEvery</td>
        <td>string</td>
        <td>CSS class name to apply to the &lt;tr&gt; tag every N rows. For example <tt>hide@address.com</tt> will add class="brdr" to every 5th &lt;tr&gt; tag.</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>RowNumber</td>
        <td>boolean | string</td>
        <td>If this is true or a non-empty string, an extra column will be added as the first
            column of the table, for numbering each row. Numbering always start at 1.
            The value of this option is used as the field name; you can use '&amp;nbsp;' 
            or boolean true
            if you don't want a field name shown. The cells in this new column
            will be assigned CSS class "rn", which you can use to style them if required.</td>
        <td>false</td>
        <td>rn</td>
    </tr>
    <tr class="section"><td><a name="cel"></a>&nbsp;</td><td colspan="4">Cell options</td></tr>
    <tr>
        <th>Option name</th>
        <th>Type</th>
        <th>Description</th>
        <th>Default</th>
        <th>Alias</th>
    </tr>
    <tr>
        <td>TdAttr</td>
        <td>string</td>
        <td>Attribute for each table cell (&lt;td&gt; tag).</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>EmptyValue</td>
        <td>string</td>
        <td>The value to show for an empty field, eg <tt>'(empty)'</tt></td>
        <td>&amp;nbsp;</td>
        <td>Empty</td>
    </tr>
    <tr>
        <td>NullValue</td>
        <td>string</td>
        <td>The value to show for a null field, eg <tt>'-null-'</tt>. You can also include HTML markup in this string, so for example you could set this to <tt>'&lt;span style="color:#888;"&gt;-null-&lt;/span&gt;'</tt></td>
        <td>&amp;nbsp;</td>
        <td>Null</td>
    </tr>
    <tr>
        <td>AlignLeft</td>
        <td>string</td>
        <td>This is a comma-separated list of fields to left-align. It affects the values and 
        fieldname headings for the specified fields. As with the other Align options, it uses the 
        align attribute of the &lt;td&gt; tag, and will be overruled by any alignment specified in CSS.</td>
        <td>(empty)</td>
        <td>left</td>
    </tr>
    <tr>
        <td>AlignRight</td>
        <td>string</td>
        <td>This is a comma-separated list of fields to right-align. It affects the values and 
        fieldname headings for the specified fields. As with the other Align options, it uses the 
        align attribute of the &lt;td&gt; tag, and will be overruled by any alignment specified in CSS.</td>
        <td>(empty)</td>
        <td>right</td>
    </tr>
    <tr>
        <td>AlignCentre</td>
        <td>string</td>
        <td>This is a comma-separated list of fields to centre-align. It affects the values and 
        fieldname headings for the specified fields. As with the other Align options, it  uses the 
        align attribute of the &lt;td&gt; tag, and will be overruled by any alignment specified in CSS.</td>
        <td>(empty)</td>
        <td>aligncenter, center, centre</td>
    </tr>
    <tr>
        <td>Tooltips?</td>
        <td>&nbsp;</td>
        <td>see the LinkFields option</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>FormatAsNumber</td>
        <td>string</td>
        <td>A comma-separated list of fields to format as a number. By default, these fields 
        will be formatted according to the NumberFormat option, but you can optionally add a 
        specific format for each field, eg <tt>'BestGuess,Amount=2'</tt> (for details, see NumberFormat)</td>
        <td>(empty)</td>
        <td>AsNum</td>
    </tr>
    <tr>
        <td>FormatAsDate</td>
        <td>string</td>
        <td>A comma-separated list of fields to format as a date. By default, these fields 
        will be formatted according to the DateFormat option, but you can optionally add a 
        specific format for each field, eg <tt>'DatePaid,TransDate=j M Y H:i:s'</tt>
        (for details, see DateFormat)<br />
        NOTE: format strings specified here CANNOT CONTAIN A COMMA - you would 
        need to encode it so browser shows a comma</td>
        <td>(empty)</td>
        <td>AsDate</td>
    </tr>
    <tr>
        <td>CellClass</td>
        <td>string</td>
        <td>A comma-separated list of fields with classname/s, eg <tt>'Tmst=myclass,UserId=class2'</tt>.
        In addition to the simple <tt>field=class</tt> format, you can also apply classes 
        conditionally with the format <tt>field=ClassCondValue</tt> 
        (or multiple conditions with <tt>field=ClassCondValue|ClassCondValue</tt>), where
        <pre class="clean">
<b>Class</b> is a classname (or space separated class names)
<b>Cond</b>  is a comparison operator. Equals sign is not allowed; use @ instead.
          equal to .......... @
          not equal to ...... ! or !@ or &lt;&gt;
          greater than ...... &gt;
          greater or equal .. &gt;@
          less than ......... &lt;
          less or equal ..... &lt;@
<b>Value</b> is compared with the current value of the field. It can be a static string, or
      a fieldname in square brackets (the value of that field in the current row will be
      substituted). Value can NOT be an expression; if you need something more complex, 
      you could add a calculated field to the SQL as a hidden field and use a comparison 
      with that field.</pre>
        EXAMPLES:
        To assign classname "neg" to the Amount field if it's less than zero:
            <pre class="clean">       Amount=neg&lt;0</pre>
        For same as above, but also assign classname "big" if Amount is more than the Average field:
            <pre class="clean">       Amount=neg&lt;0|big&gt;[Average]</pre>
        To assign classname "grey" to Tmst if it's empty, and classname "me" to an Id of 6:
            <pre class="clean">       Tmst=grey@,Id=hide@address.com</pre></td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>HeadClass</td>
        <td>string</td>
        <td>A comma-separated list of fields with classname/s, eg <tt>'Tmst=myclass,UserId=class2'</tt>. 
        This applies a CSS class name to the fieldname headings.</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>ShowTotal</td>
        <td>string</td>
        <td>A comma-separated list of fields to be totalled. The totals will appear in a row at 
        the end of the table, with &lt;tr&gt; attribute <tt>class="total"</tt>.</td>
        <td>(empty)</td>
        <td>Total</td>
    </tr>
    <tr class="section"><td><a name="lnk"></a>&nbsp;</td><td colspan="4">Link options</td></tr>
    <tr>
        <th>Option name</th>
        <th>Type</th>
        <th>Description</th>
        <th>Default</th>
        <th>Alias</th>
    </tr>
    <tr>
        <td>AutoEmailLink</td>
        <td>boolean</td>
        <td>If true, any field value that looks like an email address will be rendered as a mailto: link.</td>
        <td>false</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>AutoEmailFields</td>
        <td>string</td>
        <td>A comma-separated list of fields that should appear as clickable mailto: links. This is an alternative to AutoEmailLink, 
        which links ALL email addresses it finds.</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>AutoEmailSubject</td>
        <td>string</td>
        <td>If not empty, this string will be used as the email subject for mailto: links created for AutoEmailLink or AutoEmailFields options.</td>
        <td>(empty)</td>
        <td>&nbsp;</td>
    </tr>
    <tr>
        <td>LinkFields</td>
        <td>array</td>
        <td>Allows you to create clickable links and/or tooltips on specific fields.
        This option, if specified, is an array indexed by FieldName, with each value in the format
        <pre class="clean">    Href~||~Title~||~LinkAttrib</pre>
        where
<pre class="clean">
    Href ........ location href for the link destination
    Title ....... text to be used as a tooltip (on mouseover)
    LinkAttrib .. any other a attributes, eg 'onclick="doThing();"'
</pre><br />
        Any of the 3 parts can include the current value of another field in the row by using [FieldName]. For example,
        <pre class="clean">    array('AuthorRef' =&gt; 'edit.php?id=[AuthorId]~||~Update this author'</pre>
        This will create a link on every AuthorRef value, with an 
        href that changes depending on the value of AuthorId in each row.
        The tooltip for every link will be "Update this author".<br /><br />

        If Href is not present, a &lt;span&gt; tag will be created instead of a link (&lt;a&gt; tag). 
        The &lt;span&gt; tag is normally used if you just want a tooltip - any fieldname specified in 
        the <tt>Title</tt> will usually be included in the HideFields option.<br /><br />
        
        You may need to apply rawurlencode() or htmlentities() to these strings if they 
        include quotes or other special characters.<br /><br />
        EXAMPLE: Advanced email link
<pre class="clean">
    $Url = 'mailto:[email]?subject=Subs%20renewal&amp;body=';
    $Url .= rawurlencode("[firstname] [lastname]\r\n\r\nRenew at http://domain.com/renew?qr=[Hash]\r\n");
    // but don't urlencode square brackets!..
    $Url=str_replace(array('%5B', '%5D'), array('[', ']'), $Url);
    $HtmlTable->setOption('LinkFields', array('Hash'=&gt;$Url));</pre>
    The above example will create a mailto: link on the <tt>Hash</tt> field, where the email To address will be the
    value from the <tt>email</tt> field, and the email body will begin with values from the <tt>firstname</tt> 
    and <tt>lastname</tt> fields.</td>
        <td>(empty)</td>
        <td>Link</td>
    </tr>
</table>
<br />
<p>
    <a href="http://tweezy.net.au/doco/dbHtmlTable/">More usage examples</a> |
    <a href="http://tweezy.net.au/dbHtmlTable.html">dbHtmlTable download page</a>
</p>
<br /><br />
</body></html>
Return current item: dbHtmlTable