CODEWITHSUNDEEP
phpmysql
Amir Mustafa
Amir Mustafa

Reputation: 85

PHP/SQL - Ignore HTML tags when searching the database

I have a case here where I need to search by course summary which contains data with HTML tags.

Now the case here is I am writing SQL to search by summary. Summary column must contains HTML tags. how can I write the HTML ignoring the HTML tags.

Query to search:

$getids = $DB->get_records_sql("SELECT id FROM {course} WHERE (summary LIKE '%$searchcourse%' ) "

Way summary column in db: enter image description here

I followed a similar link but couldn't get much information.

Thank You

Upvotes: 0

Views: 375

Answers (1)

Muhammad Azizol Aminuddin
Muhammad Azizol Aminuddin

Reputation: 623

Not tried it myself. source: http://forums.mysql.com/read.php?52,177343,177985#msg-177985

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
    WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
      BEGIN
        SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
        SET iLength = ( iEnd - iStart) + 1;
        IF iLength > 0 THEN
          BEGIN
            SET Dirty = Insert( Dirty, iStart, iLength, '');
          END;
        END IF;
      END;
    END WHILE;
    RETURN Dirty;
END;

In your code

$getids = $DB->get_records_sql("SELECT id FROM {course} WHERE fnStripTags(summary) LIKE '%$searchcourse%'

Upvotes: 1

Related Questions