CODEWITHSUNDEEP
phpmysqlhierarchical-datarecursive-query
Jason
Jason

Reputation: 105

Querying a self relationship category table

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

Upvotes: 1

Views: 246

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562991

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.

Upvotes: 0

Adam Byrtek
Adam Byrtek

Reputation: 12212

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.

Upvotes: 1

Related Questions