2

I have a column called papers in a table called products that has values like this :

"1,2,3,4,5,6" and in other row it has like this "3,4,5"

I want to launch this query with php,

SELECT * from products where papers like "%5%" AND papers like "%3%" and papers like "%4%"

TL;DR

I want to select a column that has multiple strings, ignoring the order

GMB
  • 216,147
  • 25
  • 84
  • 135
sysdevs
  • 363
  • 1
  • 9
  • 3
    [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) is also worth a read. – Nigel Ren Oct 12 '20 at 19:50

1 Answers1

2

In MySQL, you can use find_in_set():

select * 
from products 
where find_in_set('3', papers) and find_in_set('4', papers) and find_in_set('5', papers)

Note that your question suggests a flawed database design. You should not be storing multiple integer values in a single string column. Instead, you should have each integer value in a separate row, probably in another table, that would refer to the main table through a foreign key constraint. Recommended reading: Is storing a delimited list in a database column really that bad?.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • emm I could have done it with the same way I sent my code, but I want to do it dynamically with php, without repeating 'and' – sysdevs Oct 12 '20 at 19:50
  • 1
    @MMoh: (1) your code is not safe, as it would match `3` against a string like `1,2,31`. `find_in_set()` avoids that trap. (2) I don't think you can do this with a single condition. – GMB Oct 12 '20 at 19:52
  • they actually are not numbers, ```SELECT * from products where find_in_set('agreement_to_sell', papers) AND find_in_set('land_register', papers) AND find_in_set('re_promotion', papers)``` here is my real query, just wanted to simplify things – sysdevs Oct 12 '20 at 19:54
  • if you want to do it "dynamically with php" then repeating the and is easy. if it is not, then this is really a php question and you need to show your php code – ysth Oct 12 '20 at 19:55
  • @GMB yours is a better solution than mine tbh, I will try to use it in my code, Regards. – sysdevs Oct 12 '20 at 20:03