CODEWITHSUNDEEP
phpsql
moumouh206
moumouh206

Reputation: 113

Search in column that has array(value1,value2,...) in sql table

I have a sql table called "Offres"
inside this table a column named "regions" has array in it (value1, value2, value3,...)
row1 :"35,2,15,69,98"
row2 :"7,9,15,5,69"
row3 :"7,3,45,5,6"
how can I search rows which has multiple value in same time
for exp : I want to search rows which has 15,69 in it
the result should display row1 and row2
thanks

enter image description here

Upvotes: 1

Views: 9205

Answers (5)

DrCopyPaste
DrCopyPaste

Reputation: 4117

For the sake of completeness I am posting this as an answer, too, so it does not get lost in the huge discussion in the comments ;)

First of all, if you want to have multiple values in one column in SQL the set-column is the way to go. See for example here and here. But if you absolutely cannot get around having multiple values in one column and you can guarantee, that they will always occur in the same pattern (comma-separated, no spaces), then you can search for rows that contain 2 specific such values you can do it like this (according to your example)

SELECT * FROM Offres WHERE
  (
      regions = '15' /*value is the only one in that row, obviously this is not necessary if you look for 2 values at once, but if you would only look for one value at once you MUST include it ;)*/
    OR regions LIKE '%,15' /*value is the last value in that row*/
    OR regions LIKE '15,%' /*value is the first value in that row*/
    OR regions LIKE '%,15,%' /*value is a value somewhere in between other values in that row*/
  )
  AND
  (
      regions = '69' /*value is the only one in that row, obviously this is not necessary if you look for 2 values at once, but if you would only look for one value at once you MUST include it ;)*/
    OR regions LIKE '%,69' /*value is the last value in that row*/
    OR regions LIKE '69,%' /*value is the first value in that row*/
    OR regions LIKE '%,69,%' /*value is a value somewhere in between other values in that row*/
  )

You can also try that out in this fiddle.

Upvotes: -1

Deepanshu Goyal
Deepanshu Goyal

Reputation: 2813

for exp : I want to search rows which has 15,69 in it

if you can add spaces before and after your values, then it might be easy check fiddle

like 

row1 :' 690 , 6969 , 069 , 69069 '
row2 :' 69 , 69 , 69 , 69 , 69 '
row3 :' 6969 , 69 , 69069 '
row4 :' 069 , 69069 '
row5 :' 69 '

    SELECT * FROM Offres WHERE regions LIKE regions LIKE '% 69 %';

Upvotes: -2

Daniel
Daniel

Reputation: 303

You can search for elements within such lists with FIND_IN_SET(). If you need to find multiple entries within the rows, you need multiple selects.

But: Good database structures would avoid all this and have only one entry per row instead of lists

Upvotes: 0

Extrakun
Extrakun

Reputation: 19305

If you intend for a field to be searchable via SQL, store it as multiple rows and do not serialize it.

If some reasons, you have to, you may have to extract out each row, deserialize it (for your case, with explode(), and match it singularly, which takes up lots of processing power.

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Never, never, never store multiple values in one column!

As you see now this will only result in problems. Please normalize your DB structure first like this

Offers
id   region
1    35
1    2
1    15
...
2    7
2    9
2    15
...

Upvotes: 5

Related Questions