Jump to content


Photo

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


  • Please log in to reply
7 replies to this topic

#1 abdfahim

abdfahim
  • Members
  • PipPipPip
  • Abd Fahim
  • 535 posts

Posted 25 August 2006 - 12:48 PM

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 ..
$result=mysql_query("SELECT * FROM table_name WHERE column_name LIKE '%red%'");

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?

#2 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 25 August 2006 - 01:48 PM

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

#3 abdfahim

abdfahim
  • Members
  • PipPipPip
  • Abd Fahim
  • 535 posts

Posted 25 August 2006 - 01:51 PM

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.

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 August 2006 - 01:58 PM

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...

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

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 25 August 2006 - 01:59 PM

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

#6 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 August 2006 - 02:04 PM

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

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#7 abdfahim

abdfahim
  • Members
  • PipPipPip
  • Abd Fahim
  • 535 posts

Posted 26 August 2006 - 04:33 AM

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.

#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 29 August 2006 - 11:39 AM

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


How right you are... Good spot  :-X
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users