LIKE Condition in PHP Not Work correctly

Question

i have a row in my database with name "active_sizes" and i want filter my website items by size, for this, i use LIKE Condition in php :

AND active_sizes LIKE '%" . $_GET['size'] . "%'

but by using this code i have problem

for example when $_GET['size']=7.0 this code shows items that active_sizes=17.0

my active_sizes value looks like 17.0,5.0,6.5,7.5,,

thanks

Answer 1

Using comma-separated values in a single field in a database is indicative of bad design. You should normalize things, and have a seperate "item_sizes" table. As it stands now, you need a VERY ugly where clause to handle such sub-string mismatches:

$s = (intval)$_GET['size'];

... WHERE (active_sizes = $s)   // the only value in the field
      OR (active_sizes LIKE '$s%,') // at the beginning of the field
      OR (active_sizes LIKE '%,$s,%')  // in the middle of the field
      OR (active_sizes LIKE '%,$s') // at the end of the field

Or, if you normalized things properly and had these individual values in their own child table:

WHERE (active_sizes_child.size = $s)

I know which one I'd choose to go with...

You don't state which DB you're using, but if you're in MySQL, you can temporarily accomplish the same thing with

WHERE find_in_set($s, active_sizes)

at the cost of losing portability. Relevant docs here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set