Solved

Matching Keywords

Posted on 2011-03-09
18
379 Views
Last Modified: 2013-12-24
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?
0
Comment
Question by:overcolor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
18 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 35090487
Can you define "best match each other" with an example?

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

Author Comment

by:overcolor
ID: 35090534
Yes I have full control, Im building the system from ground up?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35090585
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.
0
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 

Author Comment

by:overcolor
ID: 35090595
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??
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35090627
So each "Topic" can have one or more keywords

TopicID, TopicName
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, .....)

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

Author Comment

by:overcolor
ID: 35090650
Yes
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 35090723
Then I'd recommend having separate tables for unique "Topics", "Banners" and "Keywords".

TABLE: Topic
=============
TopicID (unique record ID)
TopicName

TABLE: Banner
=============
BannerID (unique record ID)
BannerName
BannerImage
....

TABLE: Keyword
=============
KeywordID (unique record ID)
Keyword
Then store the topic keywords and banner keywords in a junction table.

TopicKeyWord    (Topic + Keyword combinations)
=============
TopicID
KeywordID

BannerKeyWord   (Banner + Keyword combinations)
=============
BannerID
KeywordID

A query like this would give you the banners that contained at least 1 matching keyword for a specific topic

SELECT 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

Open in new window


To get the best match,  order by the total matches (descending). Then use limit TOP or LIMIT to grab only 1 banner record. The syntax is db dependent.

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

Open in new window


0
 

Author Comment

by:overcolor
ID: 35090749
I will try this and let you know tomorrow
Thank you
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35090777
Sounds good. Have a good night.
0
 

Author Comment

by:overcolor
ID: 35096962
@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?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35097897
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>


0
 

Author Comment

by:overcolor
ID: 35098137
Ok testing again, thank you will let you know
0
 

Author Comment

by:overcolor
ID: 36162465
_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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36162545
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
0
 

Author Comment

by:overcolor
ID: 36162592
Ok! not to sound dumb, but I am to this: does the "ak" "tk" "a" there just to show me something?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36162700
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
0
 

Author Comment

by:overcolor
ID: 36163252
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36163549
>> 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

0

Featured Post

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question