How would you make a smarter Search?
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]
Re: How would you make a smarter Search?
I am interested in this too... Dan if you figure out a way I would be most interested... On Fri, 8 Oct 2004 14:50:49 -0400 (EDT), Dan Venturini [EMAIL PROTECTED] 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]
RE: How would you make a smarter Search?
Dan Venturini mailto:[EMAIL PROTECTED]
on Friday, October 08, 2004 11:51 AM said:
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.
[snip]
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.
well i think the principal is that you need to search for each word
individually, grouping them with AND. i had the same question but never
got around to working on it so i did a little investigation but came up
dry (so far).
i thought an easy way to do it would be to use the IN() function:
SELECT name
FROM products
WHERE name IN ('cleaning', 'computer')
but this doesn't work as it's looking for a name with exactly 'cleaning'
or exactly 'computer'. so i tried adding LIKE before the IN, but that's
just plain invalid. then i tried wrapping each item with % but although
it doesn't throw an error, that doesn't work either.
the only other thing i can think of (not that a better answer is not out
there of course) is to create a statement like the following:
SELECT name
FROM products
WHERE name LIKE '%cleaning%'
AND name LIKE '%computer%'
report back to the list if you find out anything else, or if anyone
would like to chime in and answer this.
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How would you make a smarter Search?
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]
RE: How would you make a smarter Search?
Have you considered creating a full text index on that field?
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Chris W. Parker [EMAIL PROTECTED] wrote on 10/08/2004 04:11:07 PM:
Dan Venturini mailto:[EMAIL PROTECTED]
on Friday, October 08, 2004 11:51 AM said:
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.
[snip]
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.
well i think the principal is that you need to search for each word
individually, grouping them with AND. i had the same question but never
got around to working on it so i did a little investigation but came up
dry (so far).
i thought an easy way to do it would be to use the IN() function:
SELECT name
FROM products
WHERE name IN ('cleaning', 'computer')
but this doesn't work as it's looking for a name with exactly 'cleaning'
or exactly 'computer'. so i tried adding LIKE before the IN, but that's
just plain invalid. then i tried wrapping each item with % but although
it doesn't throw an error, that doesn't work either.
the only other thing i can think of (not that a better answer is not out
there of course) is to create a statement like the following:
SELECT name
FROM products
WHERE name LIKE '%cleaning%'
AND name LIKE '%computer%'
report back to the list if you find out anything else, or if anyone
would like to chime in and answer this.
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
