Querying a self relationship category table

Question

I am trying to optimize my php code for the following table

Create table categories (
    cat_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id Int UNSIGNED,
    cat_name Varchar(50) NOT NULL,
 Primary Key (cat_id)) ENGINE = InnoDB;

To get all categories and subcategories i use one query for querying only parent categories and then issue an individual query to get the sub categories.

here is my code for listing the all main categories

$result = mysql_query("SELECT cat_id, cat_name FROM categories WHERE parent_id IS NULL");

To list individual subcategories of each category i use following query

$sub_result = mysql_query ("SELECT cat_id, cat_name FROM categories WHERE parent_id=$cat_id");

I have 30 categories so the above issues 30 queries on each page, I am trying to minimize the number of queries. Any hint?

Thanks

Answer 1

The article on managing hierarchical data might give you some inspiration. Don't be discouraged by the fact that it's on MySQL site, the main ideas are the same.

Answer 2

This is tough in MySQL because of lack of support for recursive queries.

There are several alternative ways to structure your table so you can query all descendants of a hierarchy in a single query. But they involve changing the way you store data.

See my presentation Models for Hierarchical Data with SQL and PHP for examples demonstrating several solutions.