Data sort problem

Trying to sort data by grouping

[fullfocus]

Hello:
I'm trying to sort my sub-categories within each main category. The sort should look like this:
Hardware
- Graphics Card
- Printers
Software
- Simulation
- Action Games
DVD Movies
- Comedy
- Drama

My table data looks like this:
category_id category_name parent_id
1 Hardware 0
2 Software 0
3 DVD Movies 0

4 Graphics Card 1
5 Printers 1
6 Simulation 2
7 Action Games 2
8 Comdey 3
9 Drama 3

The categories with parent_id = 0 represent the top level category (main category).
The categories with category_id = 4,5,6,7,8,9 are set to the appropriate parent category which is set to the category_id of the parent_id of 0.

So, category_id = 8 (Comedy) belongs to the main category of DVD Movies which has a category_id = 3.

I have a form which populates the tables. If the parent category field is left blank, the parent_id is set to 0 else the parent_id is set to the category_id of where the new category belongs. By doing it this way, I can have multiple levels of sub-categories within a main category.

When I run my script, my results show either all the data in the table or none at all. I'm not sure what to do to get this to work.

Can someone help me out? Thank you in advance.

Here is my script (this one returns nothing):
[CODE]
<?php

$conn = mysql_connect("localhost", "root", "password");
mysql_select_db("cart",$conn);

$query = "SELECT * FROM category WHERE parent_id = 0 AND parent_id = category_id";
$result = mysql_query($query, $conn) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$category_id = $row['category_id'];
$category_name = $row['category_name'];
$parent_id = $row['parent_id'];
echo $category_id;
echo $category_name;
echo $parent_id;
}
?>
[CODE]

[edited by: coopster at 12:38 pm (utc) on July 1, 2006]
[edit reason] generalized password [/edit]

[the_nerd]

looking at your table there is no record where parent_id is the same as category_id, so the database query is correct to return no values. Keep in mind that the query checks one record at a time.

nerd

[the_nerd]

forgot something: I'd use phpmyadmin or something similar so you can enter your query and see the results at once - this way you save the time needed to code a lot of debug output stuff in php.

[fullfocus]

Hello:

Thank you for the reply.

In the table, if you look at Graphics Card and Printers they both have a parent_id = 1. These two items should fall under the category of Hardware which has a category_id = 1. Hardware is the main category, Graphics Card and Printers are sub-categories for Hardware.

What I'm trying to accomplish is to develop a category tree. I want the main categories to appear at the top as links. Then, when someone clicks a category, the sub-categories will appear for that category. I also want to make the tables flexible to allow for multiple levels of sub-categories.

Maybe I've gone about the wrong way in designing the table. If you have any ideas on that, I'd love to hear some.

Thanks again.