Subcategories MySql and php

Question

Hi,

I'm having troubles with subcategories.

I want to display this on my page with 1 sql query:

• Category 1:
  • Sub cat 1
  • Sub cat 2
  • ...
  • Sub cat n
• Category 2:
  • Sub cat 1
  • Sub cat 2
  • ...
  • Sub cat n
• Category 3:
  • Sub cat 1
  • Sub cat 2
  • ...
  • Sub cat n
• ...

Table schema:

• categories
  • id | catname | description
• subcategory
  • id | parent_id | catname | description

Currently I'm using a query for the categories and in the PHP while loop another query for subcategories, but this gives me A LOT of queries on one page

Answer 1

We'd need to see your table schema to offer a proper critique, but what you could do is join the subcategories to the categories in one query, sorted by category, subcategory. Whenever you see a new category, you know it's time to finish the current subcategory list and start a new category.

Answer 2

Without seeing the tables, you probably need something like:

SELECT category.name, subcategory.name
FROM categories
LEFT JOIN subcategory
  ON subcategory.category_id = category.id
ORDER BY category.name, subcategory.name

Answer 3

There is never any need for two tables for tree structures, a standard tree-ing table schema could be:

categories
id | catname | description | parent | lineage

Lineage would contain the path back to the root category e.g. '0,2,5,6' parent would contain 6

Then for your question you can SELECT all from that table ORDER BY LENGTH(lineage) ASC

after you have your resultset from the db you index them by their id

$categories = array();
while($row = mysql_fetch_assoc($result)){
   $categories[$row['id']] = $row;
}

now when you loop through them in your html you can check to see if the current category has any child categories.

Lineage is used to find all categories that are descendants of a certain category:

SELECT * FROM categories WHERE lineage REGEXP '[[:<:]]5[[:>:]]'

This'll get any categories with 5 in their lineage.

Answer 4

You should get all the categories and all the subcategories. Something like this:

$sql = '
    SELECT * FROM categories ORDER BY id ASC
';
$result = mysql_query($sql);

$categories = array();

while ($row = mysql_fetch_array($result)) {
    $categories[$row['id']] = $row;
}

// get the subcategories
$sql = '
    SELECT * FROM subcategories ORDER BY id ASC
';
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
    $categories[$row['parent_id']]['subcategories'][] = $row;
}

print_r($categories);

This would put them in a nice array structure you can do whatever you want in PHP. And it's only two queries.

Answer 5

Similar to Zed's answer, but selecting from table subcategory and then joining categories:

SELECT categories.name, subcategory.name
FROM subcategory
JOIN categories
  ON category.id = subcategory.category_id
ORDER BY category.id, subcategory.id

This will return pairs of values like (category 1, sub cat 1), (category 1, sub cat 2) ... For further columns add them to the statement and make aliases when appropriate like

SELECT categories.name AS maincat_name, 
    subcategory.name AS subcat_name, subcategory.id AS subcat_id