Hi,
Part of your analysis is not correct. The query does not find the number anywhere but just the final bit. The like search has the % (wildcard) at the begining only, then the significant digits as retrieved in the callerid.
I made a quick test with the same data you posted as an example:
MariaDB [asterisk]> select id,phone1,phone2,firstname from visual_phonebook;
+----+----------+----------+-----------+
| id | phone1 | phone2 | firstname |
+----+----------+----------+-----------+
| 1 | 650 | 12345678 | User A |
| 2 | 12650348 | | User B |
+----+----------+----------+-----------+
2 rows in set (0.00 sec)
Then I run the query to match based on callerid '650':
MariaDB [asterisk]> SELECT id,phone1,phone2,concat(firstname,' ',lastname) AS name,company,picture FROM visual_phonebook WHERE (phone1 LIKE '%650' OR phone2 LIKE '%650') AND context='' ORDER BY LENGTH(CONCAT(phone1,phone2)) LIMIT 1;
+----+--------+----------+---------+---------+---------------+
| id | phone1 | phone2 | name | company | picture |
+----+--------+----------+---------+---------+---------------+
| 1 | 650 | 12345678 | User A | | 1-picture.png |
+----+--------+----------+---------+---------+---------------+
1 row in set (0.00 sec)
As you see, the User A record is retrieved. Not sure why you were getting the incorrect contact, only the LAST part of the number is matched.
Regarding normalization of entered numbers in the phonebook, what can I say, people might type spaces, parenthesis, dashes, dots, words, whatever... Its a bit complicated to do that kind of normalization. In general, whenever phone number lists are involved, users should use 'computer friendly' numbers to get consistent results.
So, a quick way to compare numbers would be to strip any non digit character from it, but MySQL lacks that function, however there are some examples posted on StackOverflow, like this one:
DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE output VARCHAR(255) DEFAULT '';
DECLARE iterator INT DEFAULT 1;
WHILE iterator < (LENGTH(input) + 1) DO
IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
END IF;
SET iterator = iterator + 1;
END WHILE;
RETURN output;
END
$$
With that function created on MySQL asterisk database, then you can modify the query in vphonebook.php a bit:
SELECT id,phone1,phone2,concat(firstname,' ',lastname) AS name,company,picture FROM visual_phonebook WHERE (STRIP_NON_DIGIT(phone1) LIKE '%650' OR STRIP_NON_DIGIT(phone2) LIKE '%650') AND context='' ORDER BY LENGTH(CONCAT(phone1,phone2)) LIMIT 1;
The thing is that I am not much comfortable having users needing to add a Function in MySQL/MariaDB by default... maybe I am just picky and its not a problem at all. What do you think?
