php/mysql:自定义站点搜索 [英] 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

我想自己创建搜索机制.我有页面表，我想按关键字搜索页面内容，在结果页面上，我想显示与所需关键字匹配的部分文本(与google一样).

I want to create 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进阶

推荐答案

然后，您有两个(半个)选择:

Then you have two ( and a half ) choices:

• 使用MyISAM引擎获取要搜索的数据
• 编写自己的索引内容
• 更改托管或更改DBMS(1/2解决方案)

这是您如何执行第二个选项的简短版本:

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.

以下代码摘自本书 SQL反模式 只是一点点.

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.我从来不需要测试这种方法，这就是为什么我不能保证它会起作用的原因.

这篇关于php/mysql:自定义站点搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！