Fix for the Phone Number Search bug

[kbrill]

This module fixes a LONG standing bug in Sugar that if your users enter a phone number like this "314.968.9800" and you search for it like this "968-9800" you will not find it. This patch makes it so that you can enter phone numbers anyway you want and search for them any way you want. 968-9800 will match 968.9800 or (314)-968-9800 or 968 9800.....

It also allows for my asterisk phone system to bring up a record by phone number no matter how it is formatted. Asterisk always formats it numbers like 3149689800 which under the old system matched nothing. Now it matches up with (314)-968-9800 or whatever is in the tables....

It is available here.

[Proware]

Wow - this is great!

Why doesn't Sugar jump on patches like these and incorporate them into their product?

[Proware]

This module fixes a LONG standing bug in Sugar that if your users enter a phone number like this "314.968.9800" and you search for it like this "968-9800" you will not find it. This patch makes it so that you can enter phone numbers anyway you want and search for them any way you want. 968-9800 will match 968.9800 or (314)-968-9800 or 968 9800.....

It also allows for my asterisk phone system to bring up a record by phone number no matter how it is formatted. Asterisk always formats it numbers like 3149689800 which under the old system matched nothing. Now it matches up with (314)-968-9800 or whatever is in the tables....

It is available here.

Hi Ken,

The fix works great for the general search but doesnot seem to impact the Contact / Account search. Do you have a patch for these?

[kbrill]

Hi Ken,

The fix works great for the general search but doesnot seem to impact the Contact / Account search. Do you have a patch for these?

I am working on a complete rewrite of all Sugar search routines. Date ranges, phone numbers, soundex, a google like unified search, the whole 9 yards. Not sure I can go all the way back to 4.2.0 though.

[alinoz]

I sugest to search using regular expresions in state of 'LIKE', this is an example of how i'm doing this:

 if(strpos($field, "phone") === false)
{
    $clause .=  "LIKE '{$_REQUEST['query_string']}%'";
}
else
{
   $temp_str = $_REQUEST['query_string'];
   $temp_str = ereg_replace( '[^0-9]+', '', $temp_str );
   $temp_clause = '"';
   for($i=0;$i < strlen($temp_str)-1; $i++)
   {
      $temp_clause .= $temp_str[$i] . "[- ./]?";
   }
   $temp_clause .= $temp_str[$i];
   $temp_clause .= '*"';
   $clause .="REGEXP " . $temp_clause;
} 
  

this will return a more accurate result.
In the way the search is implemented in your fix if you have this numbers: 314 233 6540 and 314 323 36540 in the database and if you search for 314 233 6540 then the both number will be returned as result.

[DJuser]

this sounds like it'll cause very slow searches?

Or have I missed something obvious here?

Running regexp on the phone number for *every* record in the table? Slowwwww.. can't benefit form indexing on the phone field...

Better would be to have an extra field in the table - to save a 'cleaned-up' version of the Phone number (all non-digits removed).

Then the searching query would run against that field. (user never sees the new field, it's behind the scenes only)

eg the table has 2 fields now:
PhoneNo CleanPhone
'314 233 6540' '3142336540'
'314 2336 540' '3142336540'
'314-233-6540 '3142336540'

And when the user searches on
'314-233'
this would first be cleaned the same way, to
'314233'
and then the query would run.

And like wise the new field is calculated and saved every time the record is created/edited.

The new field can be indexed and give a *much* faster response than processing phone number in every record...

Hope I'm not missing something stupid here...

DJ

[kbrill]

this will return a more accurate result.
In the way the search is implemented in your fix if you have this numbers: 314 233 6540 and 314 323 36540 in the database and if you search for 314 233 6540 then the both number will be returned as result.

Not sure why you would have a number line 314 323 36540 and I would put forward that if you did then you might want to to come up.

However that said I like your approach very much. It might not be perfect but it gets me thinking.

[eggsurplus]

Ken,

Another option is to replace every non-digit with a % wildcard and then just do a normal search:

  
$phone = preg_replace('/\d/','%$0',preg_replace('/\D/','',$phone));