CODEWITHSUNDEEP
phpmysqlsearch
user982853
user982853

Reputation: 2488

PHP MySQL - Search field with spaces

This may be something very easy. I am building a search tool. I have a varchar field that holds addresses. Example:

2343 S Cherry Creek St.

So i want to be able to search part or all of the field. I have tried

LIKE '%$address%'

but when the end use types in 

"2343 S Cherry"

It returns no results. When the user type in only one word it works:

"2343"

It returns the correct record. So this makes me think it is something about the spaces that are not allowing it to search the entire term.

Can someone tell me how to build this so that the end user can search the address field with more than one word. I know it has something to do with using LIKE, %% but I need it to allow spaces.

Upvotes: 1

Views: 3060

Answers (4)

Zane Bien
Zane Bien

Reputation: 23135

The issue isn't with spaces, but rather how you are inserting the user input via PHP.

Look at this SQLFiddle Demo.

Perhaps the problem is that it seems you are allowing users to enter quotes into the input so they can perform searches on entire strings.

What you want to do is eliminate the quotes in PHP, then insert your input into the SQL statement or as a bound parameter:

$address = strtr($address, array('"' => '', "'" => ''));

This will remove both single and double-quotes from your string.

Upvotes: 1

user982853
user982853

Reputation: 2488

I had an error in my import. So the address that was being imported into the db was adding and extra space. So when people search with one space between words it was returning 0 results because the data in the db had two spaces. I have fixed my import script and it is now working. Thank you.

Upvotes: 1

Mike Mackintosh
Mike Mackintosh

Reputation: 14245

I would suggest the following

SELECT * FROM table WHERE REPLACE(address, ' ', '') LIKE REPLACE('%$address%', ' ', '');

This will replace spaces with non-spaces so you can match closer and easier.

Upvotes: 0

Kir
Kir

Reputation: 694

Try this

$arr = explode(' ', $address);
if (count($arr)) {
    $query = "SELECT address FROM table WHERE address LIKE '%" . implode( "%' OR address LIKE '%", $arr) . "%' ";
}

Upvotes: 0

Related Questions