-1

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

Ryan
  • 461
  • 3
  • 6
  • 15
  • 1
    Are you *sure* you want the wildcards, rather than just matching the value against the entire field contents? – Michael Madsen Jan 16 '10 at 16:32
  • 2
    You should really be escaping that username. Preferably before little bobby tables registers http://xkcd.com/327/ – Frank Farmer Jan 16 '10 at 16:41
  • Ok, I'm officially confused. Do you mean you want to look in multiple fields? I think you need to post some actual content from your table, and some actual examples of what you want to search for, because otherwise we're just talking at cross-purposes. – Martha Jan 16 '10 at 16:56
  • Yep, I agree, sounds like this database schema needs to be changed. Perhaps a separate table to store the contents of each line in different rows. – Kaleb Brasee Jan 16 '10 at 17:17
  • re the edit: I don't think you understand, that, (1), "tousers" is not a table, but is, in fact, a column name and, (2), that it is not a great idea to store multiple values in a single field, you should instead use a many-to-many relationship (means, extra table). Also, think about the sql injections. – shylent Jan 16 '10 at 17:19

6 Answers6

2

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

MartinStettner
  • 28,719
  • 15
  • 79
  • 106
1

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 https://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.

Community
  • 1
  • 1
danben
  • 80,905
  • 18
  • 123
  • 145
1

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.

Martha
  • 3,932
  • 3
  • 33
  • 42
0

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.

shylent
  • 10,076
  • 6
  • 38
  • 55
  • But the "tousers" field contains multiple values, and the script needs to search the field – Ryan Jan 16 '10 at 16:37
0

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]' ");
Kaleb Brasee
  • 51,193
  • 8
  • 108
  • 113
0

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

Alix Axel
  • 151,645
  • 95
  • 393
  • 500