I took a long time to get around to this, but I have had a play, and I think I know what is going on. I have set up a workaround, but I also think I know what is needed for a proper fix.
THE PROBLEM
When a user dialled from an extension (3 digits) the lookup would often incorrectly return a name and picture associated with a different address book entry. This happened when the 3 digit extension number appeared anywhere within the longer phone number. For example, calling extension 650, would select entry for user with phone number 12346501
The issue is that the logic of this select statement did not work:
$res = $db->consulta("SELECT concat(firstname,' ',lastname) AS name,company,picture FROM visual_phonebook WHERE (phone1 LIKE '%%%s'OR phone2 LIKE '%%%s') AND context='%s' ORDER BY LENGTH(CONCAT(phone1,phone2)) LIMIT 1",$clid_significant,$clid_significant,$context);The problem is that the LIKE statement was finding all records containing the callerid phone number ANYWHERE within it. So, when the calling extension is 650, it matched on ALL of 1236504, 1234650, 650, 16501245. The ORDER BY statement was then supposed to select the shortest match (which should have been the extension). However, because there are two phone fields, an attempt was made to identify the record with the shortest match by concatenating both numbers in the record, and comparing that length with the concatenated numbers of the other matching records.
The LIMIT 1 statement then picked the first record.
This does not work because there is no certainty that the total length of both phone number fields will be shorter for the correct entry.
Consider this mini-database:
Calling Extension is 650.
User A Phone1: 650 Phone2: 12345678
User B Phone1: 12650348 Phone2: nul
Both of these records are selected by the above statement. They are then compared by adding the two phone fields, and picking the shortest COMBINED length:
User A: 3+8=11
User B: 8+0=8
User B record is shorter, so it is selected. The correct record is User A.
MY WORKAROUND
In Australia, all phone numbers are 10 digits (including area code) – even mobile numbers.
So, I have set the comparison to EQUALS (rather than LIKE) so that I only get one valid match. I have removed the LENGTH comparison altogether.
$res = $db->consulta("SELECT concat(firstname,' ',lastname) AS name,company,picture FROM visual_phonebook WHERE (phone1 = '%s' OR phone2 = '%s') AND context='%s' LIMIT 1",$clid_significant,$clid_significant,$context);This works, except that if the visual phone book entry does not include the area code, or includes spaces, the record is not found.
FIX REQUIRED
• We need to be able to strip the spaces out of the visual phonebook phone number entries when comparing;
• We need to be able to perform a routine on the visual phonebook phone number entries to only compare the significant digits – in the same way that the script does with the caller id number:
$significant = 10;
$startoffset = 0;
if($largo > $significant) {
$startoffset=$largo-$significant;
}
$clid_significant = substr( $decodedClidnum, $startoffset );When comparing the CLID with the visual phone book entries after stripping spaces and looking at the same significant digits, the EQUALS comparison operator will then work as desired.
I am not sufficiently expert to figure out how to do this bit. Hopefully what I have said helps, and the proper fix will be simple for you :)
