FORMfields, the premiere web framework
Login
Support Home | Knowledge Base | Documentation | Forums | Contact Us
FORMfields v3 Tutorials
FORMfields Tutorials
FORMfields Lib Installation
FORMgen Installation
Manually Creating a Form
Load/Unload From a DB
Emailing a Form
Forms on Your Webpage
Creating Tables
Pagifying Data
Using CSS to Format Forms 1
Using CSS to Format Forms 2
Other Tutorials
Creating Tables
Last Updated: September 20th, 2007
Overview
Whether you create tables from database queries or other sources of data, you can use FORMfields to display professional looking tabulated data with just a few function calls. Furthermore, FORMfields allows you to easily apply CSS formatting to specific rows, columns, and table cells.
Example 1 - Generating a Table From Arrays
In this example we create a TableSet and manually insert the headings and rows. Then we display the table, by echoing the getTableTag() function.

<?
    $table1 
= new TableSet();
    
$table1->headings = array("First""Last""Age");
    
$table1->rows = array(
        array(
"Jerome""Parker""53"),
        array(
"Joe""Smith""27"), 
        array(
"Suzanne""Jones""33")
    );
    echo 
$table1->getTableTag();
?>

Generates:
First Last Age
Jerome
Parker
53
Joe
Smith
27
Suzanne
Jones
33
Download the source code for the Example 1.

Next we will demonstrate how you can easily populate a TableSet with data from a database.
Creating the Database Needed for Examples 2-5
Use phpMyAdmin or the MySQL client, to execute the following SQL code:

    #
    # Table structure for table `ffce_traffic`
    #

    CREATE TABLE `ffce_traffic` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `advertiser` varchar(80) NOT NULL default '',
      `clicks` int(10) unsigned NOT NULL default '0',
      `impressions` int(11) NOT NULL default '0',
      `date` date NOT NULL default '0000-00-00',
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=6 ;

    #
    # Inserting data for table `ffce_traffic`
    #

    INSERT INTO `ffce_traffic` VALUES (1, 'www.urshelf.com', 50, 300, '2005-03-07');
    INSERT INTO `ffce_traffic` VALUES (2, 'www.redgeoff.com', 10, 100, '2005-03-07');
    INSERT INTO `ffce_traffic` VALUES (3, 'www.mymusicband.com', 150, 510, '2005-03-07');
    INSERT INTO `ffce_traffic` VALUES (4, 'www.urshelf.com', 40, 234, '2005-03-08');
    INSERT INTO `ffce_traffic` VALUES (5, 'www.redgeoff.com', 12, 78, '2005-03-08');

Example 2 - Generating a Table From a SQL Query
In this example, we will execute a simple query and display the results in a nifty looking table with only 3 lines of code:

<?
    $table2 
= new TableSet();
    
$table2->loadQuery("SELECT * FROM ffce_traffic");
    echo 
$table2->getTableTag();
?>

Generates:
id advertiser clicks impressions date
1
www.urshelf.com
50
300
2005-03-07
2
www.redgeoff.com
10
100
2005-03-07
3
www.mymusicband.com
150
510
2005-03-07
4
www.urshelf.com
40
234
2005-03-08
5
www.redgeoff.com
12
78
2005-03-08
Download the source code for the Example 2.
Example 3 - Refining a Table From a SQL Query
In this example we execute a more specific query which yields a table with more readable column names and data.

<?
    $table3 
= new TableSet();
    
$table3->loadQuery("
        SELECT advertiser AS 'Advertiser',
            clicks AS 'Clicks',
            impressions AS 'Impressions',
            date AS 'Day'
        FROM ffce_traffic
    "
);
    echo 
$table3->getTableTag();
?>

Generates:
Advertiser Clicks Impressions Day
www.urshelf.com
50
300
2005-03-07
www.redgeoff.com
10
100
2005-03-07
www.mymusicband.com
150
510
2005-03-07
www.urshelf.com
40
234
2005-03-08
www.redgeoff.com
12
78
2005-03-08
Example 4 - Applying CSS Formatting
Now we take it up another notch and apply CSS formatting to our table.

Here is the CSS formatting that we will apply to our table:

    div.totals {
        font-weight: bold;
    }

    div.impressions {
        text-align: center;
        color: blue;
        width: 100%;
    }

    div.date {
        color: red;
    }

    div.highlight {
        background-color: yellow;
        display: block;
        margin: 0px;
    }
    
Note: If you are unfamiliar with CSS you should visit http://www.w3schools.com/css/.

First, we execute a query to retrieve the daily website stats and append the impression and click totals. Next, we apply the div.totals CSS formatting to the bottom row (or totals) of our query data. Then we apply div.impressions CSS formatting to the impressions column. Lastly, we set a translation rule which will apply div.highlight CSS formatting to all rows that have an Advertiser equal to "www.urshelf.com".

<?
    $table4 
= new TableSet();
    
$table4->loadQuery("
        (SELECT advertiser AS 'Advertiser',
            clicks AS 'Clicks',
            impressions AS 'Impressions',
            DATE_FORMAT(date, '<div class=\"date\">%b %D</div>') AS 'Day'
        FROM ffce_traffic)
        UNION
        (SELECT 'Totals',
            sum(clicks),
            sum(impressions),
            ' '
        FROM ffce_traffic)
    "
);
    
