[SOLVED] help me plan my "comments" organization

[Lodius2000]

im striking out on what to do on how to organize my comments on my blog

 

im going to use 2 different mysql tables, one for the blog entry and one for the comments but i cant figure out how to make the comments table

 

the blog entry table is auto incremented, so i want to use that same number for storing the comments so i can use $_GET

 

i need to store the username and comment for each comment, but i cant figure out how to have an infinite number of comments using the row column structure

 

basically I want to have a sql query that says SELECT username, comment FROM comments where id='$_GET[id]', but i realize this would only return 1 username and its corresponding comment

 

but i want to get all the comments associated with the specific entry and then print all the comments out in a foreach loop

 

could someone help me with the table structure

 

Thanks a bunch

[awpti]

CREATE TABLE my_comments (

`comment_id` int not null primary key auto_increment,

`post_id` int, # references ID of post that was commented on

`user_id` int, #references ID of user that commented

`subject` .....,

  `comment text

);

 

SELECT * FROM `my_comments` LEFT JOIN my_users ON my_users.id = my_comments.user_id WHERE `post_id` = 1;

[.josh]

I think I would possibly do a one-to-many relationship.  Basically you have 2 tables, one for the blog and one for the comments.  The blog table is pretty straight forward, and so is the comment table, except for one thing: comment_info also has a blog_id column.  this column will contain the id associated to the blog entry in blog_info.  You would then run a query based on that.

 

blog_info        comment_info

blog_id    <--->    blog_id

blog_title            comment_id

blog_time            comment_time

blog_author          comment_author

blog_content        comment_content

 

 

[Lodius2000]

awpti: i knew it was something easy, didnt even think of having a comment id column and then each row could be any comment id that it wants, you just search the comment_id for the $_GET value

 

duh

 

//hates self right now

 

 

crayon, though you say it is simple, you have no idea how much my tables look like what you just layed out

your suggestions will be put to good use too

[Lodius2000]

so if i were to use a comment time and then order by, seconds from unixtime is that a speedy operation ( i guess it is only comparing integers) or would that really bog it down if i start to get in the dozens of comments range, is there a better way to keep track of how to order the comments that is faster or should i just go with seconds from unixtime?

[.josh]

no more speedy than ordering by anything else, AFAIK... but I'm nowhere near the database expert. You might wanna pose that question over in the sql forums.