I'm looking for the best method to improve the search function of my website.
I have two MySQL Tables 'articles' and 'tags'
-> Columns in 'articles'
aid (auto increment)
headline
..and a few more
-> Columns in 'tags' (New Entry for every Tag)
tid (auto increment)
aid (ID from 'articles')
tag
For example If I have an article with the headline "This is a green Article" and the tags for this article are "blue", "yellow"
and let's say there's another Articles with the headline "This is another Test" with following tags "blue","yellow","pink" and "black"
If a Visitor is now searching for "green pink black" mysql should find the Article "This is a green Article" and also the other article because of it's tags "pink" and "black"
Furthermore i need a function which orders the articles by their relevance. So in this example the article "This is another Test" should be shown first because if the tags "pink" and "black" and the article "This is a green Article" is on Position two (only "green" in the headline)
In the last hours I tried a few queries like match..against and joins but this is too complex for me.
Has anyone a hint for me?
(Sorry for my bad english)
Your query should look like:
SELECT
DISTINCT articles.aid
FROM articles
INNER JOIN tags
ON articles.aid = tags.aid
WHERE tags.tag IN ("green", "pink", "black");
Since both tables have a common attribute (namely, the article ID), you can use an INNER JOIN to join the two tables, and filter results based on other attributes (in this case, the tag name) in both tables.
The above query will give you the list of article IDs with any of the green, pink, or black tags.
edit
Sorry, I didn't see your requirement for relevance. If you want to find out how many tags were found on each Article, turn it into a GROUPed query, and COUNT the number of tags found:
SELECT
articles.aid,
articles.headline,
COUNT(tags.tid)
FROM articles
INNER JOIN tags
ON articles.aid = tags.aid
WHERE tags.tag IN ("green", "pink", "black")
GROUP BY articles.aid;
Try out the scenario...
First, set up the database:
mysql> CREATE TABLE articles (aid integer auto_increment, headline varchar(32), key(aid));
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE tags (tid integer auto_increment, aid integer, tag VARCHAR(16), key(tid));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO articles (headline) values ("This is a green Article"), ("This is another Test");
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles;
+-----+-------------------------+
| aid | headline |
+-----+-------------------------+
| 1 | This is a green Article |
| 2 | This is another Test |
+-----+-------------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tags (aid, tag) VALUES (1, "blue"), (1, "yellow"), (2, "blue"), (2, "yellow"), (2, "pink"), (2, "black");
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
and then run some queries:
mysql> SELECT articles.aid, articles.headline, COUNT(tags.tid) FROM articles INNER JOIN tags ON articles.aid = tags.aid WHERE tags.tag IN ("green", "pink", "black") GROUP BY articles.aid;
+-----+----------------------+-----------------+
| aid | headline | COUNT(tags.tid) |
+-----+----------------------+-----------------+
| 2 | This is another Test | 2 |
+-----+----------------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT articles.aid, articles.headline, COUNT(tags.tid) FROM articles INNER JOIN tags ON articles.aid = tags.aid WHERE tags.tag IN ("green", "pink", "black", "yellow") GROUP BY articles.aid;
+-----+-------------------------+-----------------+
| aid | headline | COUNT(tags.tid) |
+-----+-------------------------+-----------------+
| 1 | This is a green Article | 1 |
| 2 | This is another Test | 3 |
+-----+-------------------------+-----------------+
2 rows in set (0.00 sec)