Sagewire Logo

No duplicate result from DB...

8 Message(s) by 4 Author(s) originally posted in cfml discussion


From: C-Rock Date:   Friday, October 26, 2007
I'm very inexperienced with Cold Fusion so I apologize if this is simple.

I'm using flash remoting to call a function which searches through a mysql
database through a column called name and description for a "keyword ". The
script I have written works great except I get duplicate results of an id if
the name and description column both contain the keyword.

Here's my code, how do I fix this so only one id is returned if both columns
have the keyword. Thanks,

<!--Search -->
<CFFUNCTION name="startSearch" access="remote" returnType="query ">
<CFARGUMENT name="keywords" type="string" required="yes">
<CFQUERY name="startSearchQuery" datasource="housesmarts"
username="housesmarts" password="Fa9293ff">

SELECT id, name, description, imageLink, videoSize
FROM segments
WHERE description LIKE '%#ARGUMENTS.keywords#%'
OR name LIKE '%#ARGUMENTS.keywords#%'
</CFQUERY>
<CFRETURN startSearchQuery >
</CFFUNCTION>


From: Dan Bracuk Date:   Friday, October 26, 2007
The answer to your specific question is to change the work or to and in your
where clause.

It probably won't solve your problem though. If your db really does have
record s where everything except the id is the same, that's the big problem


From: JR Bob Dobbs Date:   Friday, October 26, 2007
I'm not experienced with MySql but I assume that it supports SELECT DISTINCT.

Try
SELECT DISTINCT id, name, description, imageLink, videoSize
FROM segments
etc...


From: Dan Bracuk Date:   Friday, October 26, 2007
[q][i]Originally posted by: [b][b]JR "Bob" Dobbs[/b][/b][/i]
I'm not experienced with MySql but I assume that it supports SELECT DISTINCT.

Try
SELECT DISTINCT id, name, description, imageLink, videoSize
FROM segments
etc...[/q]
If id is the primary key, that won't work.


From: JR Bob Dobbs Date:   Friday, October 26, 2007
Mr. Bracuk is right my previous post was incorrect. I re-read your original
post. If I understand correctly you want at most one record to be returned by
your query.
You could use derived tables, I assume MySQL supports this feature. Run two
queries and only return the first row. See attached code.

Another option'd be to use the maxrows attribute of the CFQUERY tag and
set it to "1".
SELECT TOP 1 id, name, description, imageLink, videoSize
FROM
(
<!--- get rows where name matches keyword --->
SELECT id, name, description, imageLink, videoSize, 1 AS order_val
FROM segments
WHERE name LIKE '%#ARGUMENTS.keywords#%'

UNION

<!--- get rows where description matches keyword --->
SELECT id, name, description, imageLink, videoSize, 1 AS order_val
FROM segments
WHERE description LIKE '%#ARGUMENTS.keywords#%'

) AS Qry
ORDER BY order_val <!--- if you want to change the priority of name vs.
description column taking presidence change the order_val value in for the
query --->



From: C-Rock Date:   Friday, October 26, 2007
It seems that the AND does not work giving me all the results, both columns must
contain the keyword for that. OR seems to works the best, however I'm not sure
if it's correct.

My db does not have everything the same. There are certain segments of this tv
show by which the videos are broken down. Each video has a title and a
description. We've 500 episodes and no one wants to write keywords for this.
So I'm just searching through the titles and descriptions for the keywords.

Thanks for all your help


From: Dan Bracuk Date:   Friday, October 26, 2007
As a workaround, change this,
SELECT id, name, description, imageLink, videoSize
FROM segments

to this
SELECT name, description, imageLink, videoSize, max(id) id
FROM segments
group by name, description, imageLink, videoSize


From: cf_dev2 Date:   Friday, October 26, 2007
I get duplicate results of an id if the name and description column both
contain the keyword.



Maybe I'm misunderstanding your question but .. the query you posted does a
SELECT from a single table. I do not see how it could return duplicate "ID"
values unless "ID" isn't the unique identifier for that table. Any chance
"ID" is FK representing the video ID?

If not what do you mean by duplicates?



Next Message: CFHTTP problem



Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional