How to search in SQL for cloumn with asterisk?
0
I try to create advanced search to my database.
I want to do something like that: if the user type for search = overf**w
and I have in my database an cloumn that his value = overflow - show him.
this my code:
$name = str_replace('*', '_', $name);
SELECT name FROM table WHERE name LIKE CONCAT('%', ?, '%')
its not working, I dont know what the problem.
mysqli
asked Nov 13 '18 at 7:12
JohnJohn
98
add a comment |
0
I try to create advanced search to my database.
I want to do something like that: if the user type for search = overf**w
and I have in my database an cloumn that his value = overflow - show him.
this my code:
$name = str_replace('*', '_', $name);
SELECT name FROM table WHERE name LIKE CONCAT('%', ?, '%')
its not working, I dont know what the problem.
mysqli
asked Nov 13 '18 at 7:12
JohnJohn
98
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
@pravinnavle no, I want if user wrote in inputoverf**wand I have in my databaseoverflow- return overflow
– John
Nov 13 '18 at 7:28
add a comment |
0
0
0
1
I try to create advanced search to my database.
I want to do something like that: if the user type for search = overf**w
and I have in my database an cloumn that his value = overflow - show him.
this my code:
$name = str_replace('*', '_', $name);
SELECT name FROM table WHERE name LIKE CONCAT('%', ?, '%')
its not working, I dont know what the problem.
mysqli
asked Nov 13 '18 at 7:12
JohnJohn
98
I try to create advanced search to my database.
I want to do something like that: if the user type for search = overf**w
and I have in my database an cloumn that his value = overflow - show him.
this my code:
$name = str_replace('*', '_', $name);
SELECT name FROM table WHERE name LIKE CONCAT('%', ?, '%')
its not working, I dont know what the problem.
mysqli
mysqli
asked Nov 13 '18 at 7:12
JohnJohn
98
asked Nov 13 '18 at 7:12
JohnJohn
98
asked Nov 13 '18 at 7:12
JohnJohn
98
asked Nov 13 '18 at 7:12
JohnJohn
98
asked Nov 13 '18 at 7:12
JohnJohn
98
98
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
@pravinnavle no, I want if user wrote in inputoverf**wand I have in my databaseoverflow- return overflow
– John
Nov 13 '18 at 7:28
add a comment |
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
@pravinnavle no, I want if user wrote in inputoverf**wand I have in my databaseoverflow- return overflow
– John
Nov 13 '18 at 7:28
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
@pravinnavle no, I want if user wrote in input
overf**w and I have in my database overflow - return overflow– John
Nov 13 '18 at 7:28
@pravinnavle no, I want if user wrote in input
overf**w and I have in my database overflow - return overflow– John
Nov 13 '18 at 7:28
add a comment |
1 Answer
1
active
oldest
votes
0
You can't use LIKE in this situation, you need to use REGEXP() to do a wildcard search. Replace * or ** with .*. To only return names that starts with the given value use ^ at the beginning of the regular expression
SELECT name
FROM actors
WHERE name REGEXP('^overf.*w')
I don't know php but your $name parameter should be set like this (in pseudo code)
$name = '^' + replace($name, '**', '.*')
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use?as in your code but for the $name variable you need to replace**with.*
– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
|
show 8 more comments
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
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%2f53275668%2fhow-to-search-in-sql-for-cloumn-with-asterisk%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can't use LIKE in this situation, you need to use REGEXP() to do a wildcard search. Replace * or ** with .*. To only return names that starts with the given value use ^ at the beginning of the regular expression
SELECT name
FROM actors
WHERE name REGEXP('^overf.*w')
I don't know php but your $name parameter should be set like this (in pseudo code)
$name = '^' + replace($name, '**', '.*')
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use?as in your code but for the $name variable you need to replace**with.*
– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
|
show 8 more comments
0
You can't use LIKE in this situation, you need to use REGEXP() to do a wildcard search. Replace * or ** with .*. To only return names that starts with the given value use ^ at the beginning of the regular expression
SELECT name
FROM actors
WHERE name REGEXP('^overf.*w')
I don't know php but your $name parameter should be set like this (in pseudo code)
$name = '^' + replace($name, '**', '.*')
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use?as in your code but for the $name variable you need to replace**with.*
– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
|
show 8 more comments
0
0
0
You can't use LIKE in this situation, you need to use REGEXP() to do a wildcard search. Replace * or ** with .*. To only return names that starts with the given value use ^ at the beginning of the regular expression
SELECT name
FROM actors
WHERE name REGEXP('^overf.*w')
I don't know php but your $name parameter should be set like this (in pseudo code)
$name = '^' + replace($name, '**', '.*')
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
You can't use LIKE in this situation, you need to use REGEXP() to do a wildcard search. Replace * or ** with .*. To only return names that starts with the given value use ^ at the beginning of the regular expression
SELECT name
FROM actors
WHERE name REGEXP('^overf.*w')
I don't know php but your $name parameter should be set like this (in pseudo code)
$name = '^' + replace($name, '**', '.*')
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
edited Nov 13 '18 at 8:26
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
answered Nov 13 '18 at 7:21
Joakim DanielsonJoakim Danielson
8,3723724
8,3723724
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use?as in your code but for the $name variable you need to replace**with.*
– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
|
show 8 more comments
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use?as in your code but for the $name variable you need to replace**with.*
– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
But I cant do what you wrote, because I get the value from user input
– John
Nov 13 '18 at 7:23
That was just an example, you should use
? as in your code but for the $name variable you need to replace **with .*– Joakim Danielson
Nov 13 '18 at 7:50
That was just an example, you should use
? as in your code but for the $name variable you need to replace **with .*– Joakim Danielson
Nov 13 '18 at 7:50
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
I do what you said, but I have a problem. I want to search in sql only for value that start in the user input, not contain the user input
– John
Nov 13 '18 at 8:14
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
@John, I don't understand what you mean.
– Joakim Danielson
Nov 13 '18 at 8:16
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
for example: if user type overf* *w, I want to get values from sql like: overflow, overflow its forum, overflow the best. and do not get values like that: my overflow, what is it overflow I want to get only what beginning with overflow
– John
Nov 13 '18 at 8:19
|
show 8 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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%2f53275668%2fhow-to-search-in-sql-for-cloumn-with-asterisk%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
do you mean when you search overf**w then the $name is overf__w and then you want the query to return overflow?
– pravin navle
Nov 13 '18 at 7:21
@pravinnavle no, I want if user wrote in input
overf**wand I have in my databaseoverflow- return overflow– John
Nov 13 '18 at 7:28