Skip to main content
P
pkonshak
Known Participant
May 4, 2010
Answered

SQL query and CFOUTPUT question

  • May 4, 2010
  • 1 reply
  • 900 views

Hi,

I'm trying to compare two tables in a database.  I need to output a few fields from one table, compare one field between the two tables.  Users are selecting the tables.  I have written an SQL query which does what I need:

<cfquery name="getitems" datasource="snapshot">
select #endtable#.field9, #endtable#.field12, #endtable#.field4, #endtable#.field13, #endtable#.field2, #endtable#.field3, #starttable#.field3
from #endtable#, #starttable#
where #endtable#.field5='#form.field5#'
and #starttable#.field2=#endtable#.field2
</cfquery>

Problem is on output.  You can see I am selecting field3 from two tables.  I also want to output both field3 values.

<cfoutput query="getitems">

#field3#

</cfoutput>

This gives me the value which was selected as #endtable#.field3.

How can I output the other -- #starttable#.field3?

As noted, the table names are variables being passed into the query...  Thanks!

Peter

This topic has been closed for replies.
Correct answer -__cfSearching__-

How can I output the other -- #starttable#.field3?

Use an alias to give the fields different names ie table.column AS SomeName. Otherwise, cfquery may not know which field you mean when you say "field3".

select #endtable#.field9, #endtable#.field12,

Be careful using this type of sql. Depending on the source of your variables, this can pose a sql injection risk.

1 reply

-
-__cfSearching__-Correct answer
Inspiring
May 4, 2010

How can I output the other -- #starttable#.field3?

Use an alias to give the fields different names ie table.column AS SomeName. Otherwise, cfquery may not know which field you mean when you say "field3".

select #endtable#.field9, #endtable#.field12,

Be careful using this type of sql. Depending on the source of your variables, this can pose a sql injection risk.

P
pkonshakAuthor
Known Participant
May 4, 2010

Thank you, that worked beautifully!  Another trick learned..

Also, thanks for the sql injection warning -- I am cleaning the input before passing it into the query.  Much appreciated!

Peter

D
Dan_Bracuk
Inspiring
May 4, 2010

Regarding:

I am cleaning the input before passing it into the query

Even this one?

'#form.field5#'

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices