Organizing Categories

[Kay1021]

I have a bit of a puzzle I'm trying to figure out....I was wondering if anyone might be able to help me sort it out....or in the direction.

 

I have 11 main categories and about 185 sub categories that may appear in one or more main category.

 

In the sub categories are companies.

 

The user will enter the site and search by postal code. I want to only show the categories & sub categories that have a company with that postal code.

 

I'm just having trouble figuring out how i should set up my databases.

 

Any guidance or help would be greatly appreciated... I hope this made some sense.

 

Thanks.

[DarkWater]

categories:

category_id int not null auto_incrementing

category_name varchar(50) not null

 

sub_cats:

subcategory_id int not null auto_incrementing

subcategory_name varchar(50) not null

any other info

 

cat_relation:

category_id int not null

subcategory_id int not null

 

 

To insert a sub category into multiple categories, you make several rows in the cat_relation table using  mysql_insert_id to get the last inserted ID when making the category. =)

[Kay1021]

ok i have the category table with my 11 categories and my sub category table

 

In the cat_relation table does anything go into those two fields...or how do you get the other info into them

 

and im a little confused about the last part

[Daniel0]

It would hold parent-child information.

 

Instead of DarkWater's suggested database layout, I'd just remove the sub_cats table and have both category_id and subcategory_id in the cat_relation table reference an entry in the categories table.

[Kay1021]

thanks for the help

 

i just had a question....when you like the select * from statements how do you get that information to output...

 

do you use echo or need a an if or while statement?

[Daniel0]

You'd loop through it using e.g. a while loop.

 

Example:

 

<?php
$db = new PDO('mysql:dbname=test;host=localhost', 'root', '');

$result = $db->query('something');

while($row = $result->fetch()) {
// do stuff
}

// or

foreach($result->fetchAll() as $row) {
// do stuff
}
?>

 

I used PDO here.