Ignoring spaces

Germaris

Hi there!

I've built a search that works but relies on the users to input their request very accurately.

The data they're searching on are phone numbers which come from a MySQL DB.

In the DB, phone numbers (from all over the world) are stored WITH spaces, like for example: 123 456 7890

What happens is people are typing in "1234567890" and this is coming up with no result.
This also occurs if users type in: "123 4567890"...
May be it would be also necessary to eliminate all spaces in the user's request prior to execute the query...
How should I write my queries to make them work?

Due to the infinite power of PHP, I'm quite sure a simple solution exists.
The script could first transform the user's string in a string with no spaces then compares this new string against those contained in the DB ignoring while reading the existing spaces...
This method avoid modification of the DB...

Any help would be most appreciated and I thank you in advance for it.

dougal85

could you not just re-format the search query?

ie. remove all spaces then add spaces to match the numbers in the DB - or are they not all the same format in the db?

roscor

 if(preg_match('/^[0-9_ -]{11,}$/i', $_POST['phone'])){
        $phone = $_POST['phone'];

This will validate an 11 digit uk telephone Number(any input under 11 digits will fail).
The validation is numerical 0-9 and the space after the _(underscore) allows spaces.

Germaris

Thanks to both of you for your replies.

I think we can get rid of all the different phone formats.

There's two steps to make this search
1 - Eliminate the spaces in the user's string

$newSearchText = str_replace(' ', '', $searchText);

2 - Write the MySQl query in a way it ignores all the spaces found in the field containing the phone numbers. Doing this allows to compare strings which does not contains spaces at all...
Something like:

SELECT REPLACE(field_name,' ','') ...

But I don't know the correct syntax.

What do you think?

dougal85

That would certainly work - However, I'm not sure how you would remove the spaces in your query.

maybe with regular expressions; http://dev.mysql.com/doc/refman/4.1/en/regexp.html

Germaris

Thanks for the URL.
Maybe I'll find a solution there...

Did you read my last posting? I edited it after your reply, I think...

dougal85

I see it now, I am not familiar with the REPLACE function; however after looking it up (http://dev.mysql.com/doc/refman/4.1/en/replace.html) I'm not sure thats what you want. You really want a replace very much like PHP's i guess.

Germaris

Yes, indeed.

NogDog

I believe the best solution would be to update your database, removing all spaces and/or punctuation from the phone numbers. Then all you would need to do is clean up the search values with something like:

$phoneSearch = preg_replace('/\D/', '', $phoneSearch);

If modifying the phone numbers in the DB is not an option, then filter the $phoneSearch value as above, and use MySQL's replace() function something like:

$sql = "SELECT * FROM `table` WHERE REPLACE(`phone`, ' ', '') LIKE '%$phoneSearch%'";

Germaris

Hi !

Sorry to be so late!

I didn't change my DB because I need the phone numbers to stay stored in their original format for displaying them when needed by other kind of searches through the directory.

So, I used the following code which works fine and suits my needs:

$new_sphone = str_replace(' ', '', $sphone);
$query = mysql_query( "SELECT * FROM $table WHERE $new_sphone=REPLACE(phone,' ','')" ) or die ( "select error " . mysql_error() );
if ( mysql_num_rows( $query ) > 0 ) 
while ( $row = mysql_fetch_array( $query ) ) {
print "&error=message_explaining_the_results"."&main=".$row["phone"]."&secondary=".$row["phoneTwo"]."&cellular=".$row["mobile"]."&facsimile=".$row["fax"]; 
}
else {
print "&error=message_explaining_the_results";
}

I want to thank you very much for your appreciated help!
Best regards.

Gerard