Sorting data

focus310

Hello:

I have a table with three columns. They are:
category_id PK auto_increment
category_name
parent_id

I need to sort that data so sub-categories appear underneath the correct category.

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

4 Printers 1
5 Memory 1
6 Drama 3
7 Sci Fi 3
8 Simulation 2
9 Educational 2

The root category is parent_id = 0.
The subcategories is parent_id = category_id.

The category list should appear like this:
Hardware
- Printers
- Memory
Software
- Simulation
- Educational
DVD Movies
- Drama
- Sci Fi

You can see that the categories with parent_id = 1 is the same as category_id =1.

I am having a hard time creating the category list to appear like the one above.

Can someone help me out?
Thanks in advance.

bogu

First SELECT the main categories, then for each main category_id extract the subcategories ...

When u create your db tables u first need to have in mind how u are going to work with them ...

focus310

Hi,

Thanks for the reply.

I'm able to grab all the main categories without a problem. It's when I'm trying to get the other categories they aren't falling into place.

bogu

If u can grab all the main categories without a problem, then u have all there ids, so it shouldnt be a problem to extract all the subcategories that have parrent_id=category_id ...

focus310

Hi,

When I set parent_id = category_id, my result becomes empty. No records are retrieved.

This is the SQL statement I'm using:

SELECT category_id, category_name, parent_id
FROM category
WHERE parent_id = 0 AND parent_id = category_id;

When I run the query with parent_id = 0 - no problem.
When I add the parent_id = category_id - that's when I retrieve no data.

I'm not sure where I'm going wrong with this statement.

bogu

Because your not doing as I told u ...
First select all the main categories
And with the result from that select, SELECT all the subcategory for each main category ...

focus310

First, I'm new to PHP so please bare with me. I'm not quite sure how to do what you are stating.

Here are my results:
1Hardware0
2Software0
3DVD Movies0
7Music0

The script isn't pulling and showing the sub-categories.

Here is what my crazy code looks like:

<?php

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


       $query = "SELECT * FROM category WHERE parent_id = 0";

       $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'];
   }         

              $sql = "SELECT * FROM category WHERE $parent_id = category_id";
              $result1 = mysql_query($query, $conn) or die (mysql_error());
                 while($row1 = mysql_fetch_array($result1)) {  

                $category_id = $row1['category_id'];
                $category_name = $row1['category_name'];
                    $parent_id = $row1['parent_id']; 

     echo $category_id;
     echo $category_name;
     echo $parent_id;

}
          ?>

Lars_Berg

You dont need multiple queries

select concat(case when parent_id <> 0 then '- ' else '' end,
category_name)
from categories
order by case when parent_id = 0 then category_id else parent_id end,
category_id

focus310

Hi,

This didn't work either.