Topic: Blog Comment

[Bman900]

So this is how I have my blog table

id (primary key) | title | content | date | user

here is what am thinking for my comment table

id (primary key) | name | date | blogid

So if I do an enquiry in PHP where I search the comments table for all comments that match the id number of the blog with my "blogid" and display them in asceding order would that work? Or is there a better way to do this?

[ignace]

Forum threads; posts, Blog posts; comments, .. they have lots of commonalities for example:

A blog post contains a title, publish date, content and has an author. A comment contains a title (Re: ..), approved date, content and has an author. Using this information and our knowledge of SQL allows you to form a more effective application, for example we can create these tables:

Code: [Select]

CREATE TABLE blog_post (
  id integer not null auto_increment,
  author_id integer,
  title varchar(128),
  title_slug varchar(128),
  publish_date datetime,
  content text,
  is_published boolean,
  KEY blog_post_author_id_fk (author_id),
  UNIQUE blog_post_title_slug_un (title_slug),
  PRIMARY KEY (id));

CREATE TABLE blog_post_comment (
  id integer not null auto_increment,
  blog_post_id integer,
  title varchar(128),
  author varchar(32),
  approved_date datetime,
  content text,
  is_approved boolean,
  KEY blog_post_comment_blog_post_id_fk (blog_post_id),
  PRIMARY KEY (id));
Our normal 2-step query is now:

Code: [Select]

SELECT title, user.username AS author, publish_date, content
FROM blog_post JOIN user ON blog_post.author_id = user.id
WHERE is_published = true AND title_slug = $slug
UNION
SELECT title, author, approved_date AS publish_date, content
FROM blog_post_comment
JOIN blog_post ON blog_post_comment.blog_post_id = blog_post.id
WHERE is_approved = true AND blog_post.title_slug = $slug
Your result now looks like:

Code: [Select]

title | author | publish_date | content
Hello World | ignace | 2010-01-10 15:02:05 | Lorem ipsum dolor sit amet consectetuer adipiscing elit..
Re: Hello World | robin | 2010-01-10 15:59:01 | Wow, great article LOVE IT!!
PHP:

$blogPost = mysql_fetch_array($result, MYSQL_FETCH_ASSOC);
..

while ($comment = mysql_fetch_array($result, MYSQL_FETCH_ASSOC)) {
 ..