MySQL fuzzy search like implementation tutorial

Under MySQL, in the Chinese fuzzy search, often return some unrelated
Records, such as looking for "%a%", may return a Chinese character, but no a characters exist.
I have encountered similar problems before, after a detailed reading of MySQL Manual, found that you can
There is a convenient way to solve the problem and get satisfactory results.

Example:
• You want to retrieve the news library by "title", which may contain both Chinese and English, the following SQL statement:
Select Id,title,name from achech_com.news where title like '%a% '
Returns, some title fields are determined with the "a" keyword, while others have only Chinese, but are returned in the search results.

Workaround, using the BINARY property for retrieval, such as:
Select Id,title,name from achech_com.news where binary title like '%a% '
The returned results are more correct than before, but the English letters are case-sensitive, so there are times when the results of retrieval such as "Achech" and "Achech" are different.

Know the use of the BINARY property can solve the problem before, and then look at the MySQL support
UCASE and CONCAT functions, in which UCASE is converted into uppercase letters and CONCAT letters
The function of a number is to connect the characters, and here are the SQL statements that we have completely resolved:
Select Id,title,name from achech_com.news where binary UCase (title) like Concat ('% ', UCase (' a '), '% ')
The retrieval step is to specify the attribute as BINARY to accurately retrieve the result, and the title content is like the possibility of uppercase and lowercase letters, so first use the UCase function to convert the contents of the field into uppercase letters, and then do like operations, and like operations using the Fuzzy method, using C Oncat's advantage is that it can be a direct keyword, do not need to take the "%" million, the "' A '" directly into your variables, in any language everything is all right.

Of course, you can also write this:
Select Id,title,name from achech_com.news where binary UCase (title) Like UCase ('%a% ')
Let's take a look at an implementation here.

<?php
Require_once ("inc/conn.php");
Require_once ("inc/function.php");
$name = get_value (' name ', 0);

if (Empty ($name) | | | strlen ($name) >20)
{
ShowMsg (' result ': ' false ');
}
Else
{
$sql = "Select Id,user_name from aduse where binary ucase (user_name) like UCase ('% $name% ')";

$result = mysql_query ($sql) or Die (Mysql_error ());
if (mysql_num_rows ($result))
{
while ($rs = Mysql_fetch_array ($result))
{
$temp. = "<member memberid=" "$rs [0]." "Name=". $rs [1]. "" Url= "searchmember.php?id=". $rs [0]. ""/> n ";
}
echo "<?xml version=" 1.0 "encoding=" gb2312 "?> n<channel> n", $temp, "</channel>";
}
Else
{
ShowMsg (' result ': "NotFound");
}
}
?>