处理mysql中的特殊字符

Question

I have 2 mysql tables property and building. In property table i have a field called unitnumber the value in this field is like 1a, 2b etc.. In the building table i have a field called address which is in PHP's urlencoded format. The value will be something like 26+Dovedale+Place%2C+Parnell

Now i want to implement a search function so that when user types 1a, 26 Dovedale I need to pull up the result from the database.

Implemented So Far:

    $keyword = preg_replace('/[^a-zA-Z0-9-]/', '', $_REQUEST['keyword']);
    $keyword = str_replace(" ", "-", $keyword);
    $a->rqNotEmpty('keyword', $keyword);

    $query = "SELECT * FROM `buildings` b left join properties p on p.buildingId=b.id 
      WHERE concat(p.floor,p.number,b.address) LIKE '%".$keyword."%'
      or concat(p.number,p.floor,b.address)  LIKE '%".$keyword."'";

But now the problem is when i prepare the $keyword variable i will get something like 1a26Dovedale and in database after CONCAT the value will be somethign like 1a26+Dovedale+Place%2C+Parnell. I cant urlencode the $keyword because the database is not in standardized form. The database my have 2 spaces between the address. So is there a way to urldecode mysql string ?? I am dealing with nearly 4 million records here!!

Answer 1

You could try installing a MySQL UDF (User Defined Function) to provide the urlencode() and urldecode() functions within MySQL. Here's the link: http://freecode.com/projects/libmysqludf