Getting Data From the Database - You're First Query
Getting data from a database using Coldfusion is probably much easier than you might have thought. It only requires on coldfusion tag, cfquery. cfquery can be used to execute SQL commands to the database.
Before you can access the database with Coldfusion, you'll need to set up a datasource in the Coldfusion Administrator. If you don't have access to the Administrator, you'll have to have your web master or hosting company set up the datasource name (DSN). Some hosting companies have a control panel to let you set up DSN's without using the Coldfusion Administrator.
We'll assume you set up a DSN called 'myTestDSN' that points to a database with a table called 'myTestTable' that contains field names id, name, and age.
To get ALL of the data out of the table 'myTestTable' use cfquery
<cfquery name="qMyFirstQuery" datasource="myTestDSN">
SELECT id,name,age FROM myTestTable
</cfquery>
Notice that the datasource attribute of cfquery is the same as the name od the DSN that you set up. We use the name attribute to refer to when we want to use the results of the query. A quick and easy way to simply view the results is to use CFDUMP.
<cfdump var="#qMyFirstQuery#">
Outputing the Data
While cfdump is good for testing that you've got some data, it is not meant to be used for displaying data to the public.
For this, we use cfoutput. cfoutput has an attribute called query which will loop through the rows of our query and output the field names that we want.
Using the same query name as the one we specified in our cfquery tag above, we build a simple cfoutput tag. Cfoutput always requires a start and end tag.
Inside the cfoutput tags we can now specify our field names to display. Remember, our table we used above has three fields: id,name, and age. We can use those field names in the cfoutput tag to display their values. You can only display values of fields that you've specified in your SQL query in the cfquery tag. If you did not specify the field name, it is not returned from the database.
cfoutput is going to loop through the rows of the resulting data from the cfquery tag. Along with the field names, there are also a few other variables that you can use during the output of a query. CurrentRow will tell you which row of output you are currently on, and recordCount will tell you the total number of records found with your query.
<cfoutput query="qMyFirstQuery">
#qMyFirstQuery.currentRow#) Name: #name# Age: #age#<br/>
</cfoutput>
This would output something like:
1) Name: Kevin Age: 27
2) Name: Cortney Age: 29
3) Name: Phoebe Age: 4
Things to Remember
Make sure your Datasource Name is set up in the ColdFusion Admin.
Make sure you're table name and field names are correct in the SQL in cfquery.
Make sure there actually data in the database if you are expecting any output.
____________
