Getting Information from a Database

I l @ ve RuBoard

To get information out of your database, you need two tools: ColdFusion and SQL. ColdFusion connects to the database and SQL manipulates the information in the database.

1. On your local hard drive, create a folder called Lesson5 within the Inetpub\ wwwroot folder. Move the file firstquery.cfm from the Lesson5\Start folder on your CD into the Lesson5 folder on your hard drive.

   You are using the firstquery.cfm file to complete this taskstarting to construct your first database query using ColdFusion.

2. Open ColdFusion Studio and choose File > Open. Navigate to the Lesson5 folder on your hard drive, select the firstquery.cfm file, and click Open .

   Comments within this file indicate where to add your query and output code.

   

3. Directly below the comment <!--- Query goes here ---> , type:

    <cfquery name="productlist" datasource="products"> 

   

   The <cfquery> tag is used whenever you need to access a database. In the query you typed, there are two attributes: name and datasource. Name gives the query a name so you can access the information that was returned by the query. That information is stored in a ColdFusion recordset of the same name. (A recordset contains all the rows of information that were returned by the query.) Datasource specifies which database ColdFusion will access.

4. Below the <cfquery> statement, type:

    SELECT *  FROM tblProducts 

   

   All of your SQL code goes inside the <cfquery> tag. The SELECT statement retrieves information from the database. The * is a wildcard which returns all records in a table. In this case, the table called tblProducts. So this statement returns every record in the tblProducts table.

   NOTE

   In SQL there are four basic commands; SELECT, INSERT, UPDATE, and DELETE. They all do exactly what they sound like. SELECT is used to retrieve information, UPDATE is used to update information, INSERT is used to add information, and DELETE is used to remove information. There are also many keywords that can be used to make your commands more specific. The FROM and WHERE clauses specify what table to select information from and specific conditions that the information must meet.

5. After the SQL code, type:

    </cfquery>  <cfquery name="productlist" datasource="products"> SELECT * FROM tblProducts </cfquery> 

   You have now completed your first query. This query will return all the records in the tblProducts table. But if you were to open this file in your browser, you would see just the wirelessagent template screen. This is because you are just getting the information from the database; you're not actually outputting it. In other words, returning information is not necessarily the same as outputting the information.

   Don't close this file, as you will be using it in the next task to actually output the information.

I l @ ve RuBoard