List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 16 1999 2:30pm
Subject:Re: Help with MySQL Select Stmt
View as plain text  
On Wed, 1999-09-15 10:35:02 -0400, Jaymes H. Sorbel wrote:
> I have a question about MySQL select statements.  I have a web site
> that interfaces with a large quantity of records.  I want to do a
> keyword search.
> The results I want to order in the order of how many keywords were
> matched.  So if 3 keywords are entered and all three are found, it
> displays these first, then it displays the ones where less than 3
> were found.
> 
> I was trying to do two searches.  One with an AND - (field1 LIKE
> "%keyword1%" && field1 LIKE "%keyword2%").  Then I thought I would
> do the same type of SELECT using the OR function.  The problem is I
> get duplicates from the first select statement that I don't want.

I suggest to go a quite different way:
  Store the search words into a temporary table and do the search with
  a join!  This way it's easy to get the ranking for any number of
  search words.

Example:
   CREATE TABLE mytable (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      text VARCHAR(100)
   );
   INSERT INTO mytable(text) VALUES
    ('This is a test text')
   ,('Hot tests in the household')
   ,('House music is hot')
   ,('my house')
   ,('nothing here');
So this is my test data:
   SELECT * FROM mytable;
   +----+----------------------------+
   | id | text                       |
   +----+----------------------------+
   |  1 | This is a test text        |
   |  2 | Hot tests in the household |
   |  3 | House music is hot         |
   |  4 | my house                   |
   |  5 | nothing here               |
   +----+----------------------------+

Now let search for the words 'test', 'house', and 'hot'.

   CREATE TABLE searchterms ( pattern VARCHAR(30) );
   INSERT INTO searchterms VALUES ('%test%'),('%house%'),('%hot%');
So this is the table with search patterns:
   SELECT * FROM searchterms;
   +---------+
   | pattern |
   +---------+
   | %test%  |
   | %house% |
   | %hot%   |
   +---------+

Now it's easy to find all texts which match the patterns:

   SELECT * FROM mytable AS m, searchterms AS s
   WHERE m.text LIKE s.pattern;
   +----+----------------------------+---------+
   | id | text                       | pattern |
   +----+----------------------------+---------+
   |  1 | This is a test text        | %test%  |
   |  2 | Hot tests in the household | %test%  |
   |  2 | Hot tests in the household | %house% |
   |  3 | House music is hot         | %house% |
   |  4 | my house                   | %house% |
   |  2 | Hot tests in the household | %hot%   |
   |  3 | House music is hot         | %hot%   |
   +----+----------------------------+---------+

Using GROUP BY id and counting the rows of each groups, we also get a
rank number for sorting:

   SELECT m.*, COUNT(*) AS num
   FROM mytable AS m, searchterms AS s
   WHERE m.text LIKE s.pattern
   GROUP BY m.id
   ORDER BY num DESC;
   +----+----------------------------+-----+
   | id | text                       | num |
   +----+----------------------------+-----+
   |  2 | Hot tests in the household |   3 |
   |  3 | House music is hot         |   2 |
   |  1 | This is a test text        |   1 |
   |  4 | my house                   |   1 |
   +----+----------------------------+-----+

That's it! ;-)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Help with MySQL Select StmtLBFan991115 Sep
  • Re: Help with MySQL Select StmtChristian Mack15 Sep
  • Re: Help with MySQL Select StmtMartin Ramsch16 Sep