Comma Separated List in MySQL field

[squiggerz]

say I have a list of categories in a field, and a page that is supposed to pull from the db based on a category. Is there any way I can do a query where it searches the contents of that field for the category in question?

 

like SELECT FROM table WHERE category = $category  but have it search the category field (the comma separated list for each entry) for the $category var

 

 

[Barand]

The answer lies in correctly normalising your data and not holding data as comma-separated lists

 

[pre]

instead of

 

+------+---------+------------------+

|  id  |  name  |  categories      |

+------+---------+------------------+

|  1  |  widget |  12, 20, 15      |

|  2  |  gizmo  |  12, 14, 16      |

+------+---------+------------------+

 

you should have

 

+------+---------+          +------+---------+

|  id  |  name  |          |  id  |  cat  |

+------+---------+          +------+---------+

|  1  |  widget |          |  1  |    12  |

|  2  |  gizmo  |          |  1  |    20  |

+------+---------+          |  1  |    15  |

                            |  2  |    12  |

                            |  2  |    14  |

                            |  2  |    16  |

                            +------+---------+

[/pre]

 

then it's easy