Project Detail

MySQL search query  

MySQL search query is project number 326097
posted at Freelancer.com. Click here to post your own project.

 

| More Free Trial For New Buyers
 

Status:

Selected Providers: UmidKZ

Budget: $30-250

Created: 10/08/2008 at 12:25 EDT

Bid Count: 5

Average Bid:
$ 95

10/09/2008 at 12:25 EDT

Project Creator: gladideg
Employer Rating: 10/1010/1010/1010/1010/1010/1010/1010/1010/1010/10 (12 reviews)

Bid On This Project
 

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.

###

Messages Posted:0 View project clarification board Post message on project clarification board

Bid On This Project
 

If you are the project creator or one of the bidders Log In for more options

 

125

2 days

10-08-2008 12:51 EDT

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 expertise and experience in ecommerce site development, flash and animation. Our team can work on HTML,DHTML, JAVA SCRIPT, FLASH , ASP,ASP.NET,PHP,MY SQL, SQL Server, MS SQL. Our Bid includes development , deployment and testing. We are ready to start and send daily reports. we communicate through mail,messenger,phone and skype.We will a deliver a professional looking design(s) with proposed functionality to match your concept.

help

 

50

0 days

10-08-2008 12:50 EDT

A VERY IMPORTANT OFFER IS WAITING FOR YOU IN YOUR PM!

help

 

100

2 days

10-08-2008 12:37 EDT

Hi i can help u instantly!

help

 

50

1 day

10-08-2008 12:35 EDT

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 right now.

help

 

150

2 days

10-08-2008 12:31 EDT

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

help


    Bid on this Project