SQL query is not working properly(numbers)

Question

I am developing php script with MySQL . I stored genre datas as "1, 2 ,3"(IDs)

Then I have a problem with searching data.

My Data table is:

metas table

-------------------
meta_id,meta_genres
--------------------
|1     |1,2,3      |

|2     |10,3,4     |

|3     |12,3,5     |

-------------------

my query is

SELECT * FROM metas WHERE meta_genres LIKE '%1%'

then return ids: 1,2,3

I want to it returns just 1.

How can I fix it ?

Best Regards.

@Gowri : nope...this would fail if 1 is in last..like 4,3,1! — NoobEditor
– NoobEditor, Commented Oct 6, 2014 at 9:33
You question isn't that precise, so I'll assume that you want to get the ID (tell me if I guess wrong) of the meta_genre which fit your need. So you should just change the SQL query in SELECT meta_id FROM metas WHERE meta_genre LIKE '%1%'; — Anwar
– Anwar, Commented Oct 6, 2014 at 9:33
If you can't normalize your tables properly, use FIND_IN_SET() but you should really normalize for better efficiency — Mark Baker
– Mark Baker, Commented Oct 6, 2014 at 9:33
I would advise you to bring your database up to the 3rd normalize form! ->en.wikipedia.org/wiki/Database_normalization — Rizier123
– Rizier123, Commented Oct 6, 2014 at 9:34
SELECT * FROM metas WHERE meta_genres LIKE '%1,% OR meta_genres like '%,1% should do the trick — Azrael
– Azrael, Commented Oct 6, 2014 at 9:35

Rimas · Accepted Answer · 2014-10-06 10:04:06Z

4

Use FIND_IN_SET(str,strlist) function:

SELECT *
 FROM metas
 WHERE FIND_IN_SET(1, meta_genres)

EDIT: if there are spaces before commas (e.g. 1 , 2 , 3) in meta_genres then above query can return empty set. To work around:

SELECT *
 FROM metas
 WHERE FIND_IN_SET(1, REPLACE(meta_genres, ' ', ''));

edited Oct 6, 2014 at 10:04

answered Oct 6, 2014 at 9:35

Rimas

6,0332 gold badges28 silver badges40 bronze badges

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

1 Comment

Ömer ASLAN Over a year ago

Dear Rimas, Your answer seems to be good. However it returns nothing. My query is true but I don't understand

Ömer ASLAN · Accepted Answer · 2014-10-06 10:03:50Z

1

You can also use REGEXP to find only separated 1 in set input

SELECT * FROM metas WHERE meta_genres REGEXP '[[:<:]]1[[:>:]]'

edited Oct 6, 2014 at 10:03

Ömer ASLAN

1393 silver badges16 bronze badges

answered Oct 6, 2014 at 9:37

Girish

12.2k3 gold badges37 silver badges54 bronze badges

Comments

Ziomek Ziomski · Accepted Answer · 2014-10-06 09:35:26Z

-1

You Will never retrive just 1, thats because the numbers after 1 is part of 2,3 string. So the query does not look at these numbers as individual numbers but simply as a whole string.....

answered Oct 6, 2014 at 9:35

Ziomek Ziomski

636 bronze badges

Collectives™ on Stack Overflow

SQL query is not working properly(numbers)

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related