database search

I have a little problem with the search in database
if for example if i have "Lust, Caution" and i search lust caution it didn't find anything...
and olso if is "Lust's Caution" or "Lust: Caution" or "Lust-Caution" it didnt' find anything...
is there a way to take out the "," , ":" , ";" , "-" from the search ???
i must mention that i use '%".$searchterm."%'

 

I believe what you are looking for is preg_match.

I haven't really played around with it that much yet, so might not be able to help you with how to use it, but here's an example.

$yourname = (some value from a textbox);
$string_exp = "#^[-A-Za-z\&\#0-9\;' .]*$#";
if(!preg_match($string_exp,$your_name)) {
$error_message .= 'Your Name does not appear to be valid.<br />';
}

But maybe someone else will be able to provide some better feedback on it.

You could also check out the documentation on it here:
http://php.net/manual/en/function.preg-match.php

 

If you want to change the format of the search value in your database, you can use the REPLACE('www.mysql.com', 'w', 'Ww') in MySql select query.

"SELECT * FROM `tablename` WHERE REPLACE(fieldcolumn,',',' ') = 'searchtext' "

Code (markup):

this will replace the ',' - comma with ' '-space.

 

Thank you guys, but i don't want to replace the , ; ' " - from the search, because these are not in the search. If i have in the database Lust, Caution and you search like this: Lust, Caution it will find it and display the result, but if you search Lust Caution it won't find you anything...

 

You are currently searching for the exact phrase hence the limited results you are getting. With this sort of thing you would normally break the search phrase into its constituant words, remove any common words like "in" or "a" and then perform the search with either OR or AND statements

 

I would use a loop and add an additional WHERE tablecell LIKE %".$searchterm."% OR .... OR .... to the query. This way it will build up a dynamic query and give you results if they contain Lust or Caution or both