check for appearance of multiple strings in mysql

Question

I got a database with multiple fields and about 10000 entries. I only need to check for the appearance of 4 different strings in one field.

Example:

id--name--sex--color--size--weight--price--points--class--
1   pet   m    white  180   90      120    1000    low
2   bob   m    white  180   90      110    2000    high
3   foo   m    white  180   90      120    3000    low
4   boo   m    white  180   90      140    1000    low
5   bla   m    white  180   90      100    2000    medium
6   blu   m    white  180   90      120    5000    lala

Basically there are 3 classes, but sometimes I have a custom class. Now I want to search my whole DB for the appearance of low, high and medium. It might be that there is no entry for one or more of them. Also I need to check whether there is a custom (unknown) class. It's enough to know whether they appear, I don't need any more data.

All I came up with is just querying the field class and then searching with phps in_array(), but I guess that's just a waste of resources.

Can someone help me here please ? How do I query this clever without querying all the results?

SQL has a WHERE clause. That should do it for you with the SQL query already. — hakre
– hakre, Commented Nov 18, 2011 at 14:11
Yeah but then I'd have to do 4 queries to keep each keyword covered - seems overloaded. Is that the only way ? — Anonymous
– Anonymous, Commented Nov 18, 2011 at 14:12
No you can use logical operators or the IN function to do that with one query. Additionally there are aggregate functions (GROUP BY). — hakre
– hakre, Commented Nov 18, 2011 at 14:15

Jan Korous · Accepted Answer · 2011-11-18 14:21:26Z

2

Let database find only distinct values from class column. Your keyword is 'distinct'.

select DISTINCT class from table;

Anyway if you care I would recommend reading something about database table normalization and improving the table design.

answered Nov 18, 2011 at 14:21

Jan Korous

6644 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Berkowski Over a year ago

Yes, welcome! Have a +1 worth 10 rep to keep you coming back for more.

Michael Berkowski · Accepted Answer · 2011-11-18 14:12:31Z

1

Can you not simply query for counts, GROUP BY class? This will return all classes, including your custom ones.

SELECT class, COUNT(*) FROM table GROUP BY class;

Or simply DISTINCT class

SELECT DISTINCT class FROM table

answered Nov 18, 2011 at 14:12

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Collectives™ on Stack Overflow

check for appearance of multiple strings in mysql

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related