MySQL Like query but not like other [duplicate]

This question already has answers here: 开发者_StackOverflow社区 Search for "whole word match" in MySQL (6 answers) Closed 2 years ago.

    $SQL = "SELECT * FROM `user_posts` WHERE (`post` LIKE '%@".$user."%')";

For instance, if my username is @Jake, it will show any post that has @Jake in it. But it will also do for instance, @Jake11, it will also show. How can I fix this?

---

You might consider using some sort of regular expression instead of LIKE '%...%'.

An example might be:

... WHERE `post` REGEXP '@" . mysql_real_escape_string( $user ) . "[[:>:]]'"

The [[:>:]] matches on a right word boundary. As pointed out by Bill Karwin, there's no need for a left-hand boundary pattern in this case as there is an implied word boundary at the @ character. (Indeed, you can't have a left-boundary to the left of a non-word character.)

(I'm sure others will comment on your possible exposure to SQL injection attack too.)

---

http://devzone.zend.com/article/1304

Use a FULLTEXT column :)

ALTER TABLE user_posts ADD FULLTEXT(post);

$sql = ' SELECT * FROM user_posts WHERE MATCH (post) AGAINST ("' . $user . '") ';

---

This kind of query will not be using an index and will require a full table scan. This will obviously be extremely slow once your database grows to a reasonable size. Ideally, when the post is created, you can parse the text and appropriately insert rows in a one-to-many table (properly indexed) to identity the relationship.

---

Use a regex:

$SQL = "SELECT *
        FROM `user_posts1
        WHERE (`post` LIKE '%@".$user."%)
        AND (`post` NOT RLIKE '%@".$user."[a-zA-Z0-9]%')"

In the case of your example, this will include @jake as long as the character immediately following "jake" ($user) is not between a and z, A and Z or 0-a.

---

$SQL = "SELECT * FROM `user_posts` WHERE (`post` LIKE '%@".$user."')"; 

---

The '%' is a wildcard, it will match any amount of characters in that spot. Remove those from your query and you'll be set. What that query is saying is, "find a row that has post matching [anything]@Jake[anything]"

It doesn't sound like you want to use like, just do:

$SQL = "SELECT * FROM `user_posts` WHERE (`post` = '@".$user."')";

---

It looks like you don't have a need for LIKE here, a simple equality check will work:

$SQL = "SELECT * FROM `user_posts` WHERE (`post` = '@".$user."')";

Be warned, you have a sql injection possibility here and should be using mysql_real_escape_string:

$SQL = sprintf("SELECT * FROM `user_posts` WHERE (`post` = %s)", mysql_real_escape_string('@' . $user));

If you want to find @Joe within post you can add spaces around your like item, but this will perform slowly:

$SQL = sprintf("SELECT * FROM `user_posts` WHERE (`post` LIKE %s)", mysql_real_escape_string('% @' . $user . ' %'));

For performance use a FULLTEXT index:

$SQL = sprintf("SELECT * FROM `user_posts` WHERE MATCH (post) AGAINST (%s)", mysql_real_escape_string('@' . $user));