mysql 选择关键字周围的内容 [英] mysql select content around keyword

问题描述

我正在尝试创建带有关键字突出显示的搜索表单，但显然，如果关键字没有出现在列表结果的前几句中，我将无法突出显示该关键字 - 所以我需要创建一个 sql 语句，它将填充找到的关键字左侧的字符数，右侧的字符数相同.

I'm trying to create the search form with keyword highlighting, but obviously I won't be able to highlight the keyword if it doesn't appear in the first few sentences of the result on the list - so I need to create a sql statement, which will populate number of characters of to the left of the found keyword and the same to the right.

有人知道这样的 SQL 语句是什么吗?

Anyone knows what the SQL statement would be for something like this?

好的 - 澄清一下 - 想象以下 SQL 语句:

Ok - to clarify - imagine the following SQL statement:

SELECT * 
FROM `articles`
WHERE `content` LIKE '%keyword%'

现在 - 此语句将返回与 WHERE 子句匹配的结果列表.

Now - this statement will return a list of results which match the WHERE clause.

接下来我要做的是使用以下方法 (PHP) 将结果集中的所有关键字与 span 一起包装起来，以突出显示关键字:

What I'm doing next is I'm wrapping all keywords withing the result set with the span to highlight the keyword using the following method (PHP):

public static function highlight($string = null, $keyword = null, $class = 'highlight') {
     return str_ireplace($keyword, "<span class=\"{$class}\">{$keyword}</span>", $string);
}

现在这可以正常工作，但每个结果只显示每个结果的开头 X 个字符.

Now this will work fine, but the each result only displays the beginning X number of characters from each result.

我想要实现的是首先识别内容中的关键字，然后从该关键字的左侧和右侧抓取一些内容，以便该关键字始终出现在每个结果中.

What I'm trying to achieve is to first identify the keyword within the content, then grab some content from the left and right of this keyword, so that keyword is always presented in each of the results.

我希望这是有道理的.

推荐答案

好的 - 我想我已经想通了 - 这是我想出的:

Ok - I think I've figured it out - here is what I've come up with:

SELECT
IF (
    LOCATE('keyword', `content`) < 20,
    SUBSTRING(`content`, 1, 200),
    SUBSTRING(`content`, LOCATE('keyword', `content`) - 20, 200)
) AS `content`
FROM `table`
WHERE `content` LIKE '%keyword%'

首先确定关键字的位置，然后如果它的索引小于 20，我只是从头开始获取内容，如果它发生得更远，然后是第 20 个索引，那么我会抓取它的索引最小 20 个字符，再到 200 个字符- 它有效！

First identifying the position of the keyword, then if it's index is less than 20 I simply get the content from the beginning, if it's occuring further then 20th index then I grab it's index minux 20 characters and down to 200 characters all together - and it works!

谢谢尼克 - 你的回答真的很有帮助.

Thanks Nick - your answer was really helpful.

只剩下一件事 - 我如何从内容字段中删除 html 标签以便将它们删除 - 也许使用 REGEXP - 任何人都知道如何?

Only one thing left - how can I strip html tags from the content field so that they are removed - perhaps using REGEXP - anyone knows how?

这篇关于mysql 选择关键字周围的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！