nested loop problem

Hi

I have tables named categories and subcategories. In categories table I have categoryid and categoryname fields.

In subcategories have categoryid, subcatid and subcategoryname fields.

On categories page, I list my categories like

Cat 1
Cat 2
Cat 3

I want on click on category name to display my subcategories only for clicked category like this:

(Lets asume that category 2 was clicked)

Cat 1
Cat 2
---Cat 2 SubCat 1
---Cat 2 SubCat 2
Cat 3

(If category 1 was clicked)

Cat 1
---Cat 1 SubCat 1
---Cat 1 SubCat 2
Cat 2
Cat 3

Anyone can help me to sort this out??

Regards, Zoreli

 

Yup..

1: you only need 1 table with 3 basic columns
table: category
[cat_id, cat_name, cat_parent_id]
if row has cat_parent_id then it is a sub category of parent category specified in this column.

2: populate the table with values (cat_parent_id of 0 for main cat's)

3: do a tiny bit of sql and php

$cat_id = 0;
if(isset($_GET['cat']) && intval(trim($_GET['cat']))) {
$cat_id = intval(trim($_GET['cat']));
}
/*sql
SELECT * FROM category WHERE cat_parent_id=$cat_id;
*/
foreach($results as $rowindex => $values) {
 echo '<a href="?cat='.$values['cat_id'].'">'.$values['cat_name'].'</a><br />';
}

PHP:

that should cover the basics of it

 

you cant do this without Ajax

 

Thanks for your answers. My title is wrong, nested loop wont help me here.
What I need is join and (or) union. Currently I am trying this code, without much sucess:
=====================================================
$query="SELECT products_subcategories.subcatid, products_subcategories.subcategoryname
FROM products_subcategories
INNER JOIN products_categories ON products_subcategories.catid = products_categories.catid
WHERE products_subcategories.catid = '3'
UNION
SELECT products_categories.catid, products_categories.categoryname
FROM products_categories
WHERE products_categories.catid = products_subcategories.catid";
=====================================================

If anyone can help me to sort this out I will deeply appreciate it. However, If I manage to solve it, I will post the solutuion here, so maybe it will be prevent someone else not loose 14 hours for 10 lines of code like I did.

Buh...

Regards, Zoreli