Ignore HTML tags in saved data during MySQL search
I searched too much for this and I found a couple of results, but none of them worked. I am using MySQL query to find headers and content in my posts table. My problem is that the data is being saved using TinyMCE which contains HTML tags. I want my search query to ignore HTML tags so as not to return any irrelevant results. My request:
SELECT posts.id, posts.title, posts.date, posts.tags, posts.image, users.name, users.screen_name, users.id as user_id,
IF(
posts.title LIKE '$searchTerm%', 20,
IF(posts.title LIKE '%$searchTerm%', 10, 0)
)
+ IF(posts.post LIKE '%$searchTerm%', 5, 0)
AS weight
FROM posts
LEFT JOIN users ON posts.user_id=users.id
WHERE (
posts.title LIKE '%$searchTerm%'
OR posts.post LIKE '%$searchTerm%'
)
ORDER BY weight DESC
LIMIT 100
+3
source to share
1 answer
This function can do php function
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 must 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 ;
Calling a function in a search query after defining a function
$q="CALL strip_tags(SELECT textarea FROM table where ....)"
0
user1556571
source
to share