Frank Helligberger Frank Helligberger - 24 days ago 13
MySQL Question

Search for a string in an array through SQL

I have a table named 'offers' that has a column

in_stores
. It contains the ids of the stores that this offer is available in. Graphically this table looks like this:

id | title | in_stores
1 | Shoes | 1002,1003
2 | Gloves | 1020,1011
3 | Shades | 1002,1009
4 | Hats | 1010,1002
5 | Shoes | 1220
6 | Shirts | 1010
7 | Hats | 1002


Each value in
in_stores
is saved with the
implode()
function through PHP.

My question:
How to select in a single
mysqli
query all the offers that are available in a store with id 1002. In this example the query should return offers with id 1,3,4,7. I guess I should use something like explode() first to get the results in an array and than in_array() to search for the specific id in it but all those functions are unavailable within SQL.

cb0 cb0
Answer

You "could" do it with a LIKE Query, but this is not recommended.

SELECT * FROM offers WHERE in_stores LIKE "%1002%".

As long as you don't have values bigger than 9999 this will work. BUT when one of your stores has the id 11002 of 99991002 it will also return these unwanted values.

What you should do is transform your mysql table to a have a second table storeLocations or sth. else. This should only have the properties offer_id and store_id. It will transform your data to:

`offers`
id | title   
1  | Shoes    
2  | Gloves  
3  | Shades   
4  | Hats    
5  | Shoes   
6  | Shirts  
7  | Hats    

`storeLocations`
offer_id, store_id
1        | 1002
1        | 1003
2        | 1020
2        | 1011
3        | 1002
3        | 1009
4        | 1010
4        | 1002
5        | 1220
6        | 1010
7        | 1002

Then you can select from it like

SELECT * FROM offers AS o 
   LEFT JOIN storeLocations as l ON (o.id=l.offer_id) 
   WHERE l.store_id = 1002;  

Now when you insert data you don't have to use implode but insert as many rows into storeLocations as there are store_id's for that specific item.

For more info on that topic have a look here.

Comments