phpmyadmin Question posted in
The official documentation can be found here.

SQL IN Clause only returning rows with first match in comma separated list of IDs – Phpmyadmin

I have 5 users which have a column ‘shop_access’ (which is a list of shop IDs eg: 1,2,3,4)

I am trying to get all users from the DB which have a shop ID (eg. 2) in their shop_access

Current Query:
SELECT * FROM users WHERE '2' IN (shop_access)

BUT, it only returns users which have shop_access starting with the number 2.

E.g

  • User 1 manages shops 1,2,3
  • User 2 manages shops 2,4,5
  • User 3 manages shops 1,3,4
  • User 4 manages shops 2,3

The only one which will be returned when running the IN Clause is User 2 and User 4.

User 1 is ignored (which it shouldn’t as it has number 2 in the list) as it does not start with the number 2.

I’m not in a position to currently go back and change the way this is set up, eg convert it to JSON and handle it with PHP first, so if someone can try to make this work without having to change the column data (shop_access) that would be ideal.

Tags:

3

Answers


  1. 0

    A portable solution is to use like:

    where concat(',', shop, ',') like '%,2,%'

    Or if the value to search for is given as a parameter:

    where concat(',', shop, ',') like concat('%,', ?, ',%')

    Depending on your database, there may be neater options available. In MuSQL:

    where find_in_set('2', shop)

    That said, I would highly recommend fixing your data model. Storing CSV data in a database defeats the purpose of a relational database in many ways. You should have a separate table to store the user/shop relations, which each tuple on a separate row. Recommended reading: Is storing a delimited list in a database column really that bad?.

    Login or Signup to reply.
  2. 0

    Also, you might want to consider using REGEXP here for an option:

    SELECT *
FROM users
WHERE shop_access REGEXP '[[:<:]]2[[:>:]]';
                            -- [[:<:]] and [[:>:]] are word boundaries
    Login or Signup to reply.
  3. 0 
    SELECT * FROM users WHERE (shop_access = 2) OR (shop_access LIKE "2,%" OR shop_access LIKE "%,2,%" OR shop_access LIKE "%,2")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search