CODEWITHSUNDEEP
mysqlfull-text-search
Strannik
Strannik

Reputation: 466

How use FULLTEXT SEARCH in mysql without special tags(<html>,<p> and other)

I make sarch in mysql with help next code:

SELECT * FROM `articles` WHERE MATCH (title,body) AGAINST ('database');

How use search in mysql without special tags(<html>,<p> and other)?

Upvotes: 2

Views: 3299

Answers (2)

Leon Armstrong
Leon Armstrong

Reputation: 1303

There is a php function can do this

strip_tags($text);

You can find more information here http://php.net/manual/en/function.strip-tags.php

If you still want to do this in mysql , you have to define your own function

delimiter ||
DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
return x;
END;
||
delimiter ;

Call the function in your search query after you define the function

$q="CALL strip_tags(SELECT textarea FROM table where ....)"

Upvotes: 4

user149341
user149341

Reputation:

Remove the HTML tags before you store text in the column which is FULLTEXT indexed. If you need the original text, store that in a different column.

Alternatively, use a real search indexer, like Lucene. MySQL FULLTEXT is kind of a toy.

Upvotes: 1

Related Questions