[SOLVED] How to exclude BB code/HTML ode while searching

[abdfahim]

I have a mysql table where I store my forum's posts. And now i develop a search page for the forum. The problem is, I store the posts with existing (by poster) BB CODE in the table. For example two records in the table may look like

record 1 ==>  [font color=red]This is example 1[/font]
record 2 ==> This is not red text.

Now I use normal code for searching like ..
[code]$result=mysql_query("SELECT * FROM table_name WHERE column_name LIKE '%red%'");[/code]

The problem with it is that, if I search for "red", it results both the records. But I don't want record 1 to be included in the result. So is there any way to exclude those BB CODE (or HTML code) while using SELECT command?

[Jenk]

Have a second column with all formatting removed, purely for search purposes.

[abdfahim]

I m sorry folks, i have some language mistakes in the prev post. I just modify to correct those. Now, I think my question should be clear.

[HuggieBear]

Are you writing your own BB system or modifying an existing one?

If you're writing your own, then why not use PHP to veryify the input at the time of insert into the database?

You could use a regex to search for BBCode and then insert into a new column in the database named 'bbcode' either TRUE or FALSE.

Then when you search, just use...

[code]
$result=mysql_query("SELECT * FROM table_name WHERE bbcode = 'TRUE' AND column_name LIKE '%red%'");
[/code]

Regards
Rich

[Jenk]

My reply still applies. Create a second column with all formatting removed.

[HuggieBear]

Sorry, was just trying to avoid inserting almost identical data into two seperate columns.

Rich

[abdfahim]

Hi HuggieBear, there is a little problem in ur solution. Remember, search string (e.g. "red" i use here) is inputed by the visitor in the website. Now if there is a record which looks like

==>[font color=green]this is red[/font]

the search for "red" will not return this record in ur procedure because it contains BB code. So what I want is to search the string which is visible in forum page. (BB Code is not visible in forum page)

I think Jenk's solution is one which will work here. But, as HuggieBear said, I have to add an extra column for repeated data.

[HuggieBear]

[quote]
Hi HuggieBear, there is a little problem in ur solution. Remember, search string (e.g. "red" i use here) is inputed by the visitor in the website. Now if there is a record which looks like

==>[font color=green]this is red[/font]

the search for "red" will not return this record
[/quote]

How right you are... Good spot  :-X