Browse DevX
Sign up for e-mail newsletters from DevX


Warm Up to ColdFusion : Page 4

Macromedia's ColdFusion technology isn't new, but since the release of the MX version, there's been a new surge of interest. This is a great time to learn the basics of the CF environment. This simple guide will get you started with CFML, form-building, databases, and a sample application you can build in no time flat.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

ColdFusion and Databases
ColdFusion works wonderfully with databases, which are an essential element of dynamic, interactive web sites. In this section, you'll use the favorite color example from the last section and further streamline the "Favorite Color Recovery System."

Suppose you want to store a user's favorite color on your Web site and be able to retrieve it later, simply by having users enter their name. To do that, you'll need a database designed to store user names and favorite colors, for example, in a table called "users."

The sample code uses an Access database, but you can use any database in a similar manner. If you want to try the Access version, you can download it here.

Adding Records To a Database
Databases can have many tables. A record is a row or entry in a table. The columns specify the names and types of data the table can contain, and the rows contain the specific values. Figure 2 shows the structure of the sample "users" table:

Figure 2: This shows the structure of the sample "users" table.

To add records to a database, you need a form handler which will receive the contents of the form and enter the data into the database. Change the the form in fav_color_form.cfm so it submits to a file called db_user_add.cfm (the "db" stands for "Database") by modifying the form tag's action attribute value.

After doing that, the form will send the variables "name" and "color" to the db_user_add.cfm file. That file should retrieve and insert the values into the "users" table using CFQUERY. A query is a question or command sent to a database. Queries are often used to answer questions about data such as "Give me all the names of people who say red is their favorite color." The database would then send the names of the people whose favorite color is listed as red.

You use the CFML tag CFQUERY to both get data from (retrieve) and send data to (insert, update), the database. CFQUERY is "the tag of database conversation."

The CFQUERY tag has a name attribute, where you name the query whatever you like. In my personal naming scheme, all query names start with "Q" so I can recognize them in the code more easily.

The CFQUERY also has a datasource attribute where you specify which database to use. The sample code uses a DSN (Data Source Name) called "FC" which you set up on the server to point to the Access sample database. A DSN is basically a shortcut to the database, just as the variable "color" may represent the value "blue."

<CFQUERY name="qadd_user" datasource="FC"> INSERT INTO users (name, color) VALUES ('#name#', '#color#') </CFQUERY>

User added successfully.

Oh no! What's all that code between the CFQUERY tags??!? That is SQL, the language of databases. SQL has its own syntax that's independent from CFML. But don't worry, SQL is pretty straightforward. If you haven't seen SQL before, you can get a good introduction here. In the preceding example, the SQL statement means: "Insert the following values into the table 'users,' in the columns 'name' and 'color'." Then the values sent from the form are listed in the same order as the column names. Intense, yet straightforward.

Now what if, by some amazing coincidence, there are two people using the system who have the same name, for example "Reginald"? Before inserting a new record, you need to ask the database if any users already exist whose name is "Reginald." If no such users exist (the query doesn't return any information), you can go ahead and add Reginald.

<CFQUERY name="qget_similar_user" datasource="FC"> Select * FROM users WHERE name = '#name#' </CFQUERY> <CFIF qget_similar_user.RecordCount GT 0 > That name is taken, please go back and enter a different name. <CFELSE> <CFQUERY name="qadd_user" datasource="FC"> INSERT INTO users (name, color) VALUES ('#name#', '#color#') </CFQUERY> User added successfully. </CFIF>

The preceding code first queries the database for all the rows (the "*" means "all columns") where the value of the name column is the same as the name value sent by the form. In SQL statements, you need quotes around strings. The CFQuery returns any rows matching the query as an object, with the same name as the query—qget_similar_user in this case. That object has a property called "RecordCount" which contains the number of rows returned from the database. In CFML, you access an object's property values with "dot syntax"—using the name of the object, a dot, or period, and then the name of the property, as in qget_similar_user.RecordCount. So the code checks the "RecordCount" property to see if the query returned any rows. The GT is an operator meaning "greater-than." If any records were sent (more than 0), the code tells the user to go back and enter a different name. If the name is not taken, after the CFELSE tag, the code uses another CFQUERY to insert the name and favorite color into the users table.

Thanks for your registration, follow us on our social networks to keep up-to-date