php/mysql: Custom site search

First of all: - I can't use sphinx because i use share hosting - I don't like google solutions ie. custom search have these stupid ad and site search isn't free

I want to crea开发者_运维百科te search mechanizm on my own. I have pages table and i want to search pages content by keywords and on result page i want to show part of text which is matched with desired keywords (same like google does).

Thx in advanced

Then you have two ( and a half ) choices:

• use MyISAM engine for the data you want to search
• write your own indexing-thing
• change hosting or change DBMS ( the 1/2 solution )

Here is short version of how you can do the 2nd option:

Lets say you want to search the content of your articles. Basically what you have to to is create an index of all the words you might want to search for.

Code below taken from book SQL Antipatterns and modified only tiny bit.

I will assume that you want to index articles:

CREATE TABLE Articles(
   article_id INT AUTO_INCREMENT,
   title VARCHAR(120),
   content TEXT,
   PRIMARY KEY ( article_id )
);

You need a table for keywords ( each keyword can be in multiple articles ):

CREATE TABLE Keywords(
   keyword_id INT AUTO_INCREMENT,
   keyword VARCHAR(40) UNIQUE NOT NULL,
   PRIMARY KEY ( keyword_id )
);

Now the table to implement many-to-many relationship:

CREATE TABLE ArticlesKeywords(
   keyword_id INT,
   article_id INT,
   PRIMARY KEY ( keyword_id , article_id ),
   FOREIGN KEY ( keyword_id ) REFERENCES Keywords( keyword_id ),
   FOREIGN KEY ( article_id ) REFERENCES Articles( article_id )
);

And next you create a stored procedure, which populates your indexing mechanism:

CREATE PROCEDURE ArticlesSearch(keyword VARCHAR(40))
BEGIN
   SET @keyword = keyword;
   PREPARE s1 FROM 'SELECT MAX(keyword_id) INTO @k FROM Keywords
      WHERE keyword = ?';
   EXECUTE s1 USING @keyword;
   DEALLOCATE PREPARE s1;
   IF (@k IS NULL) THEN

      PREPARE s2 FROM 'INSERT INTO Keywords (keyword) VALUES (?)';
      EXECUTE s2 USING @keyword;
      DEALLOCATE PREPARE s2;

      SELECT LAST_INSERT_ID() INTO @k;

      PREPARE s3 FROM 'INSERT INTO ArticlesKeywords (article_id, keyword_id)
         SELECT article_id, ? FROM Articles
         WHERE title REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>:]]'')
            OR content REGEXP CONCAT(''[[:<:]]'', ?, ''[[:>]]'')';
      EXECUTE s3 USING @k, @keyword, @keyword;
      DEALLOCATE PREPARE s3;

   END IF;

   PREPARE s4 FROM 'SELECT b.*FROM Articles b
      JOIN ArticlesKeywords k USING (article_id)
      WHERE k.keyword_id = ?';
   EXECUTE s4 USING @k;
   DEALLOCATE PREPARE s4;
END

Now you can use this procedure to search the index for keyword.

CALL ArticlesSearch('OMG');

The last part of solution is making sure that each new articles is automatically indexed:

CREATE TRIGGER Articles_Insert AFTER INSERT ON Articles
FOR EACH ROW
BEGIN
   INSERT INTO ArticlesKeywords (article_id, keyword_id)
      SELECT NEW.article_id, k.keyword_id FROM Keywords k
      WHERE NEW.content REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]')
         OR NEW.title REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]');
END

.

P.S. i have never needed to test this approach, that's why i cannot guarantee that it will work.