CODEWITHSUNDEEP
phpmysqldatabase
Nick
Nick

Reputation: 594

Efficiently match a database of phone number prefixes to a phone number?

I've got a database table full of phone number prefixes and how much per minute each one costs. The phone prefixes can vary in length a lot, from one digit to lots (5-6 or more).

Some of the prefixes will overlap, for example there is usually a default country per minute rate, so the more specific mobile rate must override the less specific rate.

Currently the way I am thinking of doing it is doing a while loop checking for the number in the prefix table, and each loop removing the last digit off the end. This is very accurate however it does do a lot of queries since a number could be 12 digits long and the relevant prefix is ~4 digits.

Is there a better way?

Update: Here is a screenshot of the table, the first column is the description, 2nd is the call rate and the third is a phone prefix. Phone cost table

The challenge is to match a real phone number like say 61412345678 to the most applicable prefix without needing as many queries as my current method.

Upvotes: 0

Views: 2707

Answers (1)

DRapp
DRapp

Reputation: 48139

Should be a simple query with an order by based on the longest prefix and returning a limit of 1 record... Something like...

select
      pp.*
   from
      phonePrefix pp
   where
      '61412345678' like pp.prefix + '%'
   order by
      length( trim( pp.prefix )) DESC
   limit 1

So, your phone number would match on 

49 Australia at .0165 = "61" prefix length of prefix 2
62 Australia - Mobile at .075 = "614" prefix length of 3 <-- would be in first position and then limit 1 returns it instead of ID 49.

Now, if a phone number like '6188319383' would return the values for #49 via "61" and #53 via "61883".

Simple query, might have to tweak it some, but principle is accurate and not positive of the actual data types you have numeric vs character.

Similar would happen if say you actually did have prefixes for

6
61
618
6188
61883

The 61883 would have a prefix length of 5, in descending order and the limit of 1 record would return it over the more generic shorter prefixes.

Upvotes: 2

Related Questions