Phone search in the database

Question

MySQL

The database has a phone field that contains phone numbers in the format "7 [phone]" and the site has a search form in which the user can fill in whatever he wants (only numbers, of course) how to correctly compose a query for searching by phone ?

Answer 1

It is better to store only numbers in the database. Accordingly, when requesting only their regexp, you leave it.
In the current situation, after converting the number into numbers, you will have to convert them into your request.
Thus, do not care what the user drove. Let at least “8 (9 26) 12 3-4-5-6-7-” only 89261234567 get to you, and this can already be turned into 7 [phone]

Answer 2

What exactly is the problem?
The database knows the format.
The user only enters numbers.
User entered 84951234567 - convert it to 7 [phone] and search.

Answer 3

It is worth considering that the phone may be with an extension.
The phone may not start with +7.
The region code can be not only 3x, but also 4-5 significant, for example 8 (81378) 123-45.
Perhaps none of this applies to your situation.
And you can store 2 options in the database. Since the user wrote down the text for display. Only numbers for search and other manipulations.
If you already have a database of phones in the format 7 [phone], and you need to search by part of the number, then I think it’s worth making a string field containing the digits of the number and using it LIKE% part of the number%. If the search is only by the full number, then an integer field with the digits of the number is sufficient.

Answer 4

There are two options.
1. Write a validator in Javascript that will check the correctness of the format of the entered data and check the correctness of the format on the server side before requesting the database (in this case, you can demand from the user any single format, i.e. for example: [phone] and already such numbers convert to the required format in which they are stored in the database).
2. As suggested above, convert the various phone dialing options to the required format on the client or server side.

Answer 5

You can make things easier with jquery. The user will enter the number in this format without straining, it will correct itself. For example, using this plugin: github.com/RobinHerbots/jquery.inputmask
And then search in the database using LIKE, as suggested above.