CODEWITHSUNDEEP
phpmysql
m1a2x3x7
m1a2x3x7

Reputation: 25

MySQL SELECT women not men

I have a field in my database called categories which holds the categories of a clothing store topics (New Arrival, Top Sellers, Men, Women, Kids etc)

I have one record that has: New Arrival, Men I have a second record that has: New Arrival, Women

How could I query the database to select just women or just men?

I've tired this: SELECT * FROM products WHERE categories RLIKE [[:<:]]Men[[:>:]]'

This does work for men but it doesn't work for women.

Upvotes: 0

Views: 902

Answers (5)

Claudio
Claudio

Reputation: 39

Since you don´t have problems selecting 'Women', you can solve it in case of Men by using a query like this:

SELECT * FROM products WHERE categories LIKE '% Men';

Just put a space before the letter 'M'.

Upvotes: -1

Zanrok
Zanrok

Reputation: 297

You might want to just simplify your approach.

SELECT * FROM products WHERE categories = 'Men';
SELECT * FROM products WHERE categories = 'Women';

Since "Men" is apart of "WoMEN" this maybe the reason your sql is having issues?

However, since it was brought to my attention your DB might contain a longer string you might be able to do this as well.

SELECT * FROM products WHERE categories = 'New Arrival, Men';
SELECT * FROM products WHERE categories = 'New Arrival, Women';

Upvotes: -1

jcho360
jcho360

Reputation: 3759

you can use the find_in_set function, take a look to this example

mysql> select * from fruits;
+-------------------------------+
| fruit                         |
+-------------------------------+
| banana, apple, orange, grapes |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from fruits where find_in_set('banana',fruit);
+-------------------------------+
| fruit                         |
+-------------------------------+
| banana, apple, orange, grapes |
+-------------------------------+
1 row in set (0.00 sec)

So try something like:

SELECT * FROM products WHERE categories where find_in_set('men',categories)'

be sure that the word after the , is exactly the same, without a extra space

Upvotes: 1

Ruben
Ruben

Reputation: 9186

just use this:

SELECT * FROM products WHERE categories LIKE 'Men%';
SELECT * FROM products WHERE categories LIKE 'Women%';

only use the right %

Upvotes: 1

Naftali
Naftali

Reputation: 146310

Well if you are actually storing the words "Men" or "Women", your database is set up incorrectly

You should be using a linked table let us say categories to link products to categories.

Then you can do something like:

SELECT * FROM products p
JOIN categoriesToProducts ctp ON ctp.productId = p.productId
JOIN categories c ON c.categoryId = ctp.categoryId
WHERE c.categoryName = "Men";

Upvotes: 4

Related Questions