[RESOLVED] Categories System

[chrziz]

I have been trying to figure out an effective way of creating a categories system. I am trying to figure out how to create one with unlimited sub categories, sub sub cats, sub sub sub cats, etc.

I've tried a few different methods but cannot get any to work properly. Anyone have any suggestions?

[bpat1434]

With what, a mySQL table?

CREATE TABLE categories(
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(255) NOT NULL
) ENGINE = MYISAM

CREATE TABLE subcats(
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `pid` INT(11) NOT NULL,
  `sid` int(11) NOT NULL DEFAULT '0',
  `subcat` VARCHAR(255) NOT NULL
) ENGINE = MYISAM

Then, when you set up your top level, no levels above them, categories in the categories table, and all sub-categories (even sub categoreis of sub categoreis) in the subcats table.

For example:

categories:
	+------+----------------------+
	|  id  |       category       |
	+------+----------------------+
	|  1   |  General Information |
	|  2   | Personal Photographs |
	+------+----------------------+

subcats:
	+------+-------+-------+-------------------------+
	|  id  |  pid  |  sid  |       subcategory       |
	+------+-------+-------+-------------------------+
	|  1   |   1   |   0   |  About Me               |
	|  2   |   1   |   0   |  Biometric Information  |
	|  3   |   1   |   0   |  Schooling              |
	|  4   |   1   |   3   |  Elementary             |
	|  5   |   1   |   3   |  Intermediate           |
	|  6   |   1   |   3   |  High                   |
	|  7   |   1   |   3   |  College/University     |
	|  8   |   2   |   0   |  Picnics                |
	|  9   |   2   |   8   |  Family Picnics         |
	|  10  |   2   |   8   |  Fourth of July Picnics |
	|  11  |   2   |   10  |  July 4 2005            |
	+------+-------+-------+-------------------------+

Now you have the ability to have unlimited sub categories (as proven here) and a strict set of general categories.

In the subcats table the sid refers back to the ID of the subcats table. So if sid is not 0, then that specific sub-category is a sub-category of a sub-category.

To walk out the above inormation:

• General Information

  • About Me

  • Biometric Information

  • Schooling

    • Elementary

    • Intermediate

    • High

    • College / University

• Personal Photographs

  • Picnics

    • Family Picnics

    • Fourth of July Picnics

      • July 4 2005

[chrziz]

Thanks a lot for the help.

After I have the tables created, I'm having difficulty writing a loop that works. I am still getting oriented with PHP.

So I would have somthing like this:

$cat_query = "SELECT * FROM categories";
$cat_result = mysql_query($cat_query);

while ($row = mysql_fetch_object($cat_result)) {
$sub_cat_query = "SELECT * FROM subcategores WHERE pid = '$row->id' AND sid = '0'";
$sub_cat_result = mysql_query($sub_cat_query);
while ($row = mysql_fetch_object($sub_cat_result)) {
// Continues....
}
}

How can I create an effective loop to keep going through the categories until it gets to the bottom?

Thanks in advance!

[bpat1434]

You can do it in one query:

SELECT a.*, b.*, c.*
FROM categories
INNER JOIN subcategories b
ON a.id=b.pid
INNER JOIN subcategories c
ON b.id=c.sid
ORDER BY a.id ASC

Then loop through that.

[chrziz]

Sweet! Thank you so much for your help.

[bpat1434]

NO problem!! 🙂