Strip HTML before Search

aksival

A database table contains many TEXT rows filled with HTML content (for posting articles, blog entries, etc.) What's the best way (or is there a way) to search for a specific term using a MySQL query, while ignoring HTML tags in the data.

Example:

$database_row = "<h1>This is a header</h1> <p>This is a paragraph</p>";

A search for the term 'h1' or even '<h1>' will return this row because the search term happens to be an HTML tag within the data. This is not the desired result.

I'm not positive, but I am under the impression that the results may have to be further parsed by PHP to verify that the rows returned do match after filtering out the HTML. It seems as though there would be some functionality built into the database to accommodate something like this...perhaps something to do with regular expressions.

Any thoughts?

bastien

with the data already having the html tags you will need to use regexp in the sql to skip the html

aksival

Yeah, I was afraid of that (I'm a bit weak when it comes to regular expressions).

I'll play with it and see what I can come up with. The link looks like it will be a great help -- I honestly didn't know that MySQL had built in REGEXP functions.

This will be fun...

Thanks again.

suntra

The way I work usually is...

Beginning...everything except <..."<"

">"...everything except ">"...end

">"...everything except "<"..."<"

So "everything ..." could be :
everything except "<" (or ">")...word...everything except "<" (or ">")

suntra

I would suggest something different... why don't you create a new field which would contain the page WITHOUT HTML tags ? This way you wouldn't have to use REGEX and that would speed up your query time...

MarkR

I agree with suntra.

Create another TEXT field which will contain the same content minus HTML tags.
Whenever you insert or update this table, put in the content without HTML
Fulltext indexes can then be placed on this field (and/or other ones as desired)
You can now do a highly efficient MATCH fulltext index query which will perform well on a table with millions of rows (unlike LIKE, which is hopeless)

Mark

aksival

suntra, MarkR --

I hadn't actually thought of doing this, although the flexibility and speed of the search would be increased greatly. The obvious drawback here is that your are nearly doubling the amount of data you put into the database.

The only question I have would be is this a standard/good practice?

MarkR

aksival wrote:
suntra, MarkR
...
The obvious drawback here is that your are nearly doubling the amount of data you put into the database.

It would certainly increase storage requirements. If your database is sufficiently large that you care, you should mention this to your DBA / sysadmin in the design phase of your project.

If your database is going to be mickey-mouse (e.g. < 100M) then you don't care anyway.

Mark

aksival

Understandable. It's not going to be that large by any means, I was just thinking of handling similar issues in future projects.

Thanks, I'll try it out 🙂

Best,
aksival