I am developing an application where user can enter their phone numbers. The phone numbers can be of any country, may/may not have country code in it. Can start with + or 0 and can be of any length.
The challenge for me to make sure that there are no duplicate phone numbers in the database. I need your help in this. Because if there are 2 same numbers but with different country codes then they should NOT be treated as same.
For example the following two numbers are same but with different country codes:
Can someone help me?
PS: I m using PHP/MySQL
PPS: For some reason I cannot add a country code dropdown box separately.
I’m not seeing the issue - those two numbers are not the same, so you could either set the column as non-duplicate, or check prior to accepting the input. If your table has a column for country code and another for phone number, I would think you’d need to check prior to inserting the new record for any duplicates.
I thought something like that, it was the “for some reason I cannot add a country code dropdown” - I wondered what the reason was. But if the OP wants to ensure that the “country code + phone number” combination is always unique, seems that having them in the same input just cuts out a step during validation. I must be missing something from the original post, as to what input phone numbers would be considered the same when they should not be.
But that’s what I meant about not being sure about the problem. Those two phone numbers are different, and I’m confused about what difficulty you’re having in checking them for being unique in the database. Especially if you store country+phone number in a single database column.
What I really meant was, what data would come in from your form that isn’t unique, but might be thought to be? Even if the user typed in just “991122334455”, that would be different from the two above, and unless you’ve got some code to guess the country code, you would just compare it to those that already are there. OK, so the user might have meant to add +93 at the start, but you can’t guess at that - all you could do is separate the country code and phone number, and insist on having both.
The problem will arise when a user from say India has added the number 991122334455 without the country code and another user has prepended the country code and added 91991122334455 and another user from india added the same number with + and country code i.e. +991122334455
How would I check for number duplicacy in this case?
Well, for the last two you can store the number in two ways in the database - in the way the user entered it, and separately with no formatting, no symbols, nothing other than digits. Then strip out all that from the users input and check for duplicates. I have a books database in which I store the formatted ISBN number as the user enters it (sometimes with spaces, sometimes with dashes, and so on), and another column where I store the digits / letters only, and use that for the duplicate check.
For the first one, I can’t see that it’s possible to check. If you know the user is from India, you could check for the number as entered, then prepend the country code and check again.
Really the issue here is that you’re allowing data to be supplied in inconsistent formats, which makes it difficult to validate and may make it difficult to search on later. The best way to solve the problem is to not allow them to enter a phone number without a country code, and for it to be consistently formatted by the time it hits the database.
The phrase used in the penultimate reply sums up your problem; you have inconsistency in the way you allow users to add numbers. If you are writing the application then this issue is non existent if you were to use a separate input for STD code and phone number. As the developer make life easy for yourself and also reenforce the integrity of the logic when storing in your DB. There is too much margin for erroneous input.
In short if you have control over the design of the logic/coding - implement a better system.
What happens when you dial those numbers on a telephone though? How does that know which one is which? It’s a while since I’ve dialled an international number, but I didn’t enter any symbols last time. So how would the telephone network “know” which of those you mean? I suspect it won’t, and that they would both ring in the same place. Does it even happen, though? I know you mean the above numbers as an example, but after an admittedly cursory look at a list of international dialling codes, I can see for example a code 20 but no 20x, there’s an 81, but no 811, and so on.
In the UK there is a list for Jersey as 44 1481, but in this case the 1481 is part of the UK number plan in any case, so there isn’t a 44 1481 xxxxxx that isn’t in Jersey that could be confused with the one that is.