using two keywords

[brown2005]

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

[Psycho]

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

[brown2005]

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

[Psycho]

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.

[brown2005]

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

[brown2005]

So on my php I would have code to produce;

 

Rugbyleague.com

- sport

- rugby

- rugby league

[brown2005]

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

[Psycho]

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

[brown2005]

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 

[Barand]

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