i need to select anything from a mysql table where category= one of several things
ie something like:
where cat=1 or cat=2 or cat=fun
something like that if it makes sense
Printable View
i need to select anything from a mysql table where category= one of several things
ie something like:
where cat=1 or cat=2 or cat=fun
something like that if it makes sense
SELECT * FROM table_name WHERE cat = # or cat = #
You can use ( and ) in SQL statements like: (cat = 'a' AND cat = 'b') OR (cat = 'c' AND cat = 'd')
Also, it depends on what type of data you are using in the statement where the # are. For example, if the column contains int's, just go cat = 1, otherwise if the column is string data etc use cat = 'text here'
It's more efficient and easier to read to use the IN keyword:
This does exactly what you propose: it selects columns where the 'cat' column matches a value 'IN' the list you provide.Code:SELECT * FROM table WHERE cat IN (1,2,'fun');
just saw the 'in' deal, i am gonna stick with or code now that i have it working, it seems easy enough.
thanks much both!
Although OR code may work, keep in mind that it severely hampers your ability to index the query, which is crucial for performance.
so 'in' is going to be faster than 'or'?
i could not get in to work i tried:
WHERE user='$user' AND cat IN ( 1, 2, 'fun')
and it did not work.
Looks like the problem there is you're mixing numbers and a string. try:
WHERE user='$user' AND cat IN ('1','2','fun')
Yeah, it may not like the mixed types. An actual error message would help, though.
Actually recent versions of MySQL Query Optimizer (starting from 4.0 I believe) are able to detect ORs and use range select when possible, just like when using IN(...).Quote:
Although OR code may work, keep in mind that it severely hampers your ability to index the query, which is crucial for performance.
oh, cool then i will stick