• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Lesson 17. Flash, ColdFusion, and the Da... > Inserting the Data into a Database

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.

1.
Still in dante_quiz_results.cfm, switch to Code view, and scroll to the top of the page. Add several blank lines above the opening <!DOCTYPE tag.

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

2.
Begin the query by typing the following code:

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

</cfquery>

Leave a blank line in between these two lines.

The <cfquery> tag is used to query databases, whether you are 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.).

Between the opening and closing <cfquery> tags, you place the query itself. It is this query that gets sent to the database. In this case, you will 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 list 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, of course, is the part in the square brackets—you need to figure out how to get ColdFusion to supply that data for you.

3.
Between the opening and closing <cfquery> tags, insert the following code:

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

Just as it does with the <cfoutput> blocks you have 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.

4.
Save and upload dante_quiz_results.cfm. Test dante_quiz_login.cfm and take the quiz all over again. When you are finished, leave the browser open.

It works! How do you know? If you don't see an error, then 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.

5.
With any page in your site open, switch to the Databases panel. Expand, if necessary, the dante database. Expand the Tables category. Right-click (Windows) or Control-click (Macintosh) the quiz table, and choose View Data.

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


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint