CODEWITHSUNDEEP
phpmysql
user1236473
user1236473

Reputation:

MySQL LIKE query not pulling similar data

I have a simple MySQL-based search script, I will post only the part relevant to the problem below. 

$name = 'John Doe';
$query = 'SELECT username FROM members WHERE name LIKE %$name%';

Now, the problem is when I search for John Doe instead of getting the user with that particular name, I get all users named John and Doe and, the funny thing is that, it does not even put John Doe at the top, meaning... you could find John xxxx before that.

Is there and advanced MySQL attribute to accomplish this task?

Upvotes: 0

Views: 71

Answers (4)

luksch
luksch

Reputation: 11712

change

$query = 'SELECT username FROM members WHERE name LIKE %$name%';

to

$query = 'SELECT username FROM members WHERE name LIKE "'.%$name%.'"';

or

$query = "SELECT username FROM members WHERE name LIKE '%$name%'";

note, that changing to

$query = 'SELECT username FROM members WHERE name LIKE "%$name%"';

will NOT do the trick.

This is because single quoted strings do not interpret variables.

Note: Unlike the double-quoted and heredoc syntaxes, variables and escape sequences for special characters will not be expanded when they occur in single quoted strings.

Double quoted strings however, will.

The most important feature of double-quoted strings is the fact that variable names will be expanded. See string parsing for details.

UPDATE: This was heavily edited and changed in meaning, thanks to Memolition :)

Upvotes: 2

shark555
shark555

Reputation: 2572

You can also use Full Text index(only MyISAM AFAIR) and MATCH AGAINST clause. It will also give you rate of similarity. Very useful in some cases. Examples here: http://forum.kohanaframework.org/discussion/9182/mysql-full-text-searching-match-against/p1

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

You need to have single quotes around your the string you are using as the comparison for LIKE

In fact, since you have your query in single quotes what you are really querying against is:

SELECT username FROM members WHERE name LIKE %$name%

with literal $name.

Write your query like this:

$query = "SELECT username FROM members WHERE name LIKE '%$name%'";

Upvotes: 0

Memolition
Memolition

Reputation: 494

change

$query = 'SELECT username FROM members WHERE name LIKE %$name%';

to

$query = "SELECT username FROM members WHERE name LIKE '%$name%'";

not 

$query = 'SELECT username FROM members WHERE name LIKE "%$name%"';

because the double quotes as luksch says will print $name instead of $name variable value

so it has to be single quotes ' like this '%name'

so ' single quotes works for strings and variables

and " double quotes only works for strings

Upvotes: 1

Related Questions