How to use a variable in a cfquery?

Question

I use #variable.user# to show the active user on a webpage I want to use that user to look up in a table and find their work location so I can create a custom few for each work location. How can I use that variable in a query to find their location in the table?

Answer 1

You can do this two ways:

1. Reference the variable via #myVar#. If it is a string, put single quotes around it. I do not recommend doing it this way.

EX:

<cfquery name="somequery" datasource="somedb">
SELECT
    sometable.somecolumn
FROM
    sometable
WHERE
    sometable.somecolumn = '#myVar#'
</cfquery>

2. Using cfqueryparams to verify/force a datatype and reference that variable. This prevents SQL injection and other SQL based errors if you have specific constraints. I recommmend doing it this way. See cfqueryparam tag abobe documentation.

EX:

<cfquery name="somequery" datasource="somedb">
SELECT
    sometable.somecolumn
FROM
    sometable
WHERE
    sometable.somecolumn = <cfqueryparam cfsqlype="varchar" value="#myVar#">
</cfquery>