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 last

riogrande
02-21-2008 @ 3:13 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 509
Joined: Mar 2003

My distinct is not working as the country is being output more than once. I want the countries.country to be unique. Why isn´t the distinct working?TIA

<cfquery name="GetCountries" datasource="admindb">
SELECT DISTINCT clientinfo.countryid, clientinfo.Visit, countries.countryid, countries.country
FROM clientinfo, countries
WHERE clientinfo.countryid = countries.countryid
and month(clientinfo.Visit) = #url.monthnowis#
</cfquery>

<cfoutput>
<cfxml variable="MyXMLDoc">
<?xml version='1.0' encoding='utf-8' ?>
<map>
   <cfloop query="GetCountries">
<country name="#countryid#">
     <title>#country#</title>
     <color>0xFF0000</color>
</country>
   </cfloop>
</map>
</cfxml>
</cfoutput>


dlackey
02-21-2008 @ 3:34 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

YOu need to take out the clientinfo.countryid assuming that is the tables unique ID.  

Cheers,
Chip

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

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

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>

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 @ 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: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

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

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
-----------------------------

PAGE: 1 2

Website Designed and Developed by Pablo Varando.