-1

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Frank Helligberger
  • 46
  • 1
  • 1
  • 10
  • 7
    http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Nov 08 '16 at 09:51
  • Comma separated values that you need to search are a very bad idea. Is it too late to change your design to avoid this problem? – PaulF Nov 08 '16 at 09:57

3 Answers3

2

This is a major issue with storing comma separated fields.

MySQL does have a function to allow searching for these, and this will be a lot more reliable than trying to rely on using LIKE. But as it still cannot effectively use any index it will not be fast.

SELECT *
FROM offers
WHERE FIND_IN_SET('1002', in_stores)

Far better to redesign your database to use an extra table with a row for each value for each id.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • I understand the better way of doing things. Just out of curiosity will this function always work the way I want it unlike when using `LIKE` with numbers greater than 9999? Despite the slowness? It catches the field as an array and that searches it? – Frank Helligberger Nov 08 '16 at 11:52
  • 1
    Not sure how it works under the covers, but it is designed to find values in comma separated lists reliably. The general problem comes when the list is not comma separated, or uses a comma AND a space. http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set – Kickstart Nov 08 '16 at 11:54
  • Yeah. But in my case I strictly use comma separated values with no possible way of adding a space. I am chosing you answer as the correct one since it is the exact answer to what I asked. – Frank Helligberger Nov 08 '16 at 12:02
1

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.

cb0
  • 8,415
  • 9
  • 52
  • 80
-1

I think you should be using the MySQL LIKE operator. It searches the database column for strings specified in your query.

Try this query:

$sql = "SELECT * FROM offers WHERE in_store LIKE  '%1002%'";

Check this link for more explanation: W3Schools - SQL LIKE Operator

I think that should do the trick.

Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
pamekar
  • 729
  • 7
  • 10