Skip to main content
M
matthisco
Inspiring
October 1, 2009
Question

SQL query help

  • October 1, 2009
  • 1 reply
  • 1323 views

Afternoon folks,

I have a query that pulls all the content out of a cms, for each site.

SELECT *
FROM sites
LEFT JOIN pages
ON sites.siteid=pages.siteid
LEFT JOIN forum
ON sites.siteid=forum.siteid
ORDER BY sites.sitename, sites.siteid, pageorder ASC

I'd like to display a link if the siteid exists in the forum table.

Can anyone please tell me how I can check for this?

So far I have below, but nothing shows up:

<cfoutput query="getcms" group="siteid">

     <cfif isdefined("forum.siteid")>
    show link to forum
              </cfif>

</cfoutput>

Thanks in advance

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
October 1, 2009

Well you have the correct basic idea, just the wrong test.

The column in the data record set is always going to exist.  It is just going to be empty (null) if there was no values for it.  I.E. the database would have returned a null value which ColdFusion interprets as an empty string.

So your if statement needs to check for an empty string not the nonexistance of a variable.

<cfif len(getcms.forums.siteid)>

P.S. I doubt that your record set would be that complex.  It is possilbe that the database has returned the table name and column name, but that is usually rare.  Usually it just returns the column names.

D
Dan_Bracuk
Inspiring
October 1, 2009

This:

<cfif len(getcms.forums.siteid)>

will throw an error.  You have to use queryname.fieldname[record number].  For the OP, pick another field from the forum table.  siteid will show up twice, another reason why using select * is generally a bad idea.

M
matthiscoAuthor
Inspiring
October 1, 2009

Thankyou both for your quick replies!

It now works a treat!

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