CODEWITHSUNDEEP
phphtmlmysqltags
webbed
webbed

Reputation: 31

How to remove html tags except <br> from mysql data?

I have a field Product_description in mysql table, which has html tags. I want to update this field with removing html tags except br. I know I can do this with strip_tag but I don't understand how to accomplish this for the table data.

Upvotes: 2

Views: 12749

Answers (3)

Bhargav
Bhargav

Reputation: 586

Create function using below code :

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;
|
DELIMITER ;

And execute below query :

SELECT fnStripTags('this <html>is <b>a test</b>, nothing more</html>');

Upvotes: 4

FirstOne
FirstOne

Reputation: 6217

To remove all tags but <br> from a text, you can indeed use strip_tags:

$var = strip_tags($html, '<br>');

See an example here.

From the docs:

You can use the optional second parameter to specify tags which should not be stripped.

To remove them from the persisted data (not meant to run multiple times), you can create a heavy running script to be used once and update the values. Like this: (You might want to use trim too)

$con = new mysqli('localhost', 'username', 'password', 'database_name');
$stmt = $con->query('SELECT * FROM table_name');
while($row = $stmt->fetch_assoc()){
    $stmt2 = $con->prepare('UPDATE table_name set Product_description = ? WHERE Product_id = ?');
    $tmp = strip_tags($row['Product_description'], '<br>');
    $stmt2->bind_param("si", $tmp, $row['Product_id']);
    $stmt2->execute();
}

That script would be for you to run it just once, so it's (arguably) ok if it's slow...

Upvotes: 8

Homam Alhaytham
Homam Alhaytham

Reputation: 115

for just one tag you can do this query

UPDATE `TABLE` SET column1 = REPLACE(column1, '<br>', '');

or make function for strip all html tags like this example

CREATE FUNCTION `strip_tags`($str text) RETURNS text
BEGIN
    DECLARE $start, $end INT DEFAULT 1;
    LOOP
        SET $start = LOCATE("<", $str, $start);
        IF (!$start) THEN RETURN $str; END IF;
        SET $end = LOCATE(">", $str, $start);
        IF (!$end) THEN SET $end = $start; END IF;
        SET $str = INSERT($str, $start, $end - $start + 1, "");
    END LOOP;
END;

this function found from

Upvotes: 2

Related Questions