MySQL search query

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.

[url removed, login to view]

[url removed, login to view]

[url removed, login to view]

[url removed, login to view]


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.


Compétences : Traitement de Données, PHP

en voir plus : mysql search query, mysql search queries, top searching, top query, search on top, searching for community, search in code, search here, n query, find someone to write a code, find and search in php code, data enters, code search, behalf, advanced search results, 4 tries, good mysql search query, advanced php mysql search query, php mysql specific search query, mysql search tag query

Concernant l'employeur :
( 12 commentaires ) 1169, Norway

Nº du projet : #326097

Décerné à:


Hello, i understand your project requirements and very interested in taking it. I have experience in using complex mysql queries and writing advanced php scripts. Ready to start development of this project for you rig Plus

%selectedBids___i_sum_sub_4% %project_currencyDetails_sign_sub_5% USD en 1 jour
(23 Commentaires)

5 freelance font une offre moyenne de $95 pour ce travail


VALUEONWEB is a customer-specific service oriented company has got a Professional and creative team. We are the Professional Web Development Company having rich experience in Web design and development. We have experti Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 2 jours
(211 Commentaires)

Hi i can help u instantly!

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 2 jours
(16 Commentaires)


%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 0 jours
(43 Commentaires)

Hi Sir! I'd like to do this for you! I'm a professional webprogrammer and I do have the required experience. Thank you

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% USD en 2 jours
(0 Commentaires)