Multiple categories - storing and querying

[simonb]

I have a table of categories and another table of products. One record in the products table must be able to contain up to 3 of these categories. How should this be stored in the products table and what is the SQL for subsequent searches of the products table such that the product will be selected with just one of the categories being 'true'??

[drawmack]

this is a many to many relationship and should be handled with a link table

prod_to_cat
    id --> auto_increment
    prod_id --> bigint
    cat_id --> bigint

Then to look up products in a category just to this

<?php
$sql = "SELECT prod_id FROM prod_to_cat WHERE cat_id = $cat_id";
$query = mysql_query($sql);
?>