[SOLVED] Blog style with posts and replies.. how to do?

[olysim]

Hello,

I could use some help writing a statement that I just can not seem to solve.

 

The project I am working is similar to that of a blog.  A user makes a post and then people can reply to that post.  The problem is every time someone makes a reply, it is showing the original post again instead of adding the comment to the original.

 

so here is what I would like it to achieve:

Original Post

- comment 1

- comment 2

- comment 3

 

and instead it is doing this:

Original Post

- comment 1

Original Post

- comment 2

Original Post

- comment 3

 

Here is some more details on what I have going:

 

MySQL Version: 5.0

 

my statement:

 

SELECT p.ID as pID, p.user as pUser, p.comment as pComment, p.datetime as pDateTime, c.ID as cID, c.parentID as cParentID, c.user as cUser, c.comment as cComment, c.datetime as cDateTime, u.username as uUser, u.avatar as uAvatar, u.pavatar as pAvatar, nu.avatar as nuAvatar, nu.pavatar as nupAvatar 
FROM egg_posts p 
LEFT JOIN egg_comments c ON p.ID = c.parentID 
LEFT JOIN egg_users u ON p.user = u.username 
LEFT JOIN egg_users nu ON c.user = nu.username 
ORDER BY c.datetime DESC, p.datetime DESC

 

my tables:

 

CREATE TABLE `egg_comments` (

`ID` int(11) NOT NULL auto_increment,

`parentID` int(11) NOT NULL,

`user` varchar(16) default NULL,

`comment` text NOT NULL,

`datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY  (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8

 

CREATE TABLE `egg_posts` (

`ID` int(11) NOT NULL auto_increment,

`user` varchar(16) default NULL,

`comment` text NOT NULL,

`datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY  (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

 

 

CREATE TABLE `egg_users` (

`ID` int(11) NOT NULL auto_increment,

`username` varchar(16) NOT NULL,

`pw` varchar(32) NOT NULL,

`name` varchar(150) NOT NULL,

`email` varchar(255) NOT NULL,

`avatar` varchar(255) default NULL,

`pavatar` varchar(6) default NULL,

`gender` char(1) NOT NULL,

`address` varchar(255) default NULL,

`dob` varchar(50) NOT NULL,

`datecreated` timestamp NOT NULL default CURRENT_TIMESTAMP,

`active` tinyint(1) NOT NULL,

PRIMARY KEY  (`ID`),

UNIQUE KEY `username` (`username`),

UNIQUE KEY `email` (`email`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

 

 

Thanks for any help or suggestions, it is greatly appreciated!

 

 

[olysim]

I decided to show the comments only after a 'Show Comments' link had been clicked so it did away with my need for the post above.  Thanks to all who took a gander at this anyhow.