MySQL LIKE question

Question

I have a script:

$friendnotes = mysql_query("SELECT nid,user,subject,message FROM friendnote 
WHERE tousers LIKE '%$userinfo[username]%' ");

And the content in the "tousers" table of the database:

Test 
Example 
User

That script appears to be working well

However, if there is a user called "Test2", it would also display content that has "Test2" in the database where $userinfo[username] is just "Test"

Is there any way to fix that problem? For example (this is just an example, I don't mind if you give another way) make it so that it searches whole lines?

EDIT: I don't think anyone understands, the "tousers" table contains multiple values (seperated by line) not just one, I want it to search each LINE (or anything that works similiar), not row

Answer 1

From what I understand, you should just use strict comparison:

where tousers = 'whatever'

That is because tousers like %whatever% matches any row, in which the tousers field has 'whatever' anywhere in its content, so it matches 'whatever', '123whatever', 'whatever321' and '123whatever321'. I hope you get the idea.

Answer 2

So you only want to search for exact name matches? If so, just use an = and remove the % wildcards:

$friendnotes = mysql_query("SELECT nid,user,subject,message FROM friendnote 
WHERE tousers = '$userinfo[username]' ");

Answer 3

This will work if you remove the % signs, which are what allow for pattern matching.

$friendnotes = mysql_query("SELECT nid,user,subject,message FROM friendnote 
WHERE tousers LIKE '$userinfo[username]' ");

But the consensus seems to be that using equals will be faster. See http://stackoverflow.com/questions/543580/equals-vs-like.

So in that case, change to

$friendnotes = mysql_query("SELECT nid,user,subject,message FROM friendnote 
WHERE tousers = '$userinfo[username]' ");

Edit - regarding your edit, that is not a really good design. If a user can have multiple "tousers" (ie a one-to-many relationship), that should be represented as a separate table tousers, where each row represents one "touser" and has a foreign key on the user id to match it with the friendnote table. But if you absolutely can't change your design, you might want to match like this:

WHERE tousers LIKE '%$userinfo[username]\n%' ");

ensuring that there is a line break immediately following the username.

Answer 4

The condition

tousers LIKE '%Test%'

means that touser contains "Test" at some point, so it is true for "Test","MyTest","Test3","MyTest3", and so on.

If you want only to match the current user, try

... WHERE tousers = '$userinfo[username]'

EDIT If you really want to store multiple names in one column (separated by newlines), you could use a REGEXP pattern like

WHERE tousers REGEXP '(^|\\n)($userinfo[username])($|\\n)'

Be aware to make sure that $userinfo[username] does not contain any regular-expression-like characters ('$', '^', '|', '(', etc.). Also (as mentioned in the comments above) this solution is suboptimal in terms of security/performance/etc: It would be better to model an 1:n-Relationship between the friendnote table and some friendnotes_user table ...

Answer 5

Ok, so it sounds like the tousers field can contain values like 'stuff test option whatever' and 'foo test2 something blah blah', and you want to match the first but not the second. In that case, you need to include the delimiters around your search term. Assuming the search term will always have a space before and either a space or comma after it, you could do something like:

... WHERE tousers LIKE '%[ ]$userinfo[username][ ,]%'

This will encounter problems, however, if your search term can occur at the beginning of the field (no space character before it) or at the end of the field (no delimiter after it). In that case, you might need to have multiple LIKE clauses.

Answer 6

This is a perfect usage case for the MySQL REGEXP operator.