1
    $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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jake
  • 1,469
  • 4
  • 19
  • 40
  • so you want it to show only @Jake exactly? – Mike K. Sep 14 '11 at 21:34
  • There's several examples of how to do this here now, however all of them will perform terribly if you expect to run this on page load. Anything without an index will perform poorly. It would be better to either extract "@Name" from the post at post time and create a reference in an indexed table (`post_mention.post_id` = `post.id` && `post_mention.name` = "@Jake") or to have a batch process find "@Name" mentions and create an index. – six8 Sep 14 '11 at 21:41

7 Answers7

3

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.)

martin clayton
  • 76,436
  • 32
  • 213
  • 198
  • 1
    +1 I was writing a similar answer, but you posted first. :) By the way, you might not need the left-hand word boundary pattern; the `@` itself may be enough to restrict the match to only the strings you want. There's no requirement to make the word boundary patterns balance. – Bill Karwin Sep 14 '11 at 21:39
  • Doing this seems to return no data. – Jake Sep 14 '11 at 21:45
  • Thanks Bill. I put the left boundary in as an illustration, but on reflection I think it probably would have broken the expression. So I've corrected. – martin clayton Sep 14 '11 at 21:50
  • @Jake - I think I had an error in the pattern first time round. – martin clayton Sep 14 '11 at 21:51
  • 1
    +1. In theory it is still vulnerable because `$user` can have special symbols used in regular expressions :) – Karolis Sep 14 '11 at 21:53
0

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 . '") ';
Joe
  • 15,669
  • 4
  • 48
  • 83
  • FWIW, FULLTEXT indexes are supported only in MyISAM tables in current versions of MySQL, but an InnoDB fulltext implementation is reportedly coming in the future, perhaps in MySQL 5.6. – Bill Karwin Sep 14 '11 at 21:41
  • I find it to be the defining feature of MyISAM generally. If I want fulltext searching I'll use MyISAM, if I want foreign keys I'll use InnoDB. I rarely want both :P – Joe Sep 14 '11 at 21:43
  • The issue that trumps both fulltext indexing and foreign keys is that MyISAM gets corrupted if you breathe on it hard. InnoDB can get corrupted too, but it's a lot better at automatic recovery. – Bill Karwin Sep 14 '11 at 22:17
0

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.

John Cartwright
  • 5,109
  • 22
  • 25
0

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.

Taz
  • 3,718
  • 2
  • 37
  • 59
simondog
  • 1
-1

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));
six8
  • 2,886
  • 21
  • 20
-1
$SQL = "SELECT * FROM `user_posts` WHERE (`post` LIKE '%@".$user."')";
Andrey
  • 1,808
  • 1
  • 16
  • 28
-1

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."')";
Landon
  • 4,088
  • 3
  • 28
  • 42