Formatting and Manipulating Data

This chapter describes how to poplulate an HTML table with query results and how to use ColdFusion functions to format and manipulate data. During chapter practices, you will create an HTML table and apply date and currency functions for the data that is retrieved from the Emplist query - the query that you built in the last chapter.

Contents

• Controlling Data 62

• Formatting Data 62

• Using Tables with CFML 62

• Table Syntax Usage Example 62

• Table Notes and Considerations 64

• Understanding ColdFusion Functions 64

• Using Display and Formatting Functions 66

• Using the DollarFormat Function 67

• Using the DateFormat Function 68

• Function Notes and Considerations 69

• Development Considerations 69

Controlling Data

Whether data is retrieved from databases, the system itself, LDAP servers or mail servers, it possesses no inherent structure to help you control how it displays on a page. Additionally, data types such as date and currency return raw values that are not easy to work with or read.

By applying HTML tables and ColdFusion functions, you can format and manipulate the data that’s returned to a page.

Formatting Data

One of the great things about building a Web application is that you can pull data for your page at run-time. But what this means is that, though you might know the type of data you want to return to a page, you will not always know the amount of data that’s returned or its given length. To format data for display, you will incorporate HTML tables and ColdFusion display functions within your application pages.

Using Tables with CFML

As you may remember from the previous chapter, the QUERY attribute tells ColdFusion to process the contents of the CFOUTPUT block once for each row of query data. By combining the CFOUTPUT tag with standard HTML table syntax, you can create one table that displays one row of data for each record returned from a query.

Table Syntax Usage Example

The code below formats Emplist query in a table. The query was defined previously on that same page.

...

<TABLE >

<TR>

<TH>First Name</TH>

<TH>Last Name</TH>

<TH>Start Date</TH>

<TH>Salary</TH>

<TH>Contract?</TH>

</TR>

<CFOUTPUT QUERY="EmpList">

<TR>

<TD>#FirstName#</TD>

<TD>#LastName#</TD>

<TD>#StartDate#</TD>

<TD>#Salary#</TD>

<TD>#Contract#</TD>

</TR>

</CFOUTPUT>

</TABLE>

...

• Enclose a CFOUTPUT block in a table using the TABLE begin and end

  tags. One table will be generated for the page display.

• To include table headers, add one table row for the table header

  tags within the TABLE but before the CFOUTPUT block.

One row of table headers will be generated for the page display.

• Reference the name of the query that you want to display using the

  CFOUTPUT tag’s QUERY attribute.

• Add one table row for the database column references within the

  CFOUTPUT block.

Surround each column reference with a table data tag.

One table row will be generated for each row returned for the query.

To generate a table with query results:

1. Open EmpList.cfm in HomeSite.

2. Add a begin TABLE tag after the H1 block and before the CFOUTPUT

   block.

3. Edit the tag to assign a table width of 95%.

4. Add a TR tag after the begin TABLE tag and before the CFOUTPUT

   block.

5. Add five TH ALIGN = "LEFT" blocks to that TR tag to create one table

   header for each column of data that you’ll output to the page:

<TH ALIGN ="Left">First Name</TH>

<TH ALIGN ="Left">Last Name</TH>

<TH ALIGN ="Left">Start Date</TH>

<TH ALIGN ="Left">Salary</TH>

<TH ALIGN ="Left">Contract?</TH>

1. Close the table row with the closing TR tag.

2. Delete the BR tag within the CFOUTPUT block.

3. Create a new table row by adding a TR tag after the begin CFOUTPUT

   tag.

4. Add table data (TD) blocks to surround the five table column names

   referenced for output:

<TD>#FirstName#</TD>

<TD>#LastName#</TD>

<TD> #StartDate#</TD>

<TD>#Salary#</TD>

<TD>#Contract#</TD>

1. Add an ending TR tag after the last closing TD tag and before the

   closing CFOUTPUT tag to end the table row.

2. Add the closing TABLE tag after the closing CFOUTPUT tag to end the

   table. Your table code should look like this:

<TABLE>

<TR>

<TH ALIGN ="Left">First Name</TH>

<TH ALIGN ="Left">Last Name</TH>

<TH ALIGN ="Left">Start Date</TH>

<TH ALIGN ="Left">Salary</TH>

<TH ALIGN ="Left">Contract?</TH>

</TR>

<CFOUTPUT QUERY="EmpList">

<TR>

<TD>#FirstName#</TD>

<TD>#LastName#</TD>

<TD>#StartDate#</TD>

<TD>#Salary#</TD>

<TD>#Contract#</TD>

</TR>

</CFOUTPUT>

</TABLE>

1. Save the file.

2. View the page in a browser.

The employee data appears in a table.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListTable.cfmClick here/a to see how the page should look at this time.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListTable.txtClick here/a to see the code behind the scenes.

Note The navigation links will not work at this time.

Table Notes and Considerations

• The only table syntax inside the CFOUTPUT block should be the single

  table row containing the query column references.

• If you wrap the entire HTML table syntax within the CFOUTPUT block,

  multiple tables display each row of query data.

• You can mix other HTML and CFML inside or outside the CFOUTPUT

  block.

Understanding ColdFusion Functions

All programming languages possess their own set of functions that you use to manipulate and format data.

For example, you use functions to:

• Display system data, such as the current date and time.

• Perform mathematical calculations, like rounding to the nearest

  whole number.

• Find or parse characters in a string.

Function types

