Transcript Week5

Week 4
IBS 685
FORM Data Evaluation
Conditional Logic
Creating a search form
Advanced Search
IF Statements
• Coldfusion IF statements are created using
the <CFIF> tag.
• <CFIF> takes no attributes; instead, it takes
a condition.
• <CFIF FirstName IS Ben>
• Whether a variable named Firstname
contains the value Ben.
• Chapter 9 page 181
<CFIF>
• <cfif firstname IS “Ben”>
– Whether a variable named Firstname contains the value
of Ben
• <cfif firstname IS NOT “Ben”>
– Whether firstname is not Ben.
• NEQ or Not Equal
Other operators are supported too. Page 181.
• <cfif dayofweek(Now()) is 1>
It is the weekend, yeah
</cfif>
Saturday & Sunday
<cfif dayofweek(Now()) is 1 OR dayofweek(Now())7 >
It is the weekend, yeah
</cfif>
What if it is not the Weekend?
<cfif dayofweek(Now()) is 1 OR dayofweek(Now())7 >
It is the weekend, yeah
<CFELSE>
No it is not the weekend yet, sorry!!!
</cfif>
Different messages on Saturday and
Sunday (CFELSEIF)
<CFSET dow=DayOfWeek(Now())>
<!--- Let the user know --->
<CFIF dow IS 1>
<!--- It's Sunday --->
It is the weekend! But make the most of it, tomorrow it's back to work.
<CFELSEIF dow IS 7>
<!--- It's Saturday --->
It is the weekend! And even better, tomorrow is the weekend too!
<CFELSE>
<!--- No it is not :-( --->
No, it's not the weekend yet, sorry!
</CFIF>
• Every <CFIF> must have a matching
</CFIF>.
• Every <CFIF> can have a single
<CFELSE>
• Every <CFIF> can have an unlimited
number of <CFELSEIF> tags.
The code in this example checks the state entered by the user. If the user
Enter “MA” the state tax is 8.5%; the tax is 8.2% if he enters “VA”.
Any other state returns the value of “Unknown”.
<cfoutput>
<CFIF form.state IS “MA”
#form.state# state tax: 8.5%
<CFELSEIF form.state is “VA”>
#form.state# state tax: 8.2%
<CFELSE>
#form.state# State Tax Unknown
</CFIF>
</cfoutput>
Nesting <CFIF> Statements
•
•
•
•
First check the payment type
Credit card or Purchase Order
If credit card
Check the type of credit card
Nesting <CFIF> Statements
<CFIF (var.payment_type IS “CC”)>
<cfif (var.cc_type IS “Amex”)>
Bill to Amex
<cfelseif (var.cc_type IS “Visa”)>
Bill to Visa
<cfelseif (var.cc_type IS “Disc”)>
Bill to Discover
</cfif>
<CFELSEIF (var.payment_type IS “PO”)>
Purchase order payment
<CFELSE>
Unknown Payment Method
</CFIF>
In this example, if payment_type is “CC” and CC_Type is “MC”
What will be displayed?
Testing for Form Submission
• Isdefined() function
Isdefined function takes one argument, a variable of
any type and
Returns either “Yes” or “No”
The following code will return a “Yes” if the
variable X exists in the form scope, or “No” if it
doesn’t
<CFOUTPUT>
#IsDefined(FORM.x)#
</CFOUTPUT>
Testing for Form Submission
• THis function is useful when used inside a
<CFIF> block to determine how to handle
situations when variables don’t exist.
• In the case of an action page, you can determine if
the submit button was clicked by using the
Isdefined() function.
• Assume the form’s submit button was defined as
follows:
• <INPUT TYPE=“Submit” name=“Order” value=
“Place order”>
Testing for Form Submission
• The code in the ACTION page to test for existence would
be the following:
<CFIF NOT isdefined(“form.order”)>
Error – no data supplied! Please use the form and resubmit.
<CFABORT>
</CFIF>
OR
Use CFINCLUDE tag to direct the user to the form.
Form Variable Evaluation
• Certain types of form controls will not exist on the
action page if they are not given a value on the
form page.
• The form controls that do not exist on the action
page without a value are:
–
–
–
–
Radio button without an assigned default
Unchecked checkboxes
Submit buttons that are not clicked
Select controls with multiple selections allowed and
none selected.
Setting Default Values
• What if you wanted to create a variable with
a default value if it did not exist?
• You could do something similar to this:
<CFIF NOT isdefined(“firstname”)>
<CFSET firstname=“Ben”>
</CFIF>
Why Not?
• As a rule you should not include data validation
code in the middle of your core code.
– It helps create unreliable code
– Makes debugging difficult
– Makes code reuse very difficult
• So all variable validation occur before your core
code.
• If required variables are missing, throw an error,
redirect the user to another page, or do something
else.
<CFPARAM name=“FirstName”
Default=“Ben”>
• When CF processes this line, it checks to
see whether a variable named FirstName
exists. If it does then the tag is ignored and
the processing continues.
• If however a variable does not exist, it will
be created right then and there and assigned
the value specified in DEFAULT.
<CFPARAM>
<CFSET firstname = “Jeanette”>
<CFPARAM name=“FirstName” Default=“Ben”>
<CFPARAM name=“LastName” Default=“Frueh”>
<CFOUTPUT>
<#variables.firstname# #variables.lastname#>
</CFOUTPUT>
<CFABORT>
• The CFABORT tag stops processing of a
page at the tag location. ColdFusion simply
returns everything that was processed
before the CFABORT tag. CFABORT is
often used with conditional logic to stop
processing a page because of a particular
condition.
Let s search for movies
that has the word “Her”
In it is name
<TABLE ALIGN="center" BORDER="1">
<TR>
<TD>
Movie:
</TD>
<TD>
<INPUT TYPE="text" NAME="MovieTitle">
</TD>
</TR>
<TR>
<TD COLSPAN="2" ALIGN="center">
<INPUT TYPE="submit" VALUE="Search">
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
Creating Dynamic SQL Statements
Page 266
<CFQUERY NAME="movies"
DATASOURCE="ows">
SELECT MovieTitle, PitchText,
Summary, DateInTheaters
FROM Films
WHERE MovieTitle LIKE
'%#FORM.MovieTitle#%'
ORDER BY MovieTitle
</CFQUERY>
<!--- Create HTML page --->
<HTML>
<HEAD>
<TITLE>Orange Whip Studios - Movies</TITLE>
</HEAD>
<BODY>
<!--- Page header --->
<CFINCLUDE TEMPLATE="header.cfm">
<!--- Display movie list --->
<TABLE>
<TR>
<CFOUTPUT>
<TH COLSPAN="2">
<FONT SIZE="+3">Movie List (#Movies.RecordCount# movies)</FONT>
</TH>
</CFOUTPUT>
</TR>
<CFOUTPUT QUERY="movies">
<TR>
<TD>
<FONT SIZE="+2"><B>#CurrentRow#: #MovieTitle#</B></FONT><BR>
<FONT SIZE="+1"><I>#PitchText#</I></FONT>
</TD>
<TD>Released: #DateFormat(DateInTheaters)#</TD>
</TR>
<TR>
<TD COLSPAN="2">#Summary#</TD>
</TR>
Using Multiple Search Criteria
• The query block contains two elements:
– Common SQL, which is relevant to every
query.
– Search statements (dynamic) using conditional
logic.
Using Multiple Search Criteria
• The user can input four different
combinations of data
–
–
–
–
Name only
City only
Name and city
Neither
Using Multiple Search Criteria
• Your application must account for all of
these cases. Using conditional logic you
could wrap each clause in a <CFIF> block
with a test to see if the user filled in a value.
Using Multiple Search Criteria
<CFQUERY NAME="q_GetDist" DATASOURCE="FastTrack_Solution">
SELECT Distributor_ID, Distributor_Name,
FROM
Distributor
Test if they put in a Distributor Name
<CFIF Trim(Form.Distributor_Name) IS NOT "">
WHERE (Distributor_Name = '#Form.Distributor_Name#')
</CFIF>
Test if they put in a City
<CFIF Trim(Form.city) IS NOT “”>
And
City=‘#form.city#’
</CFIF>
<CFQUERY>
Using Multiple Search Criteria
• Using the code above you will encounter an
error in the following scenario
– Name not filled in
– City filled in with a value
Using Multiple Search Criteria
SELECT
Distributor_ID,Distributor_Name
FROM
Distributor
And
City=‘#form.city#’
The where clause is omitted because the name
form field was not given a value.
Dummy Where
• To get around this issue of whether or not there is
a where clause, you can use a dummy where
clause.
• Simply a condition that always returns the value of
true.
• One such condition is comparing a value to itself.
0=0
• The following query will return all rows from the
distributor table
Dummy Where
SELECT
Distributor_ID, Distributor_Name
FROM
Distributor
Where 0=0
This statement will give us all rows from the distributor table.
<CFQUERY NAME="q_GetDist" DATASOURCE="FastTrack_Solution">
SELECT Distributor_ID, Distributor_Name,
FROM
Distributor
Where 0=0
Test if they put in a Distributor Name
<CFIF (Form.Distributor_Name) IS NOT "">
AND (Distributor_Name = '#Form.Distributor_Name#')
</CFIF>
Test if they put in a City
<CFIF (Form.city) IS NOT “”>
AND City=‘#form.city#’
</CFIF>
<CFQUERY>
• Wrap this “And Clause” with a <CFIF> tag.
If the user types in a city, append the AND
clause. If the city is blank, do not append
the AND clause.
• Where lastname IN (‘Black’, ‘Jones’,
‘Smith’)
Is the same as the following
• Where lastname=‘Black’ OR
lastname=‘Black’ OR lastname=‘Smith’
Data Drill Down
• Master Page
– A list of employees is retrieved from a database
table.
– To facilitate data drill-down, a URL is created
for each retrieved row.
– The URL’s unique employee ID is passed as a
URL parameter to the drill down application
page.
Master Page
<cfquery name=“q_getemp” datasource=“test”>
Select Emp_ID, lastname, firstname
From employee
<cfquery>
<CFOUTPUT QUERY= “q_getEmp”>
<tr>
<td> #q_getEmp.empID#</td>
<td><A href=“Emp_detail.cfm?EmpID=#q_getEmp.Emp_ID#”>
#q_getemp.lastname#,
#q_getemp.firstname# </A></td>
</tr>
</cfoutput>
Data Drill-Down Page
<cfqueryname=“q_getEmpdetail”
Datasource=“Test”>
Select * from Employee
Where employee_ID=#URL.empID#
<cfquery>
<cfoutput query=“q_getempdetail”>
Inserting New Data
• Inserting data with CF requires two
application pages.
1. An HTML form to which the user can enter or
select information
2. An action page to which form variables are
passed for processing when the user submits the
insert form. This page can display the form
variables, insert them into a database, or
perform both of these actions.
Building an Insert Form Page
• Creating an Insert form page requires that you:
– Create one form control for each column you wish to
insert into the database.
– Apply any validation rules based on your database rules
– Set any default values using attributes.
– Do not create form controls for primary key columns.
Primary key columns do not allow user input.
Building Insert Form Page
• Care should be taken to restrict input
lengths on form controls to the lengths
accepted by the database column.
Last name: <input type= “text”
name = “LastName” size = “20”
maxlength= “30”>