Help with query please?

[woolyg]

Hi all,

 

Here's my table:

-- Table structure for table `table1`
--

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(256) NOT NULL,
  `parent_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`id`, `title`, `parent_id`) VALUES
(1, 'Top Level 1', 0),
(2, 'Top Level 2', 0),
(3, 'Second Level 1', 1),
(4, 'Second Level 2', 2),
(5, 'Third Level 1', 3);

 

ID

TITLE

PARENT_ID

1

Top Level 1

0

2

Top Level 2

0

3

Second Level 1

1

4

Second Level 2

2

5

Third Level 1

3

 

 

What I'm trying to do with my query is order the results by parent_id, then by title, then by id, EG:

Top Level 1

- Second Level 1

-- Third Level 1

Top Level 2

- Second Level 2

 

 

 

The Query

SELECT * 
FROM table1 
ORDER BY parent_id, title, id

.. orders each row simply by 'parent_id', then moves on to the next row. I'd ideally like it to output a row of parent 0, then the children of that parent id, then the children of its children, etc... then once it't finished iterating through the children of the first ID of parent 0, it moves on to the next ID of parent 0...

 

Can anyone point me in the right direction?

 

Thanks

WoolyG

[objnoob]

One way is to loop and sub loop. Make sure you use individual datasets, and do not overwrite them.

$sql1 = "SELECT col1, col2, col3 FROM table WHERE col1 LIKE 'Top Level %' ORDER BY col1";
$dataset1 = mysql($sql1);
while ($row_ds1 = mysql_fetch_assoc($dataset1)){
   echo $row_ds1['col1'];
   $sql2 = "SELECT col1, col2, col3 FROM table WHERE col1 LIKE 'Second Level %' AND Parent_Id={$row_ds1['col1']} ORDER BY col1";
   $dataset2 = mysql($sql2);
   while ($row_ds2 = mysql_fetch_assoc($dataset2)){
      echo $row_ds2['col1'];
      $sql3 = "SELECT col1, col2, col3 FROM table WHERE col1 LIKE 'Third Level %' AND Parent_ID={$row_ds2['col1']} ORDER BY col1";
      $dataset3 = mysql($sql3);
      while ($row_ds3 = mysql_fetch_assoc($dataset1)){
         echo $row_ds3['colwhateverrrr'];
      }
  }
}

[fenway]

A few joins would do the trick, too.