PHP strip_tags to MySQL search query

Question

I need to know how to apply strip_tags function to ignore search in tags located in post.post http://php.net/manual/en/function.strip-tags.php

$searchTerm = $db->real_escape_string($_GET['s']);
if(!empty($searchTerm)) {
    // Get search result algorithim to list by weight of title first, content second
    $q = $db->query("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%'
    )
    AND posts.status = 1
    ORDER BY weight DESC
    LIMIT 12");
    $r = $q->fetch_all(MYSQLI_ASSOC);
    $smarty->assign("posts", $r);
}

Answer 1

You can do that in sql by creating a separate function in my sql and using that in query. Please check the example below,

For Ex:

Custom Function `strip_tags()`

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 ;


SELECT strip_tags('<div style="font-size:12px">This is a body area</div>') as stripped_text;