SQL Problem 1

[ahksar]

Hi,

I am trying to retrieve columns from a database, where ID matches the form ID. Then if the number of records from those columns are Zero, I use a <CFIF> tag to perform certain operation, otherwise another set of operations. When I use the DEBUG query in the <CFQUERY> tag, it shows that the records obtained are always ONE, whatever the number of records in those columns. Any help as to how I can retrieve the records and count them will be useful.
Thank you very much
ahksar

 

[Bramvg]

Hi ahksar,


To check use:

<cfif #myquery.recordcount# eq 0>
No records found
<cfelse>
<cfoutput query=&quot;myquery&quot;>
#ID#
</cfoutput>
</cfif>

Hope this has helped you
bram

 

[ahksar]

Bram,

I did that.

<cfquery datasource=&quot;database&quot; name=&quot;GetLinks&quot; debug>
SELECT strLink1 , strLink2 , strLink3
FROM tblComments WHERE commentID='#GetComments.commentID#'
</cfquery>

<cfif 0 eq #GetLinks.recordcount#>

Do something
<cfelse>
Related Links:<br>
<a href=&quot;#strLink1#&quot;>#strLink1#</a><br>
<a href=&quot;#strLink2#&quot;>#strLink2#</a><br>
<a href=&quot;#strLink3#&quot;>#strLink3#</a><br><br>
</cfif>

And when i debug, it always shows records obtained = 1,
even if there are zero, or 3 records in the columns.

Thanks
jujian

 

[ahksar]

Bram,

I did that.

<cfquery datasource=&quot;database&quot; name=&quot;GetLinks&quot; debug>
SELECT strLink1 , strLink2 , strLink3
FROM tblComments WHERE commentID='#GetComments.commentID#'
</cfquery>

<cfif 0 eq #GetLinks.recordcount#>

Do something
<cfelse>
Related Links:<br>
<a href=&quot;#strLink1#&quot;>#strLink1#</a><br>
<a href=&quot;#strLink2#&quot;>#strLink2#</a><br>
<a href=&quot;#strLink3#&quot;>#strLink3#</a><br><br>
</cfif>

And when i debug, it always shows records obtained = 1,
even if there are zero, or 3 records in the columns.

Thanks
ahksar

 

[tlhawkins]

Just a suggestion for debugging.

Try a different Query against the same table and see if you can get the recordcount working. Might be a database issue.

 

[CFHub]

Remember that &quot;GetComments.CommentID&quot; will reurn only the &quot;first&quot; ID that was returned in the GetComments Query.

It is also interesting to see that your &quot;CommentID&quot; field is wrapped in &quot;text&quot; delimiters (Is this not throwing an error? OR is this value not a simple number?)

<cfquery datasource=&quot;database&quot; name=&quot;GetLinks&quot; debug>
SELECT strLink1 , strLink2 , strLink3
FROM tblComments WHERE commentID='#GetComments.commentID#'
</cfquery>

Also, keep in mind that RecordCount returns &quot;Rows&quot; not columns. You could try a different method alltogether if you need to determine what &quot;Columns&quot; are returned. How about:

<cfoutput strLink1 query=&quot;GetLinks&quot;>
<cfif IS NOT &quot;&quot;>
<a href=&quot;#strLink1#&quot;>#strLink1#</a><br>
</cfif>

<cfif strLink2 IS NOT &quot;&quot;>
<a href=&quot;#strLink2#&quot;>#strLink2#</a><br>
</cfif>

<cfif strLink3 IS NOT &quot;&quot;>
<a href=&quot;#strLink3#&quot;>#strLink3#</a><br>
</cfif>

 

[CFHub]

Lost whithout the good old &quot;edit&quot; button (Does anyone know if you can edit posts in this forum?)

Try this &quot;improved&quot; code...

<cfoutput query=&quot;GetLinks&quot;>

<cfif strLink1 IS NOT &quot;&quot;>
<a href=&quot;#strLink1#&quot;>#strLink1#</a><br>
</cfif>

<cfif strLink2 IS NOT &quot;&quot;>
<a href=&quot;#strLink2#&quot;>#strLink2#</a><br>
</cfif>

<cfif strLink3 IS NOT &quot;&quot;>
<a href=&quot;#strLink3#&quot;>#strLink3#</a><br>
</cfif>

</cfoutput>

 

[ahksar]

&quot;It is also interesting to see that your &quot;CommentID&quot; field is wrapped in &quot;text&quot; delimiters (Is this not throwing an error? OR is this value not a simple number?)&quot;

The reason this is working is that this value is a 16 digit hex number. Is it better to use a simple number or a hex number?

Thanks for the advice above. The reason you surmised was right, it was returning the ROW count, and not COLUMN. Thats why the records were always one. I used your code and it worked immediately. Saved me a lot of trouble.Thanks once again for all your help.
ahksar