$table4->rowClasses[$table4->getNumRows() - 1] = "totals";
    
$table4->columnClasses["Impressions"] = "impressions";
    
$table4->translateRows("Advertiser""www.urshelf.com""highlight");
    echo 
$table4->getTableTag();
?>

Generates:
Advertiser Clicks Impressions Day
www.urshelf.com
50
300
Mar 7th
www.redgeoff.com
10
100
Mar 7th
www.mymusicband.com
150
510
Mar 7th
www.urshelf.com
40
234
Mar 8th
www.redgeoff.com
12
78
Mar 8th
Totals
262
1222
Note: You may have noticed the special date formatting '<div class=\"date\">%b %D</div>'. Essentially, this type of formatting is equivalent to applying a column class, like we did for the Impressions column. Also, indices such as "Impressions" in the columnClass array are case sensitive.
Example 5 - Hiding Columns
In this example we demonstrate how and why you may want to hide columns. Let's say you want to display our ffce_traffic data, but you only want to highlight the row with id equal to "2", however, you don't want to show this id to the user. To accomplish this, we simply set a row translation which will apply div.highlight to all rows with id equal to "2". Then we hide the "id" column.

<?
    $table4 
= new TableSet();
    
$table4->loadResultSet(gmysql_query("SELECT * FROM ffce_traffic"));
    
$table4->translateRows("id""2""highlight");
    
$table4->hideColumns["id"] = true;
    echo 
$table4->getTableTag();
?>

Generates:
advertiser clicks impressions date
www.urshelf.com
50
300
2005-03-07
www.redgeoff.com
10
100
2005-03-07
www.mymusicband.com
150
510
2005-03-07
www.urshelf.com
40
234
2005-03-08
www.redgeoff.com
12
78
2005-03-08
Example 6 - Exporting Table Data
In this example we demonstrate how to export data from a TableSet to a CSV (Comma Separated Values) file that can easily be imported into Microsoft Excel or a similar such program.
export.php:
<?
    
require($_SERVER["DOCUMENT_ROOT"] . "/FORMfields/FORMfields.php");

    
// Define the headers for the outputted text file
    
header('Content-Type: text/plain');
    
header('Pragma: '); // Needed for SSL in IE
    
header('Cache-Control: '); // Needed for SSL in IE
    
header('Content-Disposition: attachment; filename=export.csv');
    
    
// Create and populate a TableSet
    
$tableSet = new TableSet();
    
$tableSet->loadQuery("SELECT * FROM ffce_traffic"); 
    
$tableSet->hideColumns["id"] = true

    
// Print the TableSet's data
    
echo $tableSet->getCommaDelimitedData(false);
?>
Then we just add a link to export.php: Download the tab deliminated data. You can import this CSV file by dragging and dropping the file into Excel. Or, you can open Excel and use select File->Open CSV to load the CSV file.
Example 7 - Enabling Column Sorting
You can easily enable click-sorting by setting the enableSort property and using loadQuery like:

<?
    $table1 
= new TableSet();
    
$table1->enableSort true;
    
$table1->loadQuery("
        SELECT advertiser AS 'The Advertiser',
            clicks AS 'Clicks',
            impressions AS 'Impressions',
            DATE_FORMAT(date, '%b %D') AS 'Day'
        FROM ffce_traffic
    "
);
    echo 
$table1->getTableTag();
?>

Generates:
The Advertiser Clicks Impressions Day
www.urshelf.com
50
300
Mar 7th
www.redgeoff.com
10
100
Mar 7th
www.mymusicband.com
150
510
Mar 7th
www.urshelf.com
40
234
Mar 8th
www.redgeoff.com
12
78
Mar 8th
Example 8 - Column Sorting Options
The following example demonstrates how to:
  • Specify which particular columns are sortable.
  • Setup a sort translation so that sorting can be performed on a value other than that displayed in the table.

<?
    $table1 
= new TableSet();
    
$table1->enableSort true;
    
    
// The following line states that only the "The Advertiser" and "Day"
    // columns should be sortable. If this line is omitted and 
    // enableSort = true then all columns are sortable.
    
$table1->sortableHeadings = array("The Advertiser""Day");
    
    
// The following line states that the "The Advertiser" column should 
    // be sorted by the value of "advertiser" in the database and not
    // "The Advertiser", which is now a URL. Without this sort translation
    // the column would be sorted by the HTML and not the value of advertiser.
    
$table1->sortTranslations["The Advertiser"] = "advertiser";
    
    
// Note: That we use CONCAT() and the "<a>" tag to turn the "The Avertiser"
    // column into a list of hyperlinks.
    
$table1->loadQuery("
        SELECT CONCAT('(',impressions,') - <a href=\"http://',
                      advertiser,'\">',advertiser,'</a>') AS 'The Advertiser',
            clicks AS 'Clicks',
            impressions AS 'Impressions',
            DATE_FORMAT(date, '%b %D') AS 'Day'
        FROM ffce_traffic
    "
);
    echo 
$table1->getTableTag();
?>

Generates:
The Advertiser Clicks Impressions Day
50
300
Mar 7th
10
100
Mar 7th
150
510
Mar 7th
40
234
Mar 8th
12
78
Mar 8th
Info
Support | Earn Money
Copyright © 2005-2018 Brain Book Software LLC.
Built with FORMfields, the premiere web framework.