PHP MySQL - Search field with spaces

Question

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.

I simply cannot believe that LIKE %% does not work for you! — verisimilitude
– verisimilitude, Commented Jul 28, 2012 at 16:01

user982853 · Accepted Answer · 2012-07-28 16:14:49Z

1

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.

answered Jul 28, 2012 at 16:14

user982853

2,44815 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zane Bien · Accepted Answer · 2012-07-28 17:34:15Z

1

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.

edited Jul 28, 2012 at 17:34

answered Jul 28, 2012 at 17:28

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Comments

Kir · Accepted Answer · 2012-07-28 16:02:17Z

0

Try this

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

answered Jul 28, 2012 at 16:02

Kir

6944 silver badges7 bronze badges

Comments

Mike Mackintosh · Accepted Answer · 2012-07-28 16:04:45Z

0

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.

answered Jul 28, 2012 at 16:04

Mike Mackintosh

14.3k7 gold badges64 silver badges87 bronze badges

Collectives™ on Stack Overflow

PHP MySQL - Search field with spaces

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related