Need mysql help

thecookie

I need some help with a mysql/SQL query.

I have a table with 3 fields; id, parent and name.

I am planning to use it to create a catalouge structure. The parent field should be set to the id of the "parent". If the parent is NULL (default value) it is a main cat.

Is it possible to with one query first select all rows with parent set to NULL, then all rows which are childs to parents?

Or do I have to solve this in any other way?

Thanks for any help!

gid

This kinda intersted me, as amazingly enough, it's been years since I've had to solve this problem, so I played around a little.

Here's the table structure I used:

CREATE TABLE `cats` (
  `id` int(11) NOT NULL auto_increment,
  `parent` int(11) default NULL,
  `name` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

INSERT INTO cats VALUES (1,NULL,'Computer');
INSERT INTO cats VALUES (2,1,'Software');
INSERT INTO cats VALUES (3,1,'Hardware');

If you're only doing categories and sub categories, and no deeper, I was playing and found that you could do this.

SELECT c.id, c.name, c.parent, c2.id as subid, c2.name as subname
FROM cats c LEFT JOIN cats c2 ON c.id=c2.parent
WHERE c.parent is NULL

You'll get a result that looks like this:

id   name         parent  subid   subname
1    Computer               2         Software
1    Computer               3         Hardware

It's not exactly a clean result, but you can figure out the structure in php code by keep track of what "id" you're on, and when you encounter a new id, then echo the name, else echo subname.

There's probably a million ways to solve this problem though, this is just one of em. In the past done id, parent, crumbs, and name. Where crumbs is a comma delimited list of children, to help out with a query. But that was a rather unique situation, because the id, parent was a separate table, so each subcategory could show up in multiple cats if we so desired.