Finding a number in mysql database
up vote
-3
down vote
favorite
Using PHP, I am trying to find a record in a MySQL table where one of three columns may contain a part of a number that is searched for.
For example:
$number = 012321456789;
$get_results = mysqli_query($con,"
SELECT ID
FROM students
WHERE StudentTel LIKE '%$number%'
OR ParentTel LIKE '%$number%'
OR SponsorTel LIKE '%$number%'
");
The above lists just about the entire database as matching results.
Because numbers are captured in different ways, I cannot search for an exact match. A number may be captured like +270123456789, (27)0123456789, 0123456789-3 etc
I tried converting the number to a string, but same results.
I've also tried FIND_IN_SET($number,StudentTel) etc, but then I get no result - probably because there is no exact match.
Any assistance will be greatly appreciated.
php mysql numbers
edited Nov 8 at 12:02
Strawberry
25.6k83149
asked Nov 8 at 11:29
user3270093
14
|
show 14 more comments
up vote
-3
down vote
favorite
Using PHP, I am trying to find a record in a MySQL table where one of three columns may contain a part of a number that is searched for.
For example:
$number = 012321456789;
$get_results = mysqli_query($con,"
SELECT ID
FROM students
WHERE StudentTel LIKE '%$number%'
OR ParentTel LIKE '%$number%'
OR SponsorTel LIKE '%$number%'
");
The above lists just about the entire database as matching results.
Because numbers are captured in different ways, I cannot search for an exact match. A number may be captured like +270123456789, (27)0123456789, 0123456789-3 etc
I tried converting the number to a string, but same results.
I've also tried FIND_IN_SET($number,StudentTel) etc, but then I get no result - probably because there is no exact match.
Any assistance will be greatly appreciated.
php mysql numbers
edited Nov 8 at 12:02
Strawberry
25.6k83149
asked Nov 8 at 11:29
user3270093
14
For example: $number = 012321456789;- That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the+in+270123456789is also an issue and should also be wrapped in quotes, same goes for all of them.
– Funk Forty Niner
Nov 8 at 11:33
3
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
1
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
1
LIKE '27021%'would work then and find all normalised phone numbers that start with27021. Which wouldn't be possible if some of them would be stored as(27)021.
– Sergiu Paraschiv
Nov 8 at 12:38
1
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains2303205anywhere, so if you are searching withLIKE '%2303205%'you should not find anything at all here.
– misorude
Nov 8 at 14:12
|
show 14 more comments
up vote
-3
down vote
favorite
up vote
-3
down vote
favorite
Using PHP, I am trying to find a record in a MySQL table where one of three columns may contain a part of a number that is searched for.
For example:
$number = 012321456789;
$get_results = mysqli_query($con,"
SELECT ID
FROM students
WHERE StudentTel LIKE '%$number%'
OR ParentTel LIKE '%$number%'
OR SponsorTel LIKE '%$number%'
");
The above lists just about the entire database as matching results.
Because numbers are captured in different ways, I cannot search for an exact match. A number may be captured like +270123456789, (27)0123456789, 0123456789-3 etc
I tried converting the number to a string, but same results.
I've also tried FIND_IN_SET($number,StudentTel) etc, but then I get no result - probably because there is no exact match.
Any assistance will be greatly appreciated.
php mysql numbers
edited Nov 8 at 12:02
Strawberry
25.6k83149
asked Nov 8 at 11:29
user3270093
14
Using PHP, I am trying to find a record in a MySQL table where one of three columns may contain a part of a number that is searched for.
For example:
$number = 012321456789;
$get_results = mysqli_query($con,"
SELECT ID
FROM students
WHERE StudentTel LIKE '%$number%'
OR ParentTel LIKE '%$number%'
OR SponsorTel LIKE '%$number%'
");
The above lists just about the entire database as matching results.
Because numbers are captured in different ways, I cannot search for an exact match. A number may be captured like +270123456789, (27)0123456789, 0123456789-3 etc
I tried converting the number to a string, but same results.
I've also tried FIND_IN_SET($number,StudentTel) etc, but then I get no result - probably because there is no exact match.
Any assistance will be greatly appreciated.
php mysql numbers
php mysql numbers
edited Nov 8 at 12:02
Strawberry
25.6k83149
asked Nov 8 at 11:29
user3270093
14
edited Nov 8 at 12:02
Strawberry
25.6k83149
asked Nov 8 at 11:29
user3270093
14
edited Nov 8 at 12:02
Strawberry
25.6k83149
edited Nov 8 at 12:02
Strawberry
25.6k83149
edited Nov 8 at 12:02
Strawberry
25.6k83149
25.6k83149
asked Nov 8 at 11:29
user3270093
14
asked Nov 8 at 11:29
user3270093
14
asked Nov 8 at 11:29
user3270093
14
14
For example: $number = 012321456789;- That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the+in+270123456789is also an issue and should also be wrapped in quotes, same goes for all of them.
– Funk Forty Niner
Nov 8 at 11:33
3
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
1
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
1
LIKE '27021%'would work then and find all normalised phone numbers that start with27021. Which wouldn't be possible if some of them would be stored as(27)021.
– Sergiu Paraschiv
Nov 8 at 12:38
1
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains2303205anywhere, so if you are searching withLIKE '%2303205%'you should not find anything at all here.
– misorude
Nov 8 at 14:12
|
show 14 more comments
For example: $number = 012321456789;- That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the+in+270123456789is also an issue and should also be wrapped in quotes, same goes for all of them.
– Funk Forty Niner
Nov 8 at 11:33
3
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
1
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
1
LIKE '27021%'would work then and find all normalised phone numbers that start with27021. Which wouldn't be possible if some of them would be stored as(27)021.
– Sergiu Paraschiv
Nov 8 at 12:38
1
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains2303205anywhere, so if you are searching withLIKE '%2303205%'you should not find anything at all here.
– misorude
Nov 8 at 14:12
For example: $number = 012321456789; - That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the + in +270123456789 is also an issue and should also be wrapped in quotes, same goes for all of them.– Funk Forty Niner
Nov 8 at 11:33
For example: $number = 012321456789; - That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the + in +270123456789 is also an issue and should also be wrapped in quotes, same goes for all of them.– Funk Forty Niner
Nov 8 at 11:33
3
3
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
1
1
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
1
1
LIKE '27021%' would work then and find all normalised phone numbers that start with 27021. Which wouldn't be possible if some of them would be stored as (27)021.– Sergiu Paraschiv
Nov 8 at 12:38
LIKE '27021%' would work then and find all normalised phone numbers that start with 27021. Which wouldn't be possible if some of them would be stored as (27)021.– Sergiu Paraschiv
Nov 8 at 12:38
1
1
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains
2303205 anywhere, so if you are searching with LIKE '%2303205%' you should not find anything at all here.– misorude
Nov 8 at 14:12
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains
2303205 anywhere, so if you are searching with LIKE '%2303205%' you should not find anything at all here.– misorude
Nov 8 at 14:12
|
show 14 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206860%2ffinding-a-number-in-mysql-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
For example: $number = 012321456789;- That will not work with the leading zero, since it is being treated as an octal; wrap it in (single) quotes. Edit: the+in+270123456789is also an issue and should also be wrapped in quotes, same goes for all of them.– Funk Forty Niner
Nov 8 at 11:33
3
The right way would to keep normalized phone numbers in database (without any special chars). If you really need original "formatted" numbers, then you might have separate columns with normalized versions just for search purpose.
– Jakub Matczak
Nov 8 at 11:37
1
“The above lists just about the entire database as matching results.” - that can only be true if every single one of your records matches the number for at least one of the fields you are looking in; if that’s not the case, then your analysis of the problem must be wrong to begin with.
– misorude
Nov 8 at 12:04
1
LIKE '27021%'would work then and find all normalised phone numbers that start with27021. Which wouldn't be possible if some of them would be stored as(27)021.– Sergiu Paraschiv
Nov 8 at 12:38
1
“I am searching for "2303205", and the first 3 matches I get are for the following numbers: "0712000061", "0849994189", "0766458485".” - then you must be doing your “searching” completely, absolute, totally wrong … None of those values contains
2303205anywhere, so if you are searching withLIKE '%2303205%'you should not find anything at all here.– misorude
Nov 8 at 14:12