search everything - phone numbers

digibandit

searching phone numbers only works if its exact, so if the phone number on file is 234567890 adding +1 or 1 before the phone number shows no search results.

Eg:
+11234567890 = nothing
11234567890 = nothing
234567890 = search result

JosephWithCOR

How about a partial number?

digibandit

JosephWithCOR As your screenshot shows, yes, that works…

I'm asking for something to be added so we can search for partial inside of the full text provided.

JosephWithCOR

Even searching prefix and number

digibandit

JosephWithCOR What?

digibandit

JosephWithCOR "if the phone number on file is 234567890 adding +1 or 1 before the phone number shows no search results."

digibandit

JosephWithCOR

Right, because the number 234567890 with a leading 1 or +1 doesn't exist in the database so the search results are correct.

Maybe I am over thinking. You search for something, blank results. Reduce and search again.

It would be COOL to 'live search' as you type into the search box searching as you type. No idea how that would even look or work. Or having the ability to filter the results to help refine your search.

digibandit

JosephWithCOR

Right, I understand the current behavior - that's exactly what I'm requesting be changed.

Eg: Customer calls. Staff searches for the number they see on caller ID. Currently requires exact format match - whether the number has +1 or not, the search needs to match what's in the database. That's extra clicks to figure out which format to search for.

The Problem

Current code strips formatting from the search query but not from database values:

$phone_query = preg_replace("/[^0-9]/", '', $query); // Line 16

This turns "+1-982-867-5309" into "19828675309" but the database still has "9828675309", so no match.

My proposed Solution (via changes in global_search.php)

Lines 37-38 currently:

OR contact_phone LIKE '%$phone_query%' OR contact_mobile LIKE '%$phone_query%'

Could instead be:

OR ( REGEXP_REPLACE(contact_phone, '[^0-9]', '') LIKE '%$phone_query%' OR ( CHAR_LENGTH('$phone_query') > 10 AND REGEXP_REPLACE(contact_phone, '[^0-9]', '') LIKE CONCAT('%', SUBSTRING('$phone_query', 2)) ) OR ( CHAR_LENGTH(REGEXP_REPLACE(contact_phone, '[^0-9]', '')) > 10 AND REGEXP_REPLACE(contact_phone, '[^0-9]', '') LIKE CONCAT('1', '%$phone_query%') ) )

Same pattern for contact_mobile (line 38) and vendor_phone (line 46).

What this does

Strips formatting characters (dashes, spaces, parentheses) from database values during comparison
Handles searching WITH country code when DB has number WITHOUT it
Handles searching WITHOUT country code when DB has number WITH it
Works for partial number searches (typing as you go)
Global-friendly - doesn't assume North American format, just handles leading digits intelligently

Examples

Search "9828675309" finds "9828675309", "19828675309", "+1-982-867-5309" in database
Search "+19828675309" finds "9828675309", "19828675309", "+1-982-867-5309" in database
Search "982867" finds all of the above (partial match still works)

My goal this month is to reduce clicks.

Having the system normalize formatting and handle country code differences instead of making staff guess the exact format stored in the database seems like an easy one.

Does that make sense as a feature request?

digibandit

digibandit @johnny and @wrongecho read this part only lol

JosephWithCOR

@ digibandit Now it does. Thank you.

digibandit

JosephWithCOR Wooooo! You're welcome? Thank you! Did I help? 👉👈