Multiple "Categories"

[usernot]

Hello,

I have a database that lists business names and the corresponding contact information associated with each business (address, phone number, etc...). In the database, there is a CATEGORY column where the appropriate business category for that particular business is entered

Currently I can select from a dropdown list of categories (pulled from the CATEGORY column in the database) and then the search will pull up a results pages that list all the businesses in that particular category. My current PHP code works just fine for one category, however, there are some businesses that would like to advertise their business in several different categories. Any suggestions as to the easiest way to do this? Here is my form script for the dropdown menu...

<?
mysql_select_db("database");

$sql = "SELECT DISTINCT category FROM table ORDER BY category";
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());

echo '<form method="POST" action="results.php">';
echo '<tr><td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Or Select A Category:</td>';
echo '<td><select name="categorysearch" tabindex="2">';

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';
}
echo '</select></td></tr>';
echo '<tr><td colspan="2"><input type="Submit" name="Submit" value="Submit"></td></tr></form></table>';
?>

Do I need to add another column to the database, such as CATEGORY2? How could I integrate something like that into the code I already have without any repeats of category type?

Thanks in advance!

[Lars_Berg]

A typical many to many relation.

You need three tables

categories

categoryID (pk)
categoryName
...

companies

companyID (pk)
companyName
....

companyCategories

companyID (fk to companies) (pk)
categoryID (fk to categories) (pK)

For each category a company belongs you store a row in the companyCategories table.

You shouldn't use the category2 approach. What if you would need 3 categories? With the above design you can have as many categories as you like for a company.

[PhiSYS]

Sometimes I workaround with serialize() categories field, but Lars Berg's is much better.

[usernot]

Lars,

Thanks for the reply. I guess your solution is a little beyond what I am capable of as a newbie to PHP. Plus, it sounds as if I would have to redo the database (which is already complete). Is there any other option available that would maintain just the single database table?

Thanks