Making a mysql (kind of tree) comment system. How to setup?

[Mortenjan]

Hi again people.

 

I am trying to make a comment system with mysql and php.

I want the user to be able to reply to posts, so that their comment goes beneath the original post. To make a kind of posting tree as for example dagbladet.no has made (example: http://www.dagbladet.no/2010/08/03/nyheter/piratangrep/adenbukta/12816125/  , look at the bottom)

 

I have made the following columns:

blog_comment_id, blog_comment_reply_id ,blog_comment_name, blog_comment_date and blog_comment_content

 

I thought that blog_comment_reply_id should connect with comment_id.

So all comments with same blog_comment_reply_id is beneath the post with one spesific comment_id. Hope my explanation is good enough.

 

How should i code in mysql? Should i use join, left join or right join or something?

 

Any help would be much appreciated.

 

Regards Morten

[Mortenjan]

By the way my mysql version is 5.0.91-community and my php version is 5.2.13.

 

i have tried this query:

 

SELECT

c1.blog_comment_id,

c1.blog_comment_name,

c1.blog_comment_content,

c1.blog_comment_date,

 

c2.blog_comment_id, 

c2.blog_comment_reply_id,

c2.blog_comment_name,

c2.blog_comment_date,

c2.blog_comment_content,

c2.blog_comment_admin, c2.blog_id

 

FROM blog_comments AS c1

RIGHT JOIN blog_comments AS c2

 

ON c1.blog_comment_id = c2.blog_comment_reply_id

 

WHERE c2.blog_id = '$blog_id'

 

ORDER BY c1.blog_comment_reply_id DESC, c2.blog_comment_id ASC

 

 

but i dont think this is the way to go at all.

 

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: sql.byethost9.org
-- Generation Time: Aug 03, 2010 at 04:59 PM
-- Server version: 5.1.48
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `kjetilja_database`
--
CREATE DATABASE `kjetilja_database` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `kjetilja_database`;

-- --------------------------------------------------------

--
-- Table structure for table `admin`
--

CREATE TABLE IF NOT EXISTS `admin` (
  `admin_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `forename` varchar(100) NOT NULL,
  `surname` varchar(100) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`admin_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Table structure for table `admin_logins`
--

CREATE TABLE IF NOT EXISTS `admin_logins` (
  `admin_login_id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_login_name` varchar(100) NOT NULL,
  `admin_login_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`admin_login_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=181 ;

-- --------------------------------------------------------

--
-- Table structure for table `blog`
--

CREATE TABLE IF NOT EXISTS `blog` (
  `blog_id` int(11) NOT NULL AUTO_INCREMENT,
  `blog_title_en` varchar(100) NOT NULL,
  `blog_title_no` varchar(100) NOT NULL,
  `blog_image` varchar(100) NOT NULL,
  `blog_content_en` longtext NOT NULL,
  `blog_content_no` longtext NOT NULL,
  `blog_date` datetime NOT NULL,
  UNIQUE KEY `blog_id` (`blog_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=168 ;

-- --------------------------------------------------------

--
-- Table structure for table `blog_comments`
--

CREATE TABLE IF NOT EXISTS `blog_comments` (
  `blog_id` int(11) NOT NULL,
  `blog_comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `blog_comment_reply_id` int(11) NOT NULL,
  `blog_comment_name` varchar(100) NOT NULL,
  `blog_comment_date` datetime NOT NULL,
  `blog_comment_content` longtext NOT NULL,
  `blog_comment_admin` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`blog_comment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=212 ;

-- --------------------------------------------------------

--
-- Table structure for table `counter`
--

CREATE TABLE IF NOT EXISTS `counter` (
  `count` int( DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `gallery`
--

CREATE TABLE IF NOT EXISTS `gallery` (
  `menu_id` int(11) NOT NULL,
  `image_id` int(11) NOT NULL AUTO_INCREMENT,
  `image_title_en` varchar(100) NOT NULL,
  `image_title_no` varchar(100) NOT NULL,
  `image_source` varchar(200) NOT NULL,
  `image_thumbnail` varchar(200) NOT NULL,
  PRIMARY KEY (`image_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

-- --------------------------------------------------------

--
-- Table structure for table `menu`
--

CREATE TABLE IF NOT EXISTS `menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT,
  `webpage_id` int(11) NOT NULL,
  `menu_title_en` varchar(100) NOT NULL,
  `menu_title_no` varchar(100) NOT NULL,
  `menu_content_en` longtext NOT NULL,
  `menu_content_no` longtext NOT NULL,
  `menu_menu` varchar(100) NOT NULL DEFAULT 'menu.php',
  `menu_include_gallery` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`menu_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;

-- --------------------------------------------------------

--
-- Table structure for table `webpage`
--

CREATE TABLE IF NOT EXISTS `webpage` (
  `webpage_id` int(11) NOT NULL AUTO_INCREMENT,
  `webpage_name_en` varchar(200) NOT NULL,
  `webpage_name_no` varchar(200) NOT NULL,
  `webpage_title_en` varchar(150) NOT NULL,
  `webpage_title_no` varchar(150) NOT NULL,
  `webpage_content_en` longtext NOT NULL,
  `webpage_content_no` longtext NOT NULL,
  `webpage_url_no` varchar(100) NOT NULL,
  `webpage_url_en` varchar(100) NOT NULL,
  `webpage_include` varchar(100) NOT NULL,
  `webpage_include_position` int(11) NOT NULL DEFAULT '1',
  `webpage_include_sponsors_menu` int(1) NOT NULL DEFAULT '0',
  `webpage_script` varchar(100) NOT NULL,
  `webpage_include_standard_menu` int(1) NOT NULL DEFAULT '1',
  `webpage_include_blog_menu` int(1) NOT NULL DEFAULT '0',
  `webpage_include_white_line` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`webpage_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

 

Thanks in advance

[fenway]

There are some excellent sticky resources on storing hierarchical data -- but use LEFT JOIN instead of RIGHT JOIN, it's the more common variant.