Macromedia Studio 8 [Electronic resources] : Training from the Source

Inserting the Data into a Database

In this task, you'll insert the username and score into the database. To do so, you'll have to hand-code a SQL query.

The query is not a part of th218 output, so it makes sense to put it outside th218 document. By putting the query above the beginning of th218 document, you ensure that if for some reason the query doesn't work, the rest of the page will not process either, but will instead display an error message.

[View full size image]

<cfquery name="insertQuizScores" datasource="dante">
</cfquery>

Leave a blank line between these two lines.

[View full size image]

The <cfquery> tag is used to query databases, whether you're merely selecting data or actually modifying it, by inserting new records or updating existing records. The name attribute is an arbitrary name you give your query. If you were returning records, you would need the value of name to output them using <cfoutput>. This query won't return any records (it only inserts them), so the value of name doesn't matter too much. The datasource attribute enables you to specify the ColdFusion DSN, which, as you'll recall, tells ColdFusion where the data is located and in what format (Microsoft Access, etc.).

You'll place the query between the opening and closing <cfquery> tags. It's this query that gets sent to the database. In this case, you'll write a query that inserts data. An insert query in SQL has the following basic syntax:

INSERT INTO someTable
(
fieldName1,
fieldName2,
fieldName3
)
VALUES
(
'Value1',
'Value2',
'Value3'
)

The first set of items lists the names of the fields in the database table into which you want to insert data. The second set of items lists the data that you want inserted. The two sets are correlated; in this example, 'Value1' will be inserted into fieldName1 of the someTable table; 'Value2' will be inserted into fieldName2, and so on.

To insert the quiz data from the Flash quiz into the database, you'll need something like this:

INSERT INTO quiz
(
username,
score
)
VALUES
(
'[the username supplied in the URL variable]',
'[the score supplied in the URL variable]'
)

The tricky part is the part in the square brackets; you'll need to figure out how to get ColdFusion to supply that data for you.

INSERT INTO quiz
(
username,
score
)
VALUES
(
'#url.username#',
'#url.score#'
)

Just as it does with the <cfoutput> blocks you've seen before, ColdFusion evaluates the variables inside the pound signs (##) before it sends the results to the database. Thus, when this command is sent to the database, the variables have already been replaced with their actual values.

[View full size image]

It works! How do you know? If you don't see an error, it must have worked. If ColdFusion had any trouble, it would have generated an error message. But an even more reliable way to verify that your data has been successfully inserted is just to go in and look at the table.

[View full size image]

In the View Data window, you'll see all the records in the quiz database. The email address and the score you just created are listed. Success!