Jump to content
brown2005

using two keywords

Recommended Posts

I currently have three tables

 

Domains

Keywords

Words

 

In words I would have

 

Sport

Football

Rugby

 

And allocate them to the domain in keywords, but say I wanted Rugby League, would I have an entry in words as Rugby League or two seperate entries rugby and league? And how would i combine them in the keywords table?

 

Keywords_id

Keywords_domain

Keywords_word

 

Thanks

Share this post


Link to post
Share on other sites

That is impossible to answer without understanding how the data is used.

Share this post


Link to post
Share on other sites

Well the keywords are related to the domains, when you search a keyword it will show domains linked with that keyword

Share this post


Link to post
Share on other sites

brown2005, you need to help us out here. You need to be specific and provide clear details. Giving a general response such as that doesn't help to provide clarity. For example, I don't fully understand how you created the three tables and what the association between them are. It appears the "domains" table holds the domains and their info. Then it appears the "words" table holds the actual keywords. So, I will *assume* the "keywords" table records contain a foreign key reference between the domains and words tables.

 

But, I don't know how you "USE" those values and what you expected results are. I would expect that you would want one entry for "rugby" and one for "league". I don't see any need to create a specific association for the concatenated "rugby league". But, since I don't know how you implemented the keyword checks or how "rugby league" would not work with just the single words, I can't provide any guidance.

Share this post


Link to post
Share on other sites

Domains_id

Domains_url

 

Keywords_id

Keywords_domain (domains_id)

Keywords_word (words_id)

 

Words_id

Words_word

 

So domains is for domains I own, then keywords will bé the keywords for those domains, with words being the words that are used for the keyword.

 

So say I have a Rugby League website, the keywords I would associate with this are sport, rugby and rugby league.

 

Domains:

1 rugbyleague.name

 

Keywords

1 1 1

1 1 2

1 1 2 3 (2 and 3 is in keywords_word)

 

(The 2 3 producing rugby league, so some how i would need to get the two words on the php code or would you say use it as 4 rugby league instead of league in words datbase, thus having extra words)

 

Words:

1 Sport

2 Rugby

3 League

Share this post


Link to post
Share on other sites

What I dont want is for the results to bé

 

Sport

Rugby

League

 

As when someone searches league I wouldnt want the website to come up. Only when they search rugby leahue i want it coming up

Share this post


Link to post
Share on other sites

So on my php I would have code to produce;

 

Rugbyleague.com

- sport

- rugby

- rugby league

 

 

What I dont want is for the results to bé

 

Sport

Rugby

League

 

As when someone searches league I wouldnt want the website to come up. Only when they search rugby leahue i want it coming up

 

Then, didn't you just answer your own question? Although, having a keyword for "rugby" and then another for "rugby league" seems odd. I think you're making it too specific. If "rugby" is a keyword on its own, why do you need "rugby league"?

Share this post


Link to post
Share on other sites

for example they could search rugby and get websites for both rugby league and rugby union, but if they search rugby league only get the rugby league one not the union 

Share this post


Link to post
Share on other sites

There are two ways of doing this.

 

1. Given that you have the domain table and the word tables then you you would have a "normalized" table (keyword) to link the two. You would then search the keyword table for those domain_ids that had links to both "rugby" and "league" (ie word_ids 3 and 5 in the example below)

Domain                              Word
+----+---------------------+        +----+-----------------+
| id | Domain              |        | id | Word            |
+----+---------------------+        +----+-----------------+
|  1 | londonharriers.org  |        |  1 | Sport           |
|  2 | leedsrhinos.com     |        |  2 | Athletics       |
|  3 | manchesterrufc.org  |        |  3 | Rugby           |
|  4 | wiganwarriors.net   |        |  4 | Union           |
+----+---------------------+        |  5 | League          |
   |                                +----+-----------------+
   |                                   |
   +-------------------------+         |                     
                  Keyword    |         |
                  +----+-----------+---------+
                  | id | domain_id | word_id |
                  +----+-----------+---------+
                  |  1 |     1     |    1    |
                  |  2 |     1     |    2    |
                  |  3 |     2     |    1    |
                  |  4 |     2     |    3    |
                  |  5 |     2     |    5    |
                  |  5 |     3     |    1    |
                  |  5 |     3     |    3    |
                  |  5 |     3     |    4    |
                  |  6 |     4     |    1    |
                  |  7 |     4     |    3    |
                  |  8 |     4     |    5    |
                  +----+-----------+---------+

2. The other way is to use FULLTEXT searches on text field/s

Domain                             
+----+---------------------+--------------------------------------------------+
| id | Domain              | Description                                      |
+----+---------------------+--------------------------------------------------+
|  1 | londonharriers.org  | Athletics club in London promoting sport for all |
|  2 | leedsrhinos.com     | Yorkshire based rugby league club                |
|  3 | manchesterrufc.org  | Rugby union sports club in South Manchester      |
|  4 | wiganwarriors.net   | Lancashire based rugby league sports club        |
+----+---------------------+--------------------------------------------------+

By setting up a fulltext index you could then search the text for, say

  • Rugby but not Union
  • Rugby
  • Rugby and must contain league also
  • etc

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.