Asked By: Anonymous
There is a search function where you can put the phone number and it will search in all numbers in the database.
And because I want to search in all numbers fields like mobile phone and home phone I made this code:
'query_type' => 'default', 'db_field' => array ( 0 => 'phone_mobile', 1 => 'phone_work', 2 => 'phone_other', 3 => 'phone_fax', 4 => 'phone_home', ),
My problem is: Sometimes the phone numbers will not include country code for example If you search on this number: 0547896564 it will not show up in the result because the number was stored with country code like this: 0096655454445 or like this +96655454445
So to solve this: I’m trying to make the search work from the right to left, like for example: This number 0096655454445 if you want to search on it you can wright 454445 the last 6 numbers and it will be in the result.
How I can make the search working by searching on any combined numbers instead of the whole phone number?
I searched around and I found that I need something called Subquery
So my code will look like this:
'phone' => array ( 'query_type' => 'format', 'operator' => 'subquery', 'subquery' => "I don't really know what exactly should I write here" 'db_field' => array ( 0 => 'phone_mobile', 1 => 'phone_work', 2 => 'phone_other', 3 => 'phone_fax', 4 => 'phone_home', ), ),
Answered By: Anonymous
Looking at the specific problem of the search string matching patterns within a string the simplest solution is the use the SQL search LIKE, so an example for the phone_mobile field would be:
SELECT * FROM YourTable WHERE mobile_phone LIKE ?;
The parameter for the where clause should be of the form %textNumber% (e.g. %3743%). Here the % act as wild card for the search.
Looking beyond your problem to search across multiple fields, I would personally run the sql statement for each field and populate an array of results.