Data sort problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Data sort problem

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post 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?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

Storing Hierarchical Data in a Database gives some usefule examples and an alternative.
Last edited by CoderGoblin on Mon Jul 03, 2006 8:27 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post 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
Post Reply