Mysql query LIKE search and using strings with sym

问题:

My problem is having symbols used in the search query. I want users to be able to use symbols without being a problem, but the LIKE function in mysql seems to not be the solution so I need some help.

EXAMPLE: If someone searches for "Blue's car" and "Blues car" is in the database, this query will return with 0 results. OR viseversa, if someone searches for "Blues car" and "Blue's car" is in the database, this query will return with 0 results as well.

This is an example of what I'm currently using:

("SELECT Title FROM MyData WHERE Title LIKE '%".$search."%'")

Is there another way on providing better search results?

Thanks.

回答1:

Search Field

You could have an additional "search" field, that stores the title without punctuation and when user enters search string, strip out punctuation before applying query. You could also remove leading "The".

I'll further explain the above.

In the database you have a record like this. The SearchTitle is the Title with the leading "The" and punctuation removed:

Title                  SearchTitle
------------------------------------------
The Blue's Clues       Blues Clues

Here, the user knows the exact title and enters the following search string:

The Blue's Clues

You strip out the leading "The" and the punctuation, yielding the following:

Blues Clues

Your query looks like this

SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

Here, the user doesn't know the exact title and enters the following:

Blues Clues

Stripping the leading "The" and the punctuation, still yields the same thing:

Blues Clues

Your query stays the same, and matches what's in the search field:

SELECT Title FROM MyData WHERE SearchTitle LIKE '%Blues Clues%'

This can be further improved. The key is to apply the same rules to the search string as to what's stored in the search field. For example, convert "two" and "II", to "2", etc., remove additional words like "ON" and "AND", etc.

Full-Text Searches

MySQL FullText searches use "Natural Language" searches. I'm not sure if this would do the trick in your case, but it might be worth researching.

回答2:

I wouldn't say this is necessarily better but MySQL has Regular Expression support. REGEXP

回答3:

Passing in any user input directly to a SQL call leaves your code potentially vulnerable to SQL injection attacks, which is a security risk, as well as what you've found that it doesn't always provide the expected result. To fix this problem, developers are advised to use prepared statements or escape the inputs [sometimes called sanitizing the input]. See the following for some more information: What's the best method for sanitizing user input with PHP?

回答4:

You can you this search term. This is very useful for multi column search from MySQL

$q = htmlspecialchars($_GET["q"], ENT_QUOTES, "ISO-8859-1");
$q = stripslashes($q);
$search_exploded = explode (" ", $q);
$construct = "";

  $construct .=" CONCAT(title,' ',category,' ',subCategory,' ',description,' ',contact,' ',email,' ',website,' ',address) LIKE '%$q%' ";

foreach($search_exploded as $search_each)
{

$construct .=" OR CONCAT(title,' ',category,' ',subCategory,' ',description,' ',contact,' ',email,' ',website,' ',address) LIKE '%$search_each%' "; 

}  
$constructs ="SELECT * FROM content WHERE   active='1' AND ($construct) ";
$run = mysql_query($constructs);
$foundnum = mysql_num_rows($run);

If you want i will provide more information