MySQL Search (using LIKE)

[immanuelx2]

Hey guys.

 

I am building a PHP/MySQL search feature that is quite simple. A user inputs keyword(s) and the MySQL finds the terms in a body of text.

 

However, if that body of text contains an URL (bbcode [ur=http://www.blahblahblah.com]Blah[/url]) it will actually search through the URL and return a part of that. Is there some way I can get the MySQL to ignore anything inside [] brackets?

 

Thanks in advance.

[Maq]

You can use a regular expression to filter that out - REGEX.

[immanuelx2]

Thanks for the link.

 

I am very new to REGEX especially with MySQL. Could you show me an example query I could derive from?

[warhead2020]

try this link..

 

 

http://www.phpro.org/tutorials/Introduction-to-PHP-Regex.html

http://www.regular-expressions.info/php.html

http://www.php.net/manual/en/ref.regex.php

 

hope that links helps u..

[gizmola]

Is your database ever going to be large?  Are you aware that when you do LIKE '%SOMETHING%' MySQL will table scan?  Using Regex functions to accomplish the same thing will also table scan.  It's just not a great approach for full text searching.  MySQL does have the Fulltext index type. 

 

My suggestion would be to use the fulltext index type, and as you fetch each row in the result:

 

1. Make a copy of the data column(s) variables, use preg_replace to remove any URL's from the copy of the data.

 

2.  Use strpos on the url eliminated data, and if you get a match on any of the words in the search phrase, return the row, otherwise exclude it.  If you do match, you want to return the original data, so that you don't improperly filter out url's.