D F
asked on
Matching Keywords
I have two MySQL tables that both contains keywords (coma delimited) I want to cross reference them to see which of the two tables best match each other. Can some tell the best way to do this with Coldfusion?
ASKER
Yes I have full control, Im building the system from ground up?
Then I'd recommend *not* storing the data as "lists". 95% of the time it's the wrong approach, because you end up with slow, error prone, queries. If you describe what type of comparisons you need to perform, we can recommend a better table structure.
ASKER
I'm building a page with topics that get pull from a database, each topic will have keywords, then I want to find the best banner to put on the topic base on the banners keywords. So everytime a topic is pulled the banner that best matches the topic will be displayed??
So each "Topic" can have one or more keywords
... and each "Banner" can have one or more keywords
... and you want to pick the banner with the greatest number of matching keywords? So for Topic #1 Sales, the best match would be "Banner2"
1, Sales (keywords: aaa, bbb, ccc, .....)
2, Banner2 (keywords: aaa, cccc, .....) <=== Has greatest matching keywords
TopicID, TopicName
1, Sales (keywords: aaa, bbb, ccc, .....)
2, Politics (keywords: ddd, eee, .....)
....
1, Sales (keywords: aaa, bbb, ccc, .....)
2, Politics (keywords: ddd, eee, .....)
....
... and each "Banner" can have one or more keywords
BannerID, BannerName
1, Banner1, (keywords: aaa, xxx, .....)
2, Banner2 (keywords: aaa, cccc, .....)
3, Banner3 (keywords: aaa, rrrr, .....)
1, Banner1, (keywords: aaa, xxx, .....)
2, Banner2 (keywords: aaa, cccc, .....)
3, Banner3 (keywords: aaa, rrrr, .....)
... and you want to pick the banner with the greatest number of matching keywords? So for Topic #1 Sales, the best match would be "Banner2"
1, Sales (keywords: aaa, bbb, ccc, .....)
2, Banner2 (keywords: aaa, cccc, .....) <=== Has greatest matching keywords
ASKER
Yes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try this and let you know tomorrow
Thank you
Thank you
Sounds good. Have a good night.
ASKER
@AGX
When they are entering the keywords for the banners or theTopic are you saying they only use one keyword for each or they can select many keywords for either topic or banner. If you saying they can enter many keyowrds for the topic or banner, how would they enter a mass keywords when adding a new topic or banner to the system?
When they are entering the keywords for the banners or theTopic are you saying they only use one keyword for each or they can select many keywords for either topic or banner. If you saying they can enter many keyowrds for the topic or banner, how would they enter a mass keywords when adding a new topic or banner to the system?
They can select as many or as few keywords as they want.
how would they enter a mass keywords when adding a new topic or banner to the system?
You can structure it however you want. It's just like any other form field where you can add/select multiple items. For ADD, create incrementing field names like: keyword1, keyword2, keyword3, etc.. . Then loop through them to add each record to your db table.
<cfloop from="1" to="#form.totalKeywords#" index="counter">
<cfset newKeyWord = FORM["keyWord"& counter]>
<cfquery ...>
.... INSERT the new value
</cfquery>
</cfloop>
For existing keywords let them select multiple items from a list. Then use the selected id's to do your insert.
<cfset form.BannerID = "12">
<cfset form.keyWordIDList = "5,8,9,10">
<cfquery ...>
INSERT INTO BannerKeyWord (BannerID, KeyWordID)
SELECT #val(form.BannerID)#, KeyWordID
FROM Keyword
WHERE KeyWordID IN (
<cfqueryparam value="#form.keyWordIDList #" list="true" cfsqltype="cf_sql_integer" > )
</cfquery>
how would they enter a mass keywords when adding a new topic or banner to the system?
You can structure it however you want. It's just like any other form field where you can add/select multiple items. For ADD, create incrementing field names like: keyword1, keyword2, keyword3, etc.. . Then loop through them to add each record to your db table.
<cfloop from="1" to="#form.totalKeywords#" index="counter">
<cfset newKeyWord = FORM["keyWord"& counter]>
<cfquery ...>
.... INSERT the new value
</cfquery>
</cfloop>
For existing keywords let them select multiple items from a list. Then use the selected id's to do your insert.
<cfset form.BannerID = "12">
<cfset form.keyWordIDList = "5,8,9,10">
<cfquery ...>
INSERT INTO BannerKeyWord (BannerID, KeyWordID)
SELECT #val(form.BannerID)#, KeyWordID
FROM Keyword
WHERE KeyWordID IN (
<cfqueryparam value="#form.keyWordIDList
</cfquery>
ASKER
Ok testing again, thank you will let you know
ASKER
_agx_:
Im having trouble with
SELECT TOP 1 b.BannerID, b.BannerName, COUNT(*) MatchingKeywords
FROM Banner b
INNER JOIN BannerKeyWord bk ON bk.BannerID = b.BannerID
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = bk.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY b.BannerID, b.BannerName
ORDER BY COUNT(*) DESC
Im sorry but I can't figure out how to do "Inner Join"
Here is what I have please work with me on this
I have three tables: affilatekeywords, topickeywords and banners.
The affilatekeywords table has two fields: keywordid and affilateID
The topickeywords table has two fields: keywordid and topicid
I'm trying to get the affilateid from the affilatekeywords table that has the most matched keywords with the (current)topicid from topickeywords that the visitor is veiwing.
I hope this made sense
Im having trouble with
SELECT TOP 1 b.BannerID, b.BannerName, COUNT(*) MatchingKeywords
FROM Banner b
INNER JOIN BannerKeyWord bk ON bk.BannerID = b.BannerID
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = bk.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY b.BannerID, b.BannerName
ORDER BY COUNT(*) DESC
Im sorry but I can't figure out how to do "Inner Join"
Here is what I have please work with me on this
I have three tables: affilatekeywords, topickeywords and banners.
The affilatekeywords table has two fields: keywordid and affilateID
The topickeywords table has two fields: keywordid and topicid
I'm trying to get the affilateid from the affilatekeywords table that has the most matched keywords with the (current)topicid from topickeywords that the visitor is veiwing.
I hope this made sense
Not tested, but something like this should work
SELECT TOP 1 a.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY a.AffiliateID
ORDER BY COUNT(*) DESC
SELECT TOP 1 a.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY a.AffiliateID
ORDER BY COUNT(*) DESC
ASKER
Ok! not to sound dumb, but I am to this: does the "ak" "tk" "a" there just to show me something?
No, the "ak" and "tk" are table aliases used in the query.. But I just noticed there's a typo. It should be:
SELECT TOP 1 ak.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY ak.AffiliateID
ORDER BY COUNT(*) DESC
Btw: I'm assuming affiliates is just a new table in addition to these: http:#a35090723
SELECT TOP 1 ak.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY ak.AffiliateID
ORDER BY COUNT(*) DESC
Btw: I'm assuming affiliates is just a new table in addition to these: http:#a35090723
ASKER
I get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 ak.AffiliateID, COUNT(*) MatchingKeywords FROM AffiliateKeyWords ak ' at line 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 ak.AffiliateID, COUNT(*) MatchingKeywords FROM AffiliateKeyWords ak ' at line 1
>> use limit TOP or LIMIT to grab only 1 banner record. The syntax is db dependent.
Like I said before syntax is db dependent. TOP is for MS SQL. If you're using MySQL it uses LIMIT. The syntax would be more like
SELECT ak.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY ak.AffiliateID
ORDER BY COUNT(*) DESC
LIMIT 1
Like I said before syntax is db dependent. TOP is for MS SQL. If you're using MySQL it uses LIMIT. The syntax would be more like
SELECT ak.AffiliateID, COUNT(*) MatchingKeywords
FROM AffiliateKeyWord ak
INNER JOIN TopicKeyWord tk ON tk.KeyWordID = ak.KeywordID
WHERE tk.TopicID = #val(someTopicID)#
GROUP BY ak.AffiliateID
ORDER BY COUNT(*) DESC
LIMIT 1
I'm sure you know storing "lists" of values is not a good structure if you need to perform comparisons. A more normalized structure would make things much easier. Do you have any leeway with the table structure?