How do I search for multiple values in one MySQL field?

Question

I have a MySQL table which contains a list of users. I want to search for all users that have a certain rank, or rights. There are five levels, 0 to 4. When I search my user_rights column for 2 OR 3 OR 4 in PHP, it only returns one row, but when I use the same criteria in a MySQL search, it returns all of the results. Is there a simple solution?

Answer 1

If you want to search for multiple values in one column, use IN:



SELECT * FROM users WHERE rank IN (2,3);



The above query is the same as:



SELECT * FROM users WHERE rank = 2 OR rank = 3;

Answer 2

First answer is correct; I'd just add that it's a good idea to bracket conditions:



where (username = 'fred' OR username = 'bert') and userlevel = 2



where (username = 'fred') OR (username = 'bert' and userlevel = 2)

Answer 3

[EDIT] Wow I did not find out about FULLTEXT -- that's a much more dependent (and certainly more efficient) method to this problem. [/END EDIT] Assuming what you want is to seek out all rows where all of the key terms the consumer enters can also be located in a particular column, you should utilize this: pick field1, field2, field3 FROM table_name the place (field1 LIKE '%apple%' AND field1 LIKE '%toast%') Of course you'll in general wish to expand on this -- for illustration that is case sensitive. If you do not need it to be case sensitive alternate: field1 LIKE '%apple%' to: upper(field1) LIKE higher('%apple%' ) (and so on) that you can additionally do all varieties of things with ANDs and ORs and whanot.. For illustration all rows with "apple" and "toast" in them OR "jam" and "butter" but no longer cranberry ... Where ( ( (upper(field1) LIKE '%APPLE%' AND upper(field1) LIKE '%TOAST%') OR (higher(field1) LIKE '%JAM%' AND upper(field1) LIKE '%BUTTER%') ) AND upper(field1) no longer LIKE '%CRANBERRY%' )

Answer 4

You must repeat the fields in your query:

select * from `table_name` where `user_rights` = '2' or `user_rights` = '3'