cocolote/CF-Queries.md

## CF-Queries.md

      
    Raw
  

              CF-Queries.md
            
          
    #ColdFusion Queries

To interact with a database with CFML first you need to setup the data source in the administrator site.

The table that I'm usign in this examples

####----TestTable-----


id
myDataAlfa
myDataInt


1
Alfa
1


2
Delta
4


Some examples to start using the databases with coldfusion.


Basic query

<cfquery name="firstQ" datasource="tsdata.ts24">
    SELECT * FROM TestTable
</cfquery>

Display the query to see what info you get from the database

<cfdump var="#firstQ#" />

Output the info in your query
This will display only one row of the table

<cfoutput>
    <p><i>myDataAlfa: </i>#firstQ.myDataAlfa# <i>myDataInt: </i>#firstQ.myDataInt#</p>
</cfoutput>

Loop through the data

<cfoutput>
    <cfloop query="#firstQ#">
        <p><i>myDataAlfa: </i>#firstQ.myDataAlfa# <i>myDataInt: </i>#firstQ.myDataInt#</p>
    </cfloop>
</cfoutput>

Extra info about the table

<cfoutput>
    <p>#firstQ.columnlist#</p>
    <p>#firstQ.recordcount#</p>
</cfoutput>

Query Output Grouping (like in the tutorial, the c fart gallery)

<cfquery name="myQuery" datasource="cfartgallery"> 
    SELECT * FROM art
    ORDER BY issold
</cfquery>

<cfoutput Query="myQuery" group="issold">
    <p>
    	Sold ?: #YesNoFormat(myQuery.issold)#:<br />
    <blockquote>
	    <cfoutput>
	        #myQuery.artname#: #DollarFormat(myQuery.price)#<br />        
	    </cfoutput>
    </blockquote>
    </p>
    <hr />
</cfoutput>

Query Param
The param is use to avoid sql injections and validate data entered by the user

<cfquery name="myQuery" datasource="cfartgallery">  
    SELECT * FROM artists
    WHERE firstname = <cfqueryparam value="#form.name#" cfsqltype="cf_sql_varchar" />
</cfquery>
The <cfqueryparam> is getting the value from a variable defined before for the form.
cfsqltype is validating the value to be cf_sql_varchar what it means that the value has to be a varchar

Query Caching

<cfquery name="myQuery" datasource="cfartgallery" cachedwithin="#createTimespan(0,1,0,0)#"> 
    SELECT * FROM artists 
</cfquery>
This is one of the way to cache a query result. In this example the chache will last for 1 hour, if the query doesn't change.

Dynamic Queries
The query is generated based on the info passed to it and the decisions made by the statements that you put inside the query

<cfquery name="myQuery" datasource="cfartgallery">  
    SELECT firstname, lastname, email FROM artists  
    WHERE 1 = 1
     <cfif structkeyExists(form, 'firstname') and len(form.firstname)>
        AND firstname = <cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar" />
    </cfif>
    <cfif structkeyExists(form, 'lastname') and len(form.lastname)>
        AND lastname = <cfqueryparam value="#form.lastname#" cfsqltype="cf_sql_varchar" />
    </cfif>
</cfquery>
This is a pretty heinous way to build a query, is better to generate a variable and then use it to execute the query like this:
<cfset query = 'SELECT firstname, lastname, email FROM artists' />
<cfif structkeyExists(form, 'firstname') and len(form.firstname)>
    <cfset query = query & "WHERE firstname = " & "#<cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar" />#" />
</cfif>

<cfquery name="myQuery" datasource="cfartgallery">  
    #query#
</cfquery>

Limiting Result Counts

<cfquery name="myQuery" datasource="cfartgallery" maxrows="100">
    SELECT firstname, lastname, email FROM artists
</cfquery>
maxrows is like adding to the query LIMIT and a number so it will only retrive 100 rows of the entire table.

Inset and Indentity Retrival
Sometimes when inserting a new record you will need the last record inserted. You could get it doing after the insert a SELECT query to get the last row inserted but CF has a better way to do this

<cfquery result="qryResult" datasource="cfartgallery">
    INSERT INTO TableTest
    (myDataAlfa, myDataInt)
    VALUES
    (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Gamma" />,
     <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="5" />,)  
</cfquery>

<cfdump var="#qryResult#" />
When this code is executed the new row inserted would be available in the variable qryResult. It will keep the whole object created.
#Queries in CFScript

Basic query

<cfscritp>
myQry = new Query();
myQry.setDatasource("cfartgallery"); 
myQry.setSQL("SELECT firstname, lastname, email FROM artists");
myQuery = myQry.execute();
writeDump(myQuery.getResult());
writeDump(myQuery.getPrefix());
</cfscript>

Concatenated methods

<cfscript>
myQueryResult = new Query(sql="SELECT firstname, lastname, email FROM artists", datasource="cfartgallery").execute().getResult();
writeDump(myQueryResult);
</cfscript>

Using Query Params

<cfscript>
myQry = new Query();
myQry.setDatasource("cfartgallery"); 
myQry.setSQL("SELECT artname, description FROM art WHERE issold = :sold");
myQry.addParam(name: "sold", value: "1", cfsqltype: "CF_SQL_INT");
myQuery = myQry.execute();
writeDump(myQuery.getResult());
writeDump(myQuery.getPrefix());
</cfscript>