trouble returning both partial and exact matches

Question

I have a table like this:

product_id    attribute_id
2             7,8
1             2,7
3             7

I also have a variable called $search_ids which contains values to search for.

If $search_ids has a value of 7 , I want it to return all 3 rows, but if it has a value of 2,7 or 7,8 I then want to return that row only.

I tried the following where $search_ids has a value of 7, but this doesn't return the second row! And if I change the row's value from 2,7 to 7,2 then it returns that row also!

So right now the following query:

$q = "SELECT product_id FROM product_attributes 
    WHERE attribute_id IN ('$search_ids')
    OR attribute_id IN ($search_ids)

returns

2
3

instead of

2
1
3

product_attributes is a table, attribute_ids is varchar, yes. — Ollicca Mindstorm, Mar 15 '14 at 18:59
you should consider redesigning your table. You could use `WHERE attribute_id LIKE '%$search_ids%'` but that will not work when you have bigger numbers like `10` which would be returned when you search for `0` or `1`. — tchow002, Mar 15 '14 at 19:03
I know this isn't best practice but having a separate row for each attribute_id doesn't work for me, there is no way for me to filter it the way I want that way. This is perfect, but I need it to fix the bug I have. — Ollicca Mindstorm, Mar 15 '14 at 19:05
Try this[This may help you a little...] [1]: http://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string — codelover, Mar 15 '14 at 19:35

tchow002 · Answer 1 · 2014-03-15T19:12:04.423

0

Try this:

$q = "SELECT product_id FROM product_attributes 
      WHERE attribute_id REGEXP '(^|,)($search_ids)(,|$)'";

This will match on rows where product_id matches $search_ids if it is exactly a match, at the start of a comma separated list, in the middle of a comma separated list, or at the end of a comma separated list.

edited Mar 15 '14 at 19:12

answered Mar 15 '14 at 19:07

tchow002

1,068
6
8

Does this work for `$search_ids = '2,7'` and one row has `attribute_id '2,6,7'` ? – kcsoft Mar 15 '14 at 19:21
No it does not. A pure SQL solution for splitting and searching is more difficult and not as simple as this. But your PHP solution for building the query should work nicely. – tchow002 Mar 15 '14 at 19:24
kcsoft is right, it should work for `2,6,7` as well. – Ollicca Mindstorm Mar 15 '14 at 19:26

kcsoft · Accepted Answer · 2014-03-15T19:29:29.310

0

I would do it this way:

$search_ids = '2,7'; //dummy data

$q = '';
$ids = explode(',', $search_ids);
foreach ($ids as $id) {
    $q .= (strlen($q) == 0)?'':' AND';
    $q .= ' FIND_IN_SET(\''.$id.'\', attribute_id) > 0';
}

$q = 'SELECT product_id FROM product_attributes WHERE' . $q;

edited Mar 15 '14 at 19:29

answered Mar 15 '14 at 19:19

kcsoft

2,917
19
14

Now that's some pro stuff! Seems to be working, but I will need to test it a bit more, because before I thought Tanvir's asnwer worked but you spotted the mistake. – Ollicca Mindstorm Mar 15 '14 at 19:31

trouble returning both partial and exact matches

2 Answers2