Hello...
here you have, in my opinion, a nice solution. To see what kind of search options you
can
use, visit my site, click on "Search Tips" on the Internal Search Engine.
Here is the query:
select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE) as score
from some_table where match(column1) against('+orange -fruit' IN BOOLEAN MODE)
order by score desc
This way you have your search results ordered by relevance, and you also get the
relevance value in
the result if you want to.
- If you use one word in your search, 1 is a probable score, because all the results
that
appear have the same relevance (they all contain that word!).
- If you use two words, where the second isn't present in all results, you shouldn't
get
relevance value 1 in all results, bacause some entries are more relevant than others
- If you use the example query (+orange -fruit) it's also natural that the relevance
value is 1,
because it's a very strict query.
With this simple method, MySQL takes care of everything that a basic search engine
needs.
Note that searching for words with 3 letters or less will not produce any result. You
could
take a look that the MySQL Manual for furher information (Match... Against).
Note that if you are using, say PHP, you should put '$search_string' in the place of
'+orange
-fruit', where $search_string is the search string the user inserted in the textfield
to perform
the search. It is important that you do NOT OMIT the ' '.
TIP. You should be able to perform this search on various columns at the same time, as
long as they belong to the SAME TABLE. This way, searching title, description, etc.
Again,
take a look at the MySQL Manual.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED]
URL: http://www.iMikalsen.com
On 8 Oct 2004 at 14:50, Dan Venturini wrote:
> Hello all Here is my problem.
> I am searching titles in an article database.
> I have two titles:
>
> mouse cleaning
> and
> cleaning your computer
>
> Now If I do a search for "cleaning mouse" I get 0 results. If I do
> "cleaning computer' I get 0 results. But If I do mouse cleaning, mouse,
> cleaning your,I get the articles.
>
> This is my query from a simple form input. The form value is called
> "search_value"
>
> $query = "SELECT id,title, post, DATE_FORMAT(date,'%M %D, %Y') AS date
> FROM article WHERE title LIKE '%". $search_value ."%' ORDER BY id DESC
> LIMIT $offset, $limit";
>
> My question is am I doing something wrong here? Do you have anytips on
> making a smart search work? This is the only way I was taught where you
> match the user input to something in the database.
>
> Thanks in advance.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