ColdFusion contains over 150 different functions. Because Allaire provides solid reference guides, it’s not important to memorize these functions; you only need to know what type of operation you want to perform on data.

The table below describes ColdFusion functions by the type of operation that you can apply to data.

During this chapter, you will practice applying display and formatting functions so that you can gain familiarity with general syntax. Refer to the CFML Language Reference for ColdFusion Express for a list of functions and their syntax.

Using Display and Formatting Functions

During the last several procedures, you have retrieved and output data from a database. The FirstName, LastName, and Contract fields look fine, but the Salary contains no currency information and the StartDate column data is difficult to read.

ColdFusion provides a complete set of functions that you can use to control the display and formatting of:

• Dates

• Times

• Currency

• Numbers

• HTML

• Paragraphs

During this chapter, you will learn how to use the:

• DollarFormat( ) function to format the data in the salary column

• DateFormat( ) function to format the data in the StartDate column

Refer to the CFML Language Reference for ColdFusion Express for a list of functions and their syntax.

Using the DollarFormat Function

Use the DollarFormat( ) function to format values with a dollar sign, a thousand separator, and 2 decimal places. Its syntax is:

DollarFormat(NumericValue)

• Place a NumericValue to format within the function’s parenthesis (

  ).

• NumericValue may be a fixed or variable value.

• Reference the function anywhere on a ColdFusion page.

• When referencing the function within a CFOUTPUT block, surround the

  entire function with pound signs (#).

This tells ColdFusion to return function results rather than the function name to the browser.

DollarFormat function usage examples

The two code sets below display $23,333.44 in a browser:

<CFOUTPUT>

#DollarFormat(2333344)#

</CFOUTPUT>

• DollarFormat( ) formats a fixed value for display.

• Surround the entire function with pound signs (#) for immediate

  output.

<CFSET Salary=DollarFormat(2333344)>

<CFOUTPUT>

#Salary#

</CFOUTPUT>

• DollarFormat( ) formats a fixed value for the Salary variable.

• Surround the Salary variable with pound signs (#) for display.

The code below formats the Salary column data that was returned from a query performed earlier on the same page:

<CFOUTPUT QUERY="EmpList">

#DollarFormat(Salary)#<BR>

</CFOUTPUT>

• DollarFormat( ) formats a variable for display.

• Surround the entire function with pound signs (#) for immediate

  output.

To format currency for display:

1. Return to the page that you’re building.

2. Format the Salary query data using the DollarFormat() function

   within the existing TD block:

#DollarFormat(Salary)#

1. Save the file.

2. View the page in a browser.

The Salary is formatted for page display.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListFunctions.cfmClick here/a to see how the page should look at this time.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListFunctions.txtClick here/a to see the code behind the scenes.

Note The navigation links will not work at this time.

Using the DateFormat Function

Use the DateFormat( ) function to display dates in a variety of ways. Its syntax is:

DateFormat(DateValue [, mask ])

• Place a DateValue to format within the function’s parenthesis ( ).

• DateValue may be a fixed or variable value.

When the DateValue is a fixed value, surround it with double quotes (" ) so that ColdFusion does not interpret it as a numeric value.

• Define an optional mask to describe the date display order and

  filter. For example, a mask may describe dd/mm/yy or mm/dd/yy.

Surround the mask value with double quotes (" ) so that ColdFusion interprets it as a character string.

• Use a comma (,) to Separate the DateValue from the mask.

DateFormat function usage examples

The code below formats the current system date on a North American site:

<CFOUTPUT>

#DateFormat(Now(),"DD/MM/YY")#

</CFOUTPUT>

• The Now( ) function is the DateValue.

• The comma (,) separates the DateValue from the mask.

• The mask tells ColdFusion to format the current value of Now as

  day/month/ year.

• The double quotes (" ) tell ColdFusion that the mask value is not

  numeric.

The code below formats the StartDate column data that was returned from a query performed earlier on the same page:

<CFOUTPUT QUERY=EmpList>

#DateFormat(StartDate)#<BR>

</CFOUTPUT>

• DateFormat( ) formats the query variable for display.

• Surround the entire function with pound signs (#) for immediate

  output.

To format dates for display:

1. Return to the page that you’re building.

2. Format the StartDate query data using the DateFormat() function

   within the existing TD block:

#DateFormat(StartDate)#

1. Save the file.

2. View the page in a browser.

The StartDate is formatted for page display.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListFunctions.cfmClick here/a to see how the page should look at this time.

http://127.0.0.1/CFDOCS/exampleapp/TutorialSolutions/Chapter6/ EmpListFunctions.txtClick here/a to see the code behind the scenes.

Note The navigation links will not work at this time.

Function Notes and Considerations

When using functions, keep these guidelines in mind:

• Function names are not case-sensitive but it’s good practice, to mix

  the case of function names for readability.

• When using functions to format data for display, surround the entire

  function with pound signs and include them within a CFOUTPUT block.

• Functions can be nested.

For example, you may display the system date value using the Now() function and format the displayed system date using the DateFormat function.

Development Considerations

During this chapter, you learned how to control and manipulate data for a page using HTML tables and ColdFusion functions. You will learn more about when and how to apply ColdFusion functions as needed throughout the remainder of this guide.

Where to go from here

• Refer to the CFML Language Reference for ColdFusion Express for a

  list of functions and their syntax.

• Move on to the next chapter to learn about how you can use forms to

  collect data from end users.

C HA PT ER 7