EasyCFM.COM ColdFusion Forums / MySQL Related Issues / Distinct doesn´t seem to work

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Distinct doesn´t seem to work -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts first

Lossed
02-27-2008 @ 3:09 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

To the motivated, hungry student willing to seek go the spoils. For the rest of us lazy sods, there is this , and this easycfm tutorial talks about it too, and straight from the horses mouth (livedocs):

" cfqueryparam

Description

Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times."

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

louissto56
02-27-2008 @ 7:31 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Hey lossed, you got a link? Im not in the mood for cf searchin (pun DEFINETLY intended Wink )

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

Lossed
02-27-2008 @ 4:26 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

quote:

you should use cfqueryparam ANYTIME the query is accepting values from outside the system such as a form, url, cookie, etc.


But wait...there's more... google "bind variables and cfqueryparam" to learn how it can help speed up your queries too.

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

cfSearchin
02-26-2008 @ 5:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

riogrande,

Yes. "URL" is not a valid cfsqltype.

Check the documentation. It has all of the valid cfsqltpes for the various databases.

http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm

dlackey
02-22-2008 @ 11:54 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

I don't believe url is a valid cfsqltype but here is a url to reference the types:

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm

More than likely for this instance, you are going to use cf_sql_integer.

Cheers,
Chip

riogrande
02-22-2008 @ 11:42 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

Thanks Chip so like this is fine?

<cfset countryval="5">
<cfquery name="q_countries" datasource="mydts">
SELECT     CASE WHEN Visit_Count >= 5 THEN Country ELSE 'LESS THAN #countryval# VISITS' END AS
CountryName, SUM(Visit_Count) AS Visit_Count
FROM     (
SELECT     UCASE(Country) AS Country,  COUNT(*) AS Visit_Count
FROM     ClientInfo
WHERE month(visit) = <cfqueryparam value = "#url.monthnowis#"      CFSQLType = "url"
maxLength = "2" null = "No" list = "No">
<cfif url.daynowis neq "">
and day(visit) = <cfqueryparam value = "#url.daynowis#"  CFSQLType = "url"      maxLength = "2" null = "No" list = "No">
</cfif>
GROUP BY UCASE(Country)
          ) AS x
GROUP BY CountryName
</cfquery>

dlackey
02-22-2008 @ 11:22 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

you should use cfqueryparam ANYTIME the query is accepting values from outside the system such as a form, url, cookie, etc.

Cheers,
Chip

riogrande
02-22-2008 @ 10:14 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

Thanks a lot for the replies, I was getting confused selecting a cloumn that was going to be used in the where part. It works fine now.
Should I use cfqueryparam on another part of the query or only on url params? TIA

<!--- Query the Datasource --->
<cfquery name="GetCountries" datasource="mydatasource">
SELECT DISTINCT countries.countryid, countries.country
FROM clientinfo, countries
WHERE clientinfo.countryid = countries.countryid
and month(clientinfo.Visit) = <cfqueryparam value = "#url.monthnowis#"      CFSQLType = "url"    
maxLength = "2" null = "No" list = "No">
</cfquery>

louissto56
02-21-2008 @ 11:41 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

You could use GROUP BY

SELECT clientinfo.countryid, clientinfo.Visit, countries.countryid, countries.country
FROM clientinfo, countries
WHERE clientinfo.countryid = countries.countryid
and month(clientinfo.Visit) = #url.monthnowis#
GROUP BY clientinfo.countryid

Try that

Louis

____________________________
My Biz List.com.au :: Feb 08

cfSearchin
02-21-2008 @ 3:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

You are misunderstanding how DISTINCT works.  DISTINCT will operate on all of the columns in your select list.  So your query will return return all distinct combinations of:

countryid, Visit, countryid, country

Also, you should not have two columns with the same name in your select list. It can produce confusing results.  

Unless there is some reason you need those extra columns, just select distinct country.


<!--- this is a sql injection risk --->
SELECT DISTINCT countries.country
FROM clientinfo, countries
WHERE clientinfo.countryid = countries.countryid
AND   month(clientinfo.Visit) = #url.monthnowis#


But, you should consider using cfqueryparm. Using url values in that manner puts your database at risk for sql injection.

This message was edited by cfSearchin on 2-21-08 @ 3:42 PM

PAGE: 1 2

Website Designed and Developed by Pablo Varando.