Project Detail

Description

I need you to construct a PHP script, that executes an advanced search-query on the database.

###

1. The user submits a search term into a form.
2. The script queries the database, and tries to find any matches based on this term.
3. The matches are then printed out on the screen.

###

The search-query is behaving on behalf of the table 'articles', but will not search within this table.

The query will only search for matches in these three tables:

(the columns in parenthese)

1. tags (tag)
2. messages (body)
3. comments (body)

(fulltext is enabled)

Each table has a column called article_id. This field links the rows to what article they belong to.

###

Table: tags
------------

This table contains tags that users submits to an article. It's an tagcloud, and each tag is duplicated when someone "votes" on it. Not normalized in other words.

ID - articleID - tag
--------------------
1 ---- 14 ----- Food
2 ---- 14 ----- Beef
3 ---- 14 ----- Beef
4 ---- 14 ----- Food
4 ---- 14 ----- Food
5 ---- 14 ----- Beer
6 ---- 14 ----- Food

Table: messages
----------------

This table contains messages that users submits to an article.

ID - articleID - body
--------------------
1 ---- 14 ----- Banana is a good option for add to ..
2 ---- 14 ----- Banana would probably add up to t ...
3 ---- 14 ----- Oranges is something I would put ...

Table: comments
-------------

This table contains comments to any messages added by users, by users.

ID - articleID - messageID - body
----------------------------------
1 ---- 14 ----- 1 ----- No way banana is not a go ...
2 ---- 14 ----- 2 ----- Yes what a good idea ...
3 ---- 14 ----- 2 ----- Bad idea ...

###

So, in order to give the users some relevancy when searching, there has to be some kind of logic to how the query handles the user submitted search term.

A tag is always very specific in our community, so it should count more than the other tables. Thus the main rule is (or something close too):

Table tags:
------------
This table counts 60%

Table messages:
---------------
This table counts 30%

Table comments:
---------------
This table counts 10%

= 100%
-------
-------

What I mean with this, is that if a search-term appears in the 'tags' table, it should give the article a higher score.

Example 1:

User enters: banana

article_id='30456': 2 times in tags, 1 time in messages, 4 times in comments
article_id='24556': 6 times in tags, 0 time in messages, 0 times in comments

Article 24556 should be on top, because it has the term repeated 6 times in the tag table.

--

Example 2:

User enters: banana

article_id='2401': 2 times in tags, 1 time in messages, 4 times in comments
article_id='64222': 0 times in tags, 25 time in messages, 12 times in comments

Here 64222 should be on top, because its apparent that since there are so many occations of this term in messages and comments, it should get a high score. ((( This is just an assumption, since I can't know if there is a mixture of loooots of other words and mysql gives it a low score )))

You get the point.

###

The output should be a table with 4 columns.

article.ID
article.body
article.url
article.score

###

I don't want results with 0 score.

###

This document is probably 10 times longer than the code you will write, but I have to be clear on what I get.

###