Page 1 of 1

Data sort problem

Posted: Fri Jun 30, 2006 2:57 pm
by fullfocus
Hello:

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.

This is my script:

Code: Select all

<?php

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


           $query = "SELECT * FROM category WHERE 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;
}
          ?>
The result of my query retrieves zero records.

Thanks again for the help.

Posted: Mon Jul 03, 2006 5:12 am
by GM
Your query retrieves no results because you are comparing for each record the category_id with the parent_id - the query extracts them only when they are equal, but they never are... Look at your data:

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

4 != 1
5 != 1
6 != 3
7 != 3
8 != 2
9 != 2
...

I would suggest either having a separate table for your parent category table (only if you know how many child levels there can be), or joining the table to itself. Your problem, however is going to be if you have many child levels, in this case SQL will not do what you want - you will need to extract all the data and process it with PHP.

Assuming only 2 levels (parent and child) a solution would be:

Code: Select all

SELECT a.category_name, b.category_name 
FROM t_category a, t_category b
WHERE (b.parent_id = a.category_id)
gives you:

Code: Select all

+---------------+---------------+
| category_name | category_name |
+---------------+---------------+
| Hardware      | Printers      |
| Hardware      | Memory        |
| DVD Movies    | Drama         |
| DVD Movies    | Sci Fi        |
| Software      | Simulation    |
| Software      | Educational   |
+---------------+---------------+
Unfortunately, you are going to need to join the table to itself for each child level, which means you need to know how many of them there are.

Posted: Mon Jul 03, 2006 5:49 am
by fullfocus
Hi,

Thank you for the reply. I was considering joining the table to itself.

In this example of data, there is only one child level. The goal is to have the flexbility for multiple child levels. And, the number of multiple child levels could be unlimited.

On a side note, how did you create the table with the results in your post? I can't seem to figure out how.

Posted: Mon Jul 03, 2006 7:01 am
by GM
Use

Code: Select all

 insert code here [/code ] tags (minus the spaces before the closing bracket) to make it monospaced. (I just copied and pasted the output from the mySQL console window).

In the case of multiple child levels, SQL will not provide an effective way of extracting the data as you need it. In order to extract the data for n child levels, you need to join the table to itself n times. Clearly this makes writing the query rather difficult.

Posted: Mon Jul 03, 2006 8:14 am
by fullfocus
It certainly does.

So, how do some of the e-commerce websites are able to show multiple sub-categories within a main category?

Posted: Mon Jul 03, 2006 8:22 am
by CoderGoblin
Storing Hierarchical Data in a Database gives some usefule examples and an alternative.

Posted: Mon Jul 03, 2006 8:26 am
by Weirdan
In order to extract the data for n child levels, you need to join the table to itself n times...
...given the db scheme you provided. Yet there are exist different approaches for storing parent-child relations, e.g. nested sets

Posted: Mon Jul 03, 2006 9:03 am
by GM
Weirdan wrote:
In order to extract the data for n child levels, you need to join the table to itself n times...
...given the db scheme you provided. Yet there are exist different approaches for storing parent-child relations, e.g. nested sets
Thanks Weirdan - I was looking for that very example, but couldn't find the damn thing.

Posted: Mon Jul 03, 2006 9:42 am
by fullfocus
Thank you very much for all the replies. I'm printing the article right now. From what I saw, it is exactly what I'm trying to accomplish. Hopefully, it'll give me some ideas.

Thanks again everyone! :